Re: dbms_job and the 'what'

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;

The value for p_char in the quoted string 'dummy_job(p_char)' is not known by
(nor passed to) dbms_job.submit.

Try

    dbms_job.submit(v_jobno, 'dummy_job(' || p_char || ');', sysdate + .0007, NULL, FALSE); which will cause the evaluation of the ARGUMENT p_char.

<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