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.