PL/SQL: Sample code to call a Procedure with OUT parameter using execute immediate

One of my collegue asked me how to call a PLSQL package procedure dynamically. He has done a lot with dynamic sql, but all of those were calling sql statements. he was not sure how to call a procedure which has an OUT variable as a parameter. This post is for him :)

This is just a quick example to show how can we get the value of an OUT variable when we call a procedure using execute immediate.

Sample Procedure

CREATE OR REPLACE PROCEDURE xxaj_test_proc (id      NUMBER
                                           ,msg OUT VARCHAR2)
IS
BEGIN
 
  DBMS_OUTPUT.put_line('    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
  DBMS_OUTPUT.put_line('    id:'||id);
  msg := 'Return from xxaj_proc for id ' || id ;
  DBMS_OUTPUT.put_line('    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
 
END xxaj_test_proc;
/

Sample code to call

DECLARE
  v_sql VARCHAR2(1000);
  v_msg VARCHAR2(100);
  v_id  NUMBER := 100;
BEGIN
 
  v_sql := 'begin xxaj_test_proc(:1,:2); end;';
 
  v_id := 100;
  DBMS_OUTPUT.put_line('Going to Call xxaj_test_proc with id: '|| v_id);
  --- Map the variables for the bind parameter and specify OUT for out variables  
  EXECUTE IMMEDIATE  v_sql using v_id,OUT v_msg; 
  DBMS_OUTPUT.put_line('v_msg: '|| v_msg); 
 
  DBMS_OUTPUT.put_line(''); 
  v_id := 101;
  DBMS_OUTPUT.put_line('Going to Call xxaj_test_proc with id: '|| v_id);
  --- Map the variables for the bind parameter and specify OUT for out variables  
  EXECUTE IMMEDIATE  v_sql using v_id,OUT v_msg; 
  DBMS_OUTPUT.put_line('v_msg: '|| v_msg); 
 
END;

Output

Going to Call xxaj_test_proc with id: 100
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    id:100
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
v_msg: Return from xxaj_proc for id 100

Going to Call xxaj_test_proc with id: 101
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    id:101
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
v_msg: Return from xxaj_proc for id 101

Feel free to point out if anything is wrong/missing in this post.

Oracle Apps: How to submit a Concurrent Program from back-end and wait for that to complete

In my previous post I have explained about how can we make the parent concurrent program wait for all the child programs to complete. In this one I thought of giving an example of how to submit a concurrent program from the back-end and wait for the completion of that.

DECLARE
 
   v_request_id        NUMBER;
   v_req_return_status BOOLEAN;
   v_phase             VARCHAR2(100);
   v_status            VARCHAR2(100);
   v_dev_phase         VARCHAR2(100);
   v_dev_status        VARCHAR2(100);
   v_message           VARCHAR2(100);
 
BEGIN
   --Initialize the session with appropirate values
   fnd_global.apps_initialize (user_id=>100
                              ,resp_id=>100
                              ,resp_appl_id=>100);
 
   --Submit the Request
   v_request_id := fnd_request.submit_request ( application => 'XXAJ'
                                              , program     => 'XXAJ_CHILD'
                                              , start_time  => SYSDATE
                                              , sub_request => FALSE);
   COMMIT;
 
   IF v_request_id = 0 THEN
      DBMS_OUTPUT.put_line('Request not submitted: '|| fnd_message.get);
   ELSE
      DBMS_OUTPUT.put_line('Request submitted successfully. Request id: ' || v_request_id);
 
      v_req_return_status := fnd_concurrent.wait_for_request 
                                            (request_id      => v_request_id
                                            ,INTERVAL        => 2 --interval Number of seconds to wait between checks
                                            -- ,max_wait     => 5 --Maximum number of seconds to wait for the request completion
                                             -- out arguments
                                            ,phase           => v_phase
                                            ,status          => v_status
                                            ,dev_phase       => v_dev_phase
                                            ,dev_status      => v_dev_status
                                            ,message         => v_message
                                            );   
      DBMS_OUTPUT.put_line('Request status: ' || v_status); 
   END IF;
 
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.put_line('Exception: ' || SQLERRM);   
END;
 

Note:- If you want to wait only for x seconds, specify the value for the parameter max_wait.

Feel free to point out if anything is wrong/missing in this blog.

Oracle Apps: How make the Parent Concurrent Program to wait till all the Child Concurrent Program Completes

This note gives a sample code in which a parent program submits multiple child programs waits for all the child programs to complete, without consuming any resources.

Ref: Oracle E-Business Suite Developer's Guide: PL/SQL APIs for Concurrent Processing

Sample Code for the Parent Program and the Child Program. In the sample code, the parent program submits 5 child programs which just wait for 10 seconds and the parent program waits for all the child programs to finish.

Note:- Once the child program completes the parent program gets restarted. Please have a closer look at the first few lines in the Parent Program code.

Package Spec

CREATE OR REPLACE PACKAGE xxaj_master_child_cp  
 AS  
  PROCEDURE parent_prg(p_errbuf OUT VARCHAR2  
            ,p_retcode OUT VARCHAR2);  
 
  PROCEDURE child_prg(p_errbuf OUT VARCHAR2  
            ,p_retcode OUT VARCHAR2);  
 
 END xxaj_master_child_cp;  
 /  

Package Body

CREATE OR REPLACE PACKAGE BODY xxaj_master_child_cp
AS
  PROCEDURE parent_prg(p_errbuf OUT VARCHAR2
                      ,p_retcode OUT VARCHAR2)
  IS
    v_req_data   VARCHAR2(30);
    v_request_id NUMBER;
 
  BEGIN
 
    -- Read the value from REQUEST_DATA.  
    --If this is the first run of the program, then this value will be null.
    -- Otherwise, this will be the value that we passed to SET_REQ_GLOBALS on the previous run.
    v_req_data   := fnd_conc_global.request_data;
    fnd_file.put_line(fnd_file.LOG,'v_req_data : '||v_req_data);
 
    -- If equals to 'EXIT_AJ_PARENT', exit the program. 
    IF v_req_data ='EXIT_XXAJ_PARENT' THEN
       RETURN;
    END IF;
 
    FOR i IN 1..5
    LOOP
      -- Submit child program   
      v_request_id   := fnd_request.submit_request(application => 'XXAJ'
                                                  ,program     => 'XXAJ_CHILD'
                                                 ,sub_request => TRUE); --This is the parameter which treats this request as the child request   
 
      fnd_file.put_line(fnd_file.LOG,'Child Request Id '||(i)||':' || v_request_id);
    END LOOP;
 
    -- Here we set the globals to put the program into the PAUSED status on exit
    -- and to save the state in request_data. 
    fnd_conc_global.set_req_globals(conc_status => 'PAUSED'
                                  , request_data => 'EXIT_XXAJ_PARENT');
 
 
  EXCEPTION
    WHEN OTHERS THEN
      fnd_file.put_line(fnd_file.LOG,'Exception: ' || SQLERRM);
  END parent_prg;
 
  PROCEDURE child_prg(p_errbuf OUT VARCHAR2
                     ,p_retcode OUT VARCHAR2)
  IS
  BEGIN
 
    fnd_file.put_line(fnd_file.LOG,'Start');
    DBMS_LOCK.sleep(10);  -- Wait for some time to see the behaviour :)
    fnd_file.put_line(fnd_file.LOG,'End!!!!');
 
  EXCEPTION
    WHEN OTHERS THEN
      fnd_file.put_line(fnd_file.LOG,'Exception: ' || SQLERRM);
  END child_prg; 
 
END  xxaj_master_child_cp;
/

This is how the Find Requests window looks like when the program is submitted.

You can see that the Parent program is in the Paused status.

Feel free to point out if anything is wrong/missing in this blog.