DBMS_JOBS

From: Gowree <gowreesan_at_gmail.com>
Date: 22 Nov 2006 06:35:07 -0800
Message-ID: <1164206107.606576.316200@j44g2000cwa.googlegroups.com>

Any idea why this auto job for every 10 minutes not working?

SQL> SET SERVEROUTPUT ON
SQL>
SQL> Create or replace PROCEDURE DatabaseStatus is
  2     v_job_number BINARY_INTEGER;
  3     v_instance_number NUMBER(10);
  4     v_database_status varchar2(20);
  5     v_logins varchar2(20);
  6     v_status varchar2(20);
  7     v_hostName varchar2(20);
  8     v_instanceName varchar2(20);

  9
 10 BEGIN
 11

 12     -- Get the instance number for use with DBMS_JOB.SUBMIT
 13     SELECT
instance_number,database_status,logins,status,host_name,instance_name
 14     INTO
v_instance_number,v_database_status,v_logins,v_status,v_hostName,v_instanceName
 15     FROM v$instance;
 16
 17     DBMS_JOB.SUBMIT (JOB => v_job_number,
 18                      WHAT => 'job1;',
 19                      NEXT_DATE => TRUNC(SYSDATE+1/144,'MI'),
 20                      INTERVAL =>  'trunc(sysdate+1/144,''MI'')',
 21                      NO_PARSE => TRUE,
 22                      INSTANCE => v_instance_number,
 23                      FORCE => NULL);
 24     COMMIT;

 25
 26 DBMS_OUTPUT.PUT_LINE('The job number is:
'||v_job_number);

 27 DBMS_OUTPUT.PUT_LINE('The Database instance number is:
'||v_instance_number);

 28 DBMS_OUTPUT.PUT_LINE('The Database status:
'||v_database_status);

 29 DBMS_OUTPUT.PUT_LINE('The Database logins:
'||v_logins);

 30 DBMS_OUTPUT.PUT_LINE('Status is:
'||v_status);

 31 DBMS_OUTPUT.PUT_LINE('Host Name is:
'||v_hostName);

 32 DBMS_OUTPUT.PUT_LINE('Instance Name is:
'||v_instanceName);

 33
 34 END;
 35 /

Procedure created.

SQL> commit;

Commit complete.

SQL> Create or replace PROCEDURE job1 as   2 begin
  3 insert into temp_Table values(sysdate,'Active');   4 commit;
  5 end;
  6 /

Procedure created.

SQL> exec DatabaseStatus;
The job number is: 7

The Database instance number is: 1

The Database status: ACTIVE

The Database logins: ALLOWED

Status is:                       OPEN

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> spool off Received on Wed Nov 22 2006 - 08:35:07 CST