Unanswered: setting up oracle job with out parameter (in proc scheduled by job)
I have a procedure that sets up an Oracle Job. This Job schedules a procedure that has 6 "in" parameters and 1 "Out" parameter. I have had no problems scheduling the job, but upon run time it fails to run and re-schedules itself. Here is the piece of code from the proc that sets up the job, (I have highlighted the piece to pay attention to):
CREATE OR REPLACE PROCEDURE CONVERSION_JOB (v_module IN VARCHAR2, v_time IN VARCHAR2,from_ver IN NUMBER, from_var IN NUMBER, from_cyc IN NUMBER,
to_ver IN NUMBER, to_var IN NUMBER, to_cyc IN NUMBER, p_status OUT NUMBER)
v_sql_timediff_stmt := 'SELECT ltrim(TO_NUMBER(ROUND(TO_DATE(:bvtime,''mm/dd/yyyy HH:MIS AM'') - SYSDATE,2))) FROM DUAL';
EXECUTE IMMEDIATE v_sql_timediff_stmt INTO v_timediff USING v_time;
IF v_module = 'BF'
v_sql_stmt := 'BEGIN DBMS_JOB.SUBMIT (:bvjobnum,''sa.approve_budget('||from_ver||','||f rom_var||','||from_cyc||','||to_ver||','||to_var|| ','||to_cyc||':status);'',SYSDATE + '||v_timediff||'); END;';
EXECUTE IMMEDIATE v_sql_stmt USING out v_jobnum;
.............and so on
now when the job is set up, the entry into dbms_jobs looks like this based on my input parameters into the above procedure:
the problem for me is that the :status variable is an OUT parameter in the approve_budget procedure.......... but Oracle doesnt seem to like it. I use the "out" parameter in approve_budget to return a "0" or "1" based on the success or failure of the proc on runtime.
Ordinarily if I was running this proc manually in SQL plus i'd just do the following:
set serveroutput on size 30000
VARIABLE status NUMBER
And then just run this sql like so: execute approve_budget(1,1,1,2,1,1,:status);
and it runs fine.......I'm having a problem scheduling the same proc though because of the output parameter....help please
someone mentioned I look into global variables or some other way of returning an out parameter so that I wouldnt have to input a variable for the "out" parameter, but no cigar so far.................