Using dynamic SQL v15

Dynamic SQL is a technique that lets you execute SQL commands that aren't known until the commands are about to be executed. In static SQL commands, the full command (with the exception of variables) must be known and coded into the program before the program can begin to execute. Using dynamic SQL, the executed SQL can change during program runtime.

In addition, dynamic SQL is the only method by which data definition commands, such as CREATE TABLE, can be executed from an SPL program.

However, the runtime performance of dynamic SQL is slower than static SQL.

Syntax

The EXECUTE IMMEDIATE command is used to run SQL commands dynamically:

EXECUTE IMMEDIATE '<sql_expression>;'
  [ INTO { <variable> [, ...] | <record> } ]
  [ USING {[<bind_type>] <bind_argument>} [, ...]} ];

Where:

• sql_expression is a string expression containing the SQL command to dynamically execute
• variable receives the output of the result set, typically from a SELECT command, created as a result of executing the SQL command in sql_expression. The number, order, and type of variables must match the number and order and be type-compatible with the fields of the result set.

When using the USING clause, the value of expression is passed to a placeholder. Placeholders appear embedded in the SQL command in sql_expression where you can use variables. Placeholders are denoted by an identifier with a colon (:) prefix, for example, :name. The number and order of the evaluated expressions must match the number, order of the placeholders in sql_expression. The resulting data types must also be compatible with the placeholders. You don't declare placeholders in the SPL program. They appear only in sql_expression.

Currently bind_type is ignored, and bind_argument is treated as IN OUT.

Alternatively, a record can be specified as long as the record’s fields match the number and order and are type-compatible with the result set.

When using the INTO clause, exactly one row must be returned in the result set. Otherwise an exception occurs. When using the USING clause, the value of expression is passed to a placeholder. Placeholders appear embedded in the SQL command in sql_expression where variables can be used. Placeholders are denoted by an identifier with a colon (:) prefix, such as :name. The number, order, and resultant data types of the evaluated expressions must match the number and order and be type-compatible with the placeholders in sql_expression.

Placeholders aren't declared anywhere in the SPL program. They appear only in sql_expression.

Currently all options for bind_type are ignored and bind_argument is treated as IN OUT.

Example: SQL commands as string literals

This example shows basic dynamic SQL commands as string literals:

DECLARE
    v_sql           VARCHAR2(50);
BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE job (jobno NUMBER(3),' ||
        ' jname VARCHAR2(9))';
    v_sql := 'INSERT INTO job VALUES (100, ''ANALYST'')';
    EXECUTE IMMEDIATE v_sql;
    v_sql := 'INSERT INTO job VALUES (200, ''CLERK'')';
    EXECUTE IMMEDIATE v_sql;
END;

This example uses the USING clause to pass values to placeholders in the SQL string:

DECLARE
    v_sql           VARCHAR2(50) := 'INSERT INTO job VALUES ' ||
                        '(:p_jobno, :p_jname)';
    v_jobno         job.jobno%TYPE;
    v_jname         job.jname%TYPE;
BEGIN
    v_jobno := 300;
    v_jname := 'MANAGER';
    EXECUTE IMMEDIATE v_sql USING v_jobno, v_jname;
    v_jobno := 400;
    v_jname := 'SALESMAN';
    EXECUTE IMMEDIATE v_sql USING v_jobno, v_jname;
    v_jobno := 500;
    v_jname := 'PRESIDENT';
    EXECUTE IMMEDIATE v_sql USING v_jobno, v_jname;
END;

This example shows both the INTO and USING clauses. The last execution of the SELECT command returns the results into a record instead of individual variables.

DECLARE
    v_sql           VARCHAR2(60);
    v_jobno         job.jobno%TYPE;
    v_jname         job.jname%TYPE;
    r_job           job%ROWTYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('JOBNO    JNAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    v_sql := 'SELECT jobno, jname FROM job WHERE jobno = :p_jobno';
    EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 100;
    DBMS_OUTPUT.PUT_LINE(v_jobno || '      ' || v_jname);
    EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 200;
    DBMS_OUTPUT.PUT_LINE(v_jobno || '      ' || v_jname);
    EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 300;
    DBMS_OUTPUT.PUT_LINE(v_jobno || '      ' || v_jname);
    EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 400;
    DBMS_OUTPUT.PUT_LINE(v_jobno || '      ' || v_jname);
    EXECUTE IMMEDIATE v_sql INTO r_job USING 500;
    DBMS_OUTPUT.PUT_LINE(r_job.jobno || '      ' || r_job.jname);
END;

The following is the output from this anonymous block:

JOBNO    JNAME
-----    -------
100      ANALYST
200      CLERK
300      MANAGER
400      SALESMAN
500      PRESIDENT

You can use the BULK COLLECT clause to assemble the result set from an EXECUTE IMMEDIATE statement into a named collection. See Using the BULK COLLECT clause, EXECUTE IMMEDIATE BULK COLLECT for more information.