dbms_job and the 'what'

From: <miki_at_llnl.gov>
Date: 2000/02/17
Message-ID: <88hemt$fve$1_at_nnrp1.deja.com>#1/1

[Quoted] [Quoted] 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