Lab 20.1 EXECUTE IMMEDIATE Statements

Lab Objective

After this Lab, you will be able to:

Use the EXECUTE IMMEDIATE Statement

Generally, dynamic SQL statements are built by your program and stored as character strings based on the parameters specified at run-time. It is important to note that these strings must contain valid SQL statements or PL/SQL code. Consider the following example of dynamic SQL statement shown below.

FOR EXAMPLE

 'SELECT first_name, last_name FROM student WHERE student_id = :student_id' 

The SELECT statement above returns a student's first and last name for a given student ID. The value of the student ID is not known in advance and is specified with the help of a bind argument , :student_id . The bind argument is a placeholder for an undeclared identifier, and its name must be prefixed by a colon . As a result, PL/SQL does not differentiate between the following statements:

 'SELECT first_name, last_name FROM student WHERE student_id = :student_id' 'SELECT first_name, last_name FROM student WHERE student_id = :id' 

To process dynamic SQL statements, you will use EXECUTE IMMEDIATE or OPEN-FOR, FETCH, and CLOSE statements.

To improve performance of dynamic SQL statements you can also use BULK EXECUTE IMMEDIATE, BULK FETCH, FORALL, and COLLECT INTO statements. However, these statements are outside the scope of this book and are not covered. You can find detailed explanations and examples of their usage in Oracle Help available on-line.

The EXECUTE IMMEDIATE Statement

The EXECUTE IMMDEDIATE statement parses a dynamic statement or a PL/SQL block for immediate execution and has the structure shown below (the reserved words and phrases surrounded by brackets are optional):

 EXECUTE IMMEDIATE  dynamic_SQL_string  [INTO defined_variable1, defined_variable2, ...] [USING [IN  OUT  IN OUT] bind_argument1, bind_argument2, ...] [RETURNING INTO  RETURN bind_argument1, bind_argument2, ...] 

The dynamic_SQL_string is a string that contains a valid SQL statement or a PL/SQL block. The INTO clause contains the list of predefined variables that hold values returned by the SELECT statement. This clause is used when a dynamic SQL statement returns a single row similar to a static SELECT INTO statement. Next , the USING clause contains a list of bind arguments whose values are passed to the dynamic SQL statement or PL/SQL block. The IN, OUT, and IN OUT are modes for bind arguments. If no mode is specified, all bind arguments listed in the USING clause are in the IN mode. Finally, the RETURNING INTO or RETURN clause contains a list of bind arguments that store values returned by the dynamic SQL statement or PL/SQL block. Similar to the USING clause, the RETURNING INTO clause may also contain various argument modes; however, if no mode is specified, all bind arguments are in the OUT mode.

Note that when an EXECUTE IMMEDIATE statement contains both USING and RETURNING INTO clauses, the USING clause may specify only IN arguments.

FOR EXAMPLE

 DECLARE    sql_stmt VARCHAR2(100);    plsql_block VARCHAR2(300);    v_zip VARCHAR2(5) := '11106';    v_total_students NUMBER;    v_new_zip VARCHAR2(5);    v_student_id NUMBER := 151; BEGIN    -- Create table MY_STUDENT    sql_stmt := 'CREATE TABLE my_student '                'AS SELECT * FROM student WHERE zip = 'v_zip;    EXECUTE IMMEDIATE sql_stmt;    -- Select total number of records from MY_STUDENT table    -- and display results on the screen    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_student'    INTO v_total_students;    DBMS_OUTPUT.PUT_LINE ('Students added: 'v_total_students);    -- Select current date and display it on the screen    plsql_block := 'DECLARE '                                                                      '   v_date DATE; '                                                              'BEGIN '                                                                        '   SELECT SYSDATE INTO v_date FROM DUAL; '                                     '   DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date, ''DD-MON- YYYY''));'                   'END;';    EXECUTE IMMEDIATE plsql_block;    -- Update record in MY_STUDENT table    sql_stmt := 'UPDATE my_student SET zip = 11105 WHERE student_id = :1 '                'RETURNING zip INTO :2';    EXECUTE IMMEDIATE sql_stmt USING v_student_id RETURNING INTO v_new_zip;    DBMS_OUTPUT.PUT_LINE ('New zip code: 'v_new_zip); END; 

The script above contains several examples of dynamic SQL.

First, you create the table MY_STUDENT and populate it with records for a specified value of zip code. It is important to note that the variable v_zip is concatenated with the CREATE statement instead of being passed in as a bind argument. This point is illustrated in the next example.

Second, you select the total number of students added to the MY_STUDENT table and display it on the screen. You use the INTO option with the EXECUTE IMMEDIATE statement because the SELECT statement returns a single row.

Third, you create a simple PL/SQL block where you select the current date and display it on the screen. Because the PL/SQL block does not contain any bind arguments, the EXECUTE IMMEDIATE statement is used in its simplest form.

Finally, you update MY_STUDENT table for a given student ID and return a new value of zip code via the RETURNING statement. So, the EXECUTE IMMEDIATE command contains both USING and RETURNING INTO options. The USING option allows you to pass a value of student ID to the UPDATE statement at run-time, and the RETURNING INTO option allows you to pass a new value of zip code from the UPDATE statement into your program.

When run, this example produces the output shown below:

  Students added: 4   22-JUN-2003   New zip code: 11105   PL/SQL procedure successfully completed.  

Next, consider the simplified yet incorrect version of the example above. Changes are shown in bold letters .

