Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2002
    Posts
    162

    Unanswered: setting up oracle job with out parameter (in proc scheduled by job)

    Hi all.

    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)

    AS

    v_sql_stmt VARCHAR2(300);
    v_sql_timediff_stmt VARCHAR2(300);
    v_timediff NUMBER;
    v_jobnum NUMBER;
    v_status NUMBER;
    err_num NUMBER;
    err_msg VARCHAR2(100);

    BEGIN

    v_sql_timediff_stmt := 'SELECT ltrim(TO_NUMBER(ROUND(TO_DATE(:bvtime,''mm/dd/yyyy HH:MIS AM'') - SYSDATE,2))) FROM DUAL';

    DBMS_OUTPUT.PUT_LINE(v_sql_timediff_stmt);

    EXECUTE IMMEDIATE v_sql_timediff_stmt INTO v_timediff USING v_time;


    IF v_module = 'BF'
    THEN
    BEGIN
    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;';
    DBMS_OUTPUT.PUT_LINE(v_sql_stmt);
    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:

    sa.approve_budget(1,1,1,2,1,1,:status)

    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.................

    thanks

    'Wale

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try changing the job to:

    v_sql_stmt := 'BEGIN DBMS_JOB.SUBMIT (:bvjobnum,''declare status integer; begin sa.approve_budget('||from_ver||','||from_var||','| |fr om_cyc||','||to_ver||','||to_var||','||to_cyc||',status); end;'',SYSDATE + '||v_timediff||'); END;';
    DBMS_OUTPUT.PUT_LINE(v_sql_stmt);
    EXECUTE IMMEDIATE v_sql_stmt USING out v_jobnum;

    (I also added a missing comma before the status parameter.)

    But why dynamic SQL? Why not just:

    DBMS_JOB.SUBMIT (v_jobnum,'declare status integer; begin sa.approve_budget('||from_ver||','||from_var||','| |from_cyc||','||to_ver||','||to_var||','||to_cyc|| ',status); end;',SYSDATE + v_timediff);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •