Execute dynamic SQL

Most dynamic SQL statements are handled by the EXECUTE IMMEDIATE statement.

If the dynamic SQL statement is a SELECT statement that returns multiple rows, PL/SQL provides the following two methods for executing dynamic SQL:

• Use the EXECUTE IMMEDIATE statement with the BULK COLLECT INTO clause.

• Use the OPEN FOR, FETCH, or CLOSE clause with a cursor.

EXECUTE IMMEDIATE statement

If the SQL statement is complete, you can directly use the EXECUTE IMMEDIATE statement. You can use placeholders to pass in or out parameters, as follows:

• If the dynamic SQL statement is a SELECT statement that returns at most one record, you can use the INTO clause to specify the output parameters and the USING clause to specify the input parameters.

• If the dynamic SQL statement is a SELECT statement that returns multiple records, you can use the BULK COLLECT INTO clause to specify the output parameters and the USING clause to specify the input parameters.

• If the dynamic SQL statement is a DML clause without RETURNING INTO, all parameters are passed in by using the USING clause.

• If the dynamic SQL statement is a DML clause with RETURNING INTO, you can use the USING clause to specify the input parameters and the RETURNING INTO clause to specify the output parameters.

The following example uses dynamic SQL to change the name of the employee whose ID is 111 to Roger and update the employee's phone number:

obclient> CREATE TABLE emp(  
   empno          NUMBER(4,0),  
   empname        VARCHAR(10),
   job_id         VARCHAR2(200),
   job            VARCHAR(10),    
   deptno         NUMBER(2,0),
   phone_num      NUMBER(20,0)

);
Query OK, 0 rows affected

obclient> INSERT INTO emp VALUES (111,'Ismael','01','AD_ASST',1,'5151244369');
Query OK, 1 row affected

obclient> SELECT empno,empname,phone_num FROM emp
     where empno=111;
+-------+---------+------------+
| EMPNO | EMPNAME | PHONE_NUM  |
+-------+---------+------------+
|   111 | Ismael  | 5151244369 |
+-------+---------+------------+
1 row in set

obclient> DECLARE
        v_id NUMBER := 111;
        v_name  VARCHAR2(20) := 'Roger';
        v_phone VARCHAR2(50);
      BEGIN
        EXECUTE IMMEDIATE 'UPDATE emp SET empname= :NAME WHERE empno = :ID
          RETURNING phone_num INTO :PHONE'
          USING v_name, v_id, OUT v_phone;
        DBMS_OUTPUT.PUT_LINE(v_phone);
      END;
      /
Query OK, 0 rows affected

obclient> SELECT empno,empname,phone_num FROM emp
     where empno=111;
+-------+---------+------------+
| EMPNO | EMPNAME | PHONE_NUM  |
+-------+---------+------------+
|   111 | Roger   | 5151244369 |
+-------+---------+------------+
1 row in set

In this example, three parameters are passed in by using the USING clause, including two input parameters and one output parameter.

You can also use a cursor variable to open dynamic SQL. You can use placeholders and specify variables in the USING clause when you open the cursor. Here is an example:

obclient> SET SERVEROUTPUT ON;
Query OK, 0 rows affected

obclient> DECLARE
       cv SYS_REFCURSOR;
       query_2 VARCHAR2(200) :=
         'SELECT * FROM emp
          where empno = :x';
       v_employees emp%ROWTYPE;
     BEGIN
       OPEN cv FOR query_2  USING 111;
       LOOP
           FETCH cv INTO v_employees;
           EXIT WHEN cv%NOTFOUND;
           DBMS_OUTPUT.PUT_LINE(v_employees.empno||'-'||v_employees.empname);
       END LOOP;
       CLOSE cv;
     END;
     /
Query OK, 0 rows affected

111-Ismael

OPEN FOR, FETCH, and CLOSE clauses

If the dynamic SQL statement is a SELECT statement that returns multiple rows, you can use local dynamic SQL to process it as follows:

• Use the OPEN FOR statement to associate a cursor variable with the dynamic SQL statement. In the USING clause of the OPEN FOR statement, specify a binding variable for each placeholder in the dynamic SQL statement.

• Use the FETCH statement to retrieve one row result, multiple result sets, or all result sets at a time.

• Use the CLOSE statement to close the cursor variable.

Here is an example: retrieve all manager-level employees from the employee information and retrieve the row result set at a time.

obclient> DECLARE
      TYPE EmpCurType  IS REF CURSOR;
      v_emp_cur        EmpCurType;
      emp_rec          emp%ROWTYPE;
      v_st_str         VARCHAR2(200);
      v_e_job          emp.job%TYPE;
    BEGIN
  -- Dynamic SQL statement with placeholders:
      v_st_str := 'SELECT * FROM emp WHERE job_id = :j';

  -- Open the cursor and specify the binding variable in the USING clause:
      OPEN v_emp_cur FOR v_st_str USING 'AD_ASST';

  -- Retrieve a row from the result set:
      LOOP
        FETCH v_emp_cur INTO emp_rec;
        EXIT WHEN v_emp_cur%NOTFOUND;
      END LOOP;

  -- Close the cursor:
      CLOSE v_emp_cur;
    END;
  /
Query OK, 0 rows affected

Repeated placeholder names

If a placeholder name is repeated in a dynamic SQL statement, the way in which the placeholder is associated with a binding variable depends on the type of the dynamic SQL statement.

If the dynamic SQL statement is a PL/SQL anonymous block or a CALL statement, each placeholder name in the USING clause must have a corresponding binding variable. If a placeholder name is repeated, you do not need to repeat its corresponding binding variable. All references to the placeholder name correspond to one binding variable in the USING clause.

If the dynamic SQL statement is not a PL/SQL anonymous block or a CALL statement, the placeholder is associated with the binding variable in the USING clause by position, not by name.