Execute dynamic SQL statements

The EXECUTE IMMEDIATE statement is used to execute most dynamic SQL statements.

For a dynamic statement that returns multiple rows, such as a SELECT statement, PL provides two methods to execute it:

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

• Use the OPEN FOR, FETCH, or CLOSE clause.

EXECUTE IMMEDIATE statement

You can directly use EXECUTE IMMEDIATE to execute a complete SQL statement. Parameters are passed in the following ways and placeholders are required:

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

• For a dynamic SELECT statement that may return multiple rows, you can use the BULK COLLECT INTO clause to specify the output parameters and the USING clause to specify the input parameters.

• For a dynamic DML statement without the RETURNING INTO clause, you use the USING clause to pass all parameters.

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

The following example modifies the name of the employee whose employee ID is 111 to Roger, and synchronizes the phone number of the employee:

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 the example, the USING clause is used to pass three parameters, which include two input parameters and one output parameter.

You can also use cursor variables to enable dynamic SQL statements and use placeholders as well. When you enable a dynamic SQL statement, use the USING clause to specify variables. 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 statements

For a dynamic SQL SELECT statement that returns multiple rows, you can use a local dynamic SQL statement to handle it as follows:

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

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

• Use the CLOSE statement to close cursor variables.

Example: Retrieve employees of the manager level from all employees, and retrieve the 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 bind variables in the USING clause:
      OPEN v_emp_cur FOR v_st_str USING 'AD_ASST';

  -- Fetch rows from the result set at a time:
      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

Duplicate placeholder names

If you use duplicate placeholder names in a dynamic SQL statement, the method of associating placeholders with bind variables is determined by the type of the dynamic SQL statement.

If the dynamic SQL statement is a PL anonymous block or a CALL statement, each placeholder name must have a corresponding bind variable in the USING clause. If you use duplicate placeholder names, you do not need to specify duplicate bind variables. Specifically, all references to a placeholder name correspond only one bind variable in the USING clause.

If the dynamic SQL statement is not a PL anonymous block or a CALL statement, placeholders are associated with bind variables in the USING clause based on locations rather than names.