2 Replies Latest reply: May 13, 2013 12:06 PM by John K.-Oracle RSS

    Syntax for APEX_PLSQL_JOB.SUBMIT_PROCESS with an OUT parameter

    John K.-Oracle
      Hi All,
      I am using APEX_PLSQL_JOB.SUBMIT_PROCESS and calling a procedure that has both an IN and an OUT parameter.

      The IN parameter works fine; I am able to call my procedure from APEX and pass it the :APP_JOB item as the IN parameter. The OUT parameter I can't get working. I can call my procedure from a different block in SqlDeveloper and everything works, so I'm confident that my procedure code is correct. I just can't get the OUT parameter returned via APEX.

      This is the plsql code that I'm calling from an APEX dynamic action:
      DECLARE
        l_sql      VARCHAR2(4000);
        l_instance VARCHAR2(30);
         l_job number;
        l_rows_processed number;
      
      BEGIN
      l_instance := :P9_INSTANCE;
        l_sql      := 'BEGIN update_tl_tables_1(:APP_JOB, :F103_ROWS_PROCESSED); END;';
        l_job      := APEX_PLSQL_JOB.SUBMIT_PROCESS( p_sql => l_sql, p_status => 'Background process submitted');
      
      l_rows_processed := :F103_ROWS_PROCESSED;
      insert into gse_lng_jobs (instance, job_id, rows_processed) values (l_instance, l_job, l_rows_processed);
      COMMIT;
      END;
      My procedure is created as:
      create or replace
      procedure UPDATE_TL_TABLES_1 (l_app_job IN number, l_rows_processed OUT number)
      IS......
      Again, this works fine from SqlDeveloper. Can anyone suggest the proper plsql code? I've tried this using a page item, a system item etc. and no luck. I first tried just passing my procedure a local variable like l_rows_processed, but the procedure wouldn't even run, so I'm assuming the API needs an actual APEX item but I'm out of ideas here.

      thanks in advance,
      john