Re: Problem scheduling a job using DBMS_JOBS.SUBMIT

login to sql plus from the schema owning the procedure

variable job number;
begin
  dbms_job.submit(:job, 'procedure_name;', sysdate,'sysdate+1'); end;
/

notice that there is no EXEC before the procedure name. notice also that the job's NEXT_DATE will not be set until the job finishes, so
using just SYSDATE+1 will cause the run TIME to migrate; e.g., it will not necessarily
start at the same time tomorrow.

I use 'TRUNC(sysdate) + 1+11/24' to get the job to start tomorrow at 11 am.

"Aditya" <adityanath_at_hotmail.com> wrote in message news:4a5a78d4.0203141054.25806b24_at_posting.google.com...
> Hi!
>
> I have to schedule a stored procedure to be run every night.
> I tried using DBMS_JOBS.SUBMIT
>
> I gave the folloing command from sqlplus connecting as system
>
> Declare
> ln_job_no Number;
> Begin
>
> DBMS_JOB.SUBMIT(ln_job_no, 'exec dash.sp_report_data;',sysdate, 'sysdate
+1');
> DBMS_OUTPUT.PUT_LINE(to_char(ln_job_no));
> End;
> /
> dash is the user and sp_report_data is the stored procedure.
>
> But it gives me following error.
> Declare
>
> *
> ERROR at line 1:
> ORA-06550: line 1, column 98:
> PLS-00103: Encountered the symbol "DASH" when expecting one of the
following:
> := . ( @ % ;
> The symbol ":=" was substituted for "DASH" to continue.
> ORA-06512: at "SYS.DBMS_JOB", line 79
> ORA-06512: at "SYS.DBMS_JOB", line 131
> ORA-06512: at line 5I tried all possible combination
>
> I tried all other possible combinations
> 'sp_report_data;'
> 'exec sp_report_data;'
> 'exec dash.sp_report_date;'
>
> It is not accepting.
>
> How can I schdule this job using DBMS_JOBS
>
> Thanks in advance.
> Regards,
> Aditya
Received on Thu Mar 14 2002 - 16:20:18 CST