Re: dbms_job and the 'what'

It should be

     dbms_job.submit(v_jobno,
           'dummy_job('''||p_char||''');',

Hth,

Sybrand Bakker, Oracle DBA

<miki_at_llnl.gov> wrote in message news:88hemt$fve$1_at_nnrp1.deja.com...
> I want to use dbms_job to execute a stored procedure,dummy_job,
> which takes an input parameter of a varchar. I put the dbms_job
> line itself into a stored procedure,foo, which is passed in the
> varchar.
> Thus the steps are:
>
> 1. in sqlplus execute foo('1');
> 2. the procedure foo has ..
>
> create or replace foo(p_char in varchar2) is
> v_jobno number;
> begin
> dbms_job.submit(v_jobno,
> 'dummy_job(p_char);',
> sysdate + .0007,
> NULL,
> FALSE);
> commit;
> end foo;
>
> If I use a no_parse of FALSE (the default), I get an error
> message that states
>
> ERROR at line 1:
> ORA-06550: line 1, column 108:
> PLS-00201: identifier 'P_CHAR' must be declared
> ORA-06550: line 1, column 93:
> PL/SQL: Statement ignored
> ORA-06512: at "SYS.DBMS_JOB", line 71
> ORA-06512: at "SYS.DBMS_JOB", line 121
> ORA-06512: at "GL_INTERFACES.MIKI_TEST_JOB", line 5
> ORA-06512: at line 1
>
> If I use a no_parse of TRUE, the stored procedure
> is accepted and put into user_jobs but dummy_job
> never does the appropriate
> action (insert a row into a table w/ the p_char).
> The job remains in user_jobs with
> the next_date is incremented.
>
> The whole things works fine if I use a '1' instead
> of p_char. But I can't hardcode the input arguments
> ahead of time.
>
> Thanks for any help
> Miki
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Feb 17 2000 - 00:00:00 CET