Re: DBMS_JOBS

Carlos,

This will invoke the database for every 10 mins and insert into temp table
which my job1.(job1 is my simple stored procedure)

Also i have tried the simple PL/SQL block also it's not running.

see the PL/SQL BLOCK below

declare

 x1 number;

BEGIN    DBMS_JOB.SUBMIT(
   x1,
   'job1;' ,
    trunc(SYSDATE+1) +11.5/24 ,
    'trunc(SYSDATE+1/144,''MI'')'

);

   commit;

END;

Carlos wrote:
> Gowree ha escrito:
>
> > 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
>
> What are the values for JOB_QUEUE_PROCESSES and JOB_QUEUE_INTERVAL?
>
> Cheers.
>
> Carlos.
Received on Wed Nov 22 2006 - 10:45:27 CST