Re: Problem scheduling a job using DBMS_JOBS.SUBMIT

Well, 'exec' is a SQL*PLUS command so forget about that one. I don't see where you tried 'dash.sp_report_data'? Are you sure the procedure doesn't require arguments? What error does it give with 'dash.sp_report_data'?

Richard

Aditya wrote:
>
> 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 - 13:38:40 CST