Click here to Skip to main content
15,920,438 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,

I have one query. My question is below.

I have wrote one store procedure in oracle for inserting records in database.

I want to run this store procedure every day but not calling from any progaram or application.

How can i do this in oracel?

sholud i have to use oracle package and jobs for doing my requirments?

Pls help me
Posted

1 solution

You need to use dbms_job package for this requirement. Syntax for submitting a job using this as below.
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);

job - Number of the job being run.
what - PL/SQL procedure to run.
next_date - Next date when the job will be run.
interval - Date function that calculates the next time to run the job. The default is NULL. This must evaluate to a either a future point in time or NULL.
no_parse - A flag. The default is FALSE. If this is set to FALSE, then Oracle parses the procedure associated with the job. If this is set to TRUE, then Oracle parses the procedure associated with the job the first time that the job is run.
For example, if you want to submit a job before you have created the tables associated with the job, then set this to TRUE.
instance - When a job is submitted, specifies which instance can run the job.
force - If this is TRUE, then any positive integer is acceptable as the job instance. If this is FALSE (the default), then the specified instance must be running; otherwise the routine raises an exception.

Refer to this [LINK] for more information.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900