Re: How can I run a procedural in scheduling?

hi eric,

perhaps DBMS_JOB.SUBMIT is what you are looking for. this method is used to register a job, potentially reoccuring, with Oracle. once registered a job, which essentially would be a stored procedure, is run according to parameters given in SUBMIT method. here goes the syntax of DBMS_JOB.SUBMIT right out of my oracle 8.1.6 manual:

DBMS_JOB.SUBMIT (
  job OUT BINARY_INTEGER,
  what IN VARCHAR2,
  next_date IN DATE DEFAULT sysdate,
  interval IN VARCHAR2 DEFAULT ’null’,   no_parse IN BOOLEAN DEFAULT FALSE,
  instance IN BINARY_INTEGER DEFAULT any_instance,   force IN BOOLEAN DEFAULT FALSE);

and an example given in the same section:

VARIABLE jobno number;
BEGIN
 DBMS_JOB.SUBMIT(:jobno,
 ’dbms_ddl.analyze_object(’’TABLE’’,  ’’DQUON’’,
’’ACCOUNTS’’,
 ’’ESTIMATE’’, NULL, 50);’  SYSDATE, ’SYSDATE + 1’);
 commit;
END;
/
Statement processed.

print jobno
JOBNO

This submits a new job to the job queue. The job calls the procedure DBMS_
DDL.ANALYZE_OBJECT to generate optimizer statistics for the table DQUON.ACCOUNTS. The statistics are based on a sample of half the rows of the
ACCOUNTS table. The job is run every 24 hours:

i hope this would help you start with scheduling your procedure.

:) ATTA "Eric" <maw_at_tpts5.seed.net.tw> wrote in message news:<9edlol$2dk9$1_at_news.itri.org.tw>...
> Dear All:
> I had a procedural need to run day by day? Had any way to solve it?
> I had find a procedural dbms_aqadm.schedule_propagation. But when I Use
> it,Oracle always had error message "dbms_aqadm.schedule_propagation Not
> Declare!!".
>
> example:
> EXECUTE dbms_aqadm.schedule_propagation( queue_name => 'BOLADM.test');
>
>
> Eric
Received on Mon May 21 2001 - 06:13:25 CDT