Procedure w/ input parameter - dbms_job 1

[cjmartin]

I have a procedure with a varchar input parameter. I'm trying to schedule via dbms_job, but am having problems.

Here's how I scheduled other procedures (without input parameters):

DECLARE
 job_no     NUMBER;
BEGIN
 DBMS_JOB.SUBMIT
    (job_no 
    ,'begin myprocedure; end;'
    ,TRUNC(SYSDATE + 1) 
    ,'TRUNC(SYSDATE + 1)');
 COMMIT;
END;

This seems to work correctly (runs it at midnight nightly). Now when trying to schedule a procedure with an input parameter...

DECLARE
 job_no     NUMBER;
BEGIN
 DBMS_JOB.SUBMIT
    (job_no 
    ,'begin myprocedure to_date('04/01/2002','MM/DD/YYYY'); end;'
    ,TRUNC(SYSDATE + 1) 
    ,'TRUNC(SYSDATE + 1)');
   COMMIT;
END;

...I get the following error:

ORA-06550: line 10, column 35:
PLS-00103: Encountered the symbol "04" when expecting one of the following:

. ( ) , * @ % & | = - + < / > at in mod not range rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like
between is null is not || is dangling
The symbol &quot;(&quot; was substituted for &quot;04&quot; to continue.
ORA-06550: line 10, column 45:
PLS-00103: Encountered the symbol &quot;,&quot; when expecting one of the following:

) , * & | = - + < / > at in mod not rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like as
between from using is null is not || is dangling
ORA-06550: line 10, column 58:
PLS-00103: Encountered the symbol &quot); end;&quot; when expecting one of the following:

. ( ) , * @ % & | = - + < / > at in mod not rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like
between is null is not || is danglin
ORA-06550: line 12, column 26:
PLS-00103: Encountered the symbol &quot;;&quot; when expecting one of the following:

. ( ) , * % & | = - + < / > at in mod not range rem

I'm using the
&quot;myprocedure to_date('04/01/2002','MM/DD/YYYY')&quot; logic b/c when I execute the procedure in SQL*Plus, I type...

SQL> exec myprocedure to_date('04/01/2002','MM/DD/YYYY')

...which executes correctly.

Any thoughts on how to code the desired input parameter when scheduling via dbms_job? Is it just a syntax problem? One thing that worries me is that I've done alot of research but haven't found even one example of someone scheduling a job that requires an input parameter. Is it even possible? I would think that it has to be possible, but...

Thanks for your input.

 

[karluk]

The immediate reason for the error you are seeing is no doubt because you embedding single quotes within a quoted string ('begin myprocedure to_date('04/01/2002','MM/DD/YYYY'); end;')

The embedded quotes are being interpreted as the end of the string, which you don't want. The standard way to avoid this is to use two consecutive single quotes. Oracle interprets this as a single embedded quote and does not terminate the string. If you didn't have any other errors, your pl/sql would be

DECLARE
job_no NUMBER;
BEGIN
DBMS_JOB.SUBMIT
(job_no
,'begin myprocedure to_date(''04/01/2002'',''MM/DD/YYYY''); end;'
,TRUNC(SYSDATE + 1)
,'TRUNC(SYSDATE + 1)');
COMMIT;
END;


However it seems to me that you have another error in that you don't call your procedure correctly. As far as I know, the right way to call a procedure with input parameters is to use the syntax

procedure_name (input_parm_1, input_parm_2, ...);

That means you don't have enough parentheses in your pl/sql. I think it should be

DECLARE
job_no NUMBER;
BEGIN
DBMS_JOB.SUBMIT
(job_no
,'begin myprocedure (to_date(''04/01/2002'',''MM/DD/YYYY'')); end;'
,TRUNC(SYSDATE + 1)
,'TRUNC(SYSDATE + 1)');
COMMIT;
END;

 

[cjmartin]

karluk,

You were right on...the script ran w/out errors with the changes you suggested. I'll see tomorrow if they ran as scheduled.

Thanks!!!