How to bind IN and OUT parameters in PL/SQL

Suppose that you need to execute dynamic PL/SQL, a stored procedure, with one in and one out parameter:

CREATE OR REPLACE PROCEDURE scott.get_emp_count(p_deptno IN NUMBER, p_count OUT NUMBER)
AS
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM emp
    WHERE deptno=p_deptno;
END get_emp_count;

Now we want to call it in SQL*Plus as follows:

DECLARE
   v_sql VARCHAR2(2000);
   v_count NUMBER;
   v_deptno NUMBER:=10;
BEGIN
   v_sql:='CALL get_emp_count(:0, :1)';
   EXECUTE IMMEDIATE v_sql USING v_deptno, v_count;

   DBMS_OUTPUT.PUT_LINE(v_count);
END;
/
*
ERROR at line 1:
ORA-06536: IN bind variable bound to an OUT position
ORA-06512: at line 7

To make it work we need to use OUT keyword before v_count variable:

DECLARE
   v_sql VARCHAR2(2000);
   v_count NUMBER;
   v_deptno NUMBER:=10;
BEGIN
   v_sql:='CALL get_emp_count(:0, :1)';
   EXECUTE IMMEDIATE v_sql USING v_deptno, OUT v_count;

   DBMS_OUTPUT.PUT_LINE(v_count);
END;
/
3

PL/SQL procedure successfully completed.

Bind parameter mode is similar to a parameter mode in a stored procedure – IN is the default and we don’t have to use it, though we can:

  1  DECLARE
  2     v_sql VARCHAR2(2000);
  3     v_count NUMBER;
  4     v_deptno NUMBER:=10;
  5  BEGIN
  6     v_sql:='CALL get_emp_count(:0, :1)';
  7     EXECUTE IMMEDIATE v_sql USING IN v_deptno, OUT v_count;
  8     DBMS_OUTPUT.PUT_LINE(v_count);
  9* END;
SQL> /
3

PL/SQL procedure successfully completed.

This technique is useful when you don’t know the name of the stored procedure to be executed with EXECUTE IMMEDIATE statement in advance, though you know the parameters.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

One thought on “How to bind IN and OUT parameters in PL/SQL

  1. leo June 27, 2016 / 9:44 am

    Exactly what I was looking for, concise explanation and right to the point. Thanks so much!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s