FOR EXAMPLE

 DECLARE    sql_stmt VARCHAR2(100);    v_zip VARCHAR2(5) := '11106';    v_total_students NUMBER; BEGIN  -- Drop table MY_STUDENT   EXECUTE IMMEDIATE 'DROP TABLE my_student';  -- Create table MY_STUDENT    sql_stmt := 'CREATE TABLE my_student '  'AS SELECT * FROM student '   'WHERE zip = :zip';  EXECUTE IMMEDIATE sql_stmt USING v_zip;    -- Select total number of records from MY_STUDENT table    -- and display results on the screen    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_student'    INTO v_total_students;    DBMS_OUTPUT.PUT_LINE ('Students added: '    v_total_students); END; 

First, you drop the table MY_STUDENT created in the previous version of the example. Next, you recreate the MY_STUDENT table, but, in this case, you use a bind argument to pass a value of zip code to the CREATE statement at run-time.

When run, this example produces the following error:

  DECLARE   *   ERROR at line 1:   ORA-01027: bind variables not allowed for data definition operations   ORA-06512: at line 12  

A CREATE TABLE statement is a data definition statement, and as a result, it cannot accept any bind arguments.

Next, consider another simplified version of the same example that causes a syntax error as well. In this version, you pass table name as a bind argument to the SELECT statement. Changes are shown in bold letters.

FOR EXAMPLE

 DECLARE    sql_stmt  VARCHAR2(100);    v_zip  VARCHAR2(5) := '11106';    v_total_students NUMBER; BEGIN    -- Create table MY_STUDENT    sql_stmt := 'CREATE TABLE my_student '                'AS SELECT * FROM student '                'WHERE zip = v_zip;    EXECUTE IMMEDIATE sql_stmt;    -- Select total number of records from MY_STUDENT table    -- and display results on the screen  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :my_table'   INTO v_total_students   USING 'my_student';  DBMS_OUTPUT.PUT_LINE ('Students added: '    v_total_students); END; 

When run, this example causes the following error:

  DECLARE   *   ERROR at line 1:   ORA-00903: invalid table name   ORA-06512: at line 13  

This example causes an error because you cannot pass names of schema objects to the dynamic SQL statements as bind arguments . In order to provide table name at the run time, you need to concatenate it with the SELECT statement as shown below:

  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM 'my_table   INTO v_total_students;  

As mentioned earlier, a dynamic SQL string can contain any SQL statement or PL/SQL block. However, unlike static SQL statements, a dynamic SQL statement should not be terminated by the semicolon (;). Similarly, a dynamic PL/SQL block should not be terminated by the forward slash (/). Consider a different version of the same example where the SELECT statement is terminated by the semicolon. Changes are shown in bold letters. Note that if you have created the MY_STUDENT table based on the corrected version of the script above, you need to drop it prior to running the script below. Otherwise, the error message generated by the example will differ from the error message shown below.

FOR EXAMPLE

 DECLARE    sql_stmt VARCHAR2(100);    v_zip VARCHAR2(5) := '11106';    v_total_students NUMBER; BEGIN    -- Create table MY_STUDENT    sql_stmt := 'CREATE TABLE my_student '                'AS SELECT * FROM student '                'WHERE zip = v_zip;    EXECUTE IMMEDIATE sql_stmt;    -- Select total number of records from MY_STUDENT table    -- and display results on the screen    EXECUTE IMMEDIATE  'SELECT COUNT(*) FROM my_student;'  INTO v_total_students;    DBMS_OUTPUT.PUT_LINE ('Students added: '    v_total_students); END; 

When run, this example produces the following error:

  DECLARE   *   ERROR at line 1:   ORA-00911: invalid character   ORA-06512: at line 13  

The semicolon added to the SELECT statement is treated as an invalid character when the statement is created dynamically. A somewhat similar error is generated when a PL/SQL block is terminated by a forward slash as demonstrated below. Changes are shown in bold letters.

FOR EXAMPLE

 DECLARE    plsql_block VARCHAR2(300); BEGIN    -- Select current date and display it on the screen    plsql_block := 'DECLARE '                                                                      '   v_date DATE; '                                                              'BEGIN '                                                                        '   SELECT SYSDATE INTO v_date FROM DUAL; '                                     '   DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date, ''DD-MON-YYYY''));'                   'END;'  '/';  EXECUTE IMMEDIATE plsql_block; END;  DECLARE   *   ERROR at line 1:   ORA-06550: line 1, column 133:   PLS-00103: Encountered the symbol "/" The symbol "/" was ignored.   ORA-06512: at line 12  

Passing NULLs

In some cases you may need to pass a NULL value to a dynamic SQL statement as a value for a bind argument. For example, you need to update the COURSE table so that the PREREQUISITE column is set to NULL. You can accomplish this with the following dynamic SQL and the EXECUTE IMMEDIATE statement.

FOR EXAMPLE

 DECLARE    sql_stmt VARCHAR2(100); BEGIN    sql_stmt := 'UPDATE course'                '   SET prerequisite = :some_value';    EXECUTE IMMEDIATE sql_stmt    USING NULL; END; 

However, when run, this script causes the following error

  USING NULL;   *   ERROR at line 7:   ORA-06550: line 7, column 10:   PLS-00457: expressions have to be of SQL types   ORA-06550: line 6, column 4:   PL/SQL: Statement ignored  

This error is generated because the literal NULL in the USING clause is not recognized as one of the SQL types. In order to pass a NULL value to the dynamic SQL statement, this example should be modified as follows (changes are shown in bold letters).

FOR EXAMPLE

 DECLARE    sql_stmt VARCHAR2(100);  v_null VARCHAR2(1);  BEGIN    sql_stmt := 'UPDATE course'                '   SET prerequisite = :some_value';    EXECUTE IMMEDIATE sql_stmt  USING v_null;  END; 

To correct the script, you add an initialized variable v_null and replace the literal NULL in the USING clause with this variable. Because the variable v_null has not been initialized, its value remains NULL, and it is passed to the dynamic UPDATE statement at run-time. As a result, this version of the script completes without any errors.