The execute immediate statement

Use EXECUTE IMMEDIATE to execute (immediately!) the specified SQL statement. Here is the syntax of this statement:

EXECUTE IMMEDIATE SQL_string [INTO { define_variable [, define_variable ]... | record }] [USING [IN | OUT | IN OUT] bind_argument [, [IN | OUT | IN OUT] bind_argument ]...];

where:

 

SQL_string

Is a string expression containing the SQL statement or PL/SQL block.

 

define_variable

Is a variable that receives a column value returned by a query.

 

record

Is a record based on a user-defined TYPE or %ROWTYPE that receives an entire row returned by a query.

 

bind_argument

Is an expression whose value is passed to the SQL statement or PL/SQL block, or an identifier that serves as an input and/or output variable to the function or procedure that is called in the PL/SQL block.

 

INTO clause

Is used for single-row queries; for each column value returned by the query, you must supply an individual variable or field in a record of a compatible type.

 

USING clause

Allows you to supply bind arguments for the SQL string. This clause is used for both dynamic SQL and PL/SQL, which is why you can specify a parameter mode. This usage is relevant only for PL/SQL, however; the default is IN, which is the only kind of bind argument you would have for SQL statements.

You can use EXECUTE IMMEDIATE for any SQL statement or PL/SQL block except for multiple-row queries. If SQL_string ends with a semicolon, it will be treated as a PL/SQL block; otherwise, it will be treated as either DML (Data Manipulation Language—SELECT, INSERT, UPDATE, or DELETE) or DDL (Data Definition Language—e.g., CREATE TABLE). The string may contain placeholders for bind arguments, but you cannot use bind values to pass in the names of schema objects, such as table names or column names.

When the statement is executed, the runtime engine replaces each placeholder (an identifier with a colon in front of it, such as:salary_value) in the SQL string with its corresponding bind argument (by position). You can pass numeric, date, and string expressions. You cannot, however, pass a Boolean, because it is a PL/SQL datatype. Nor can you pass a NULL literal value. Instead, you must pass a variable of the correct type that has a value of NULL.

NDS supports all SQL datatypes. So, for example, define variables and bind arguments can be collections, large objects (LOBs), instances of an object type, and REFs. On the other hand, NDS does not support datatypes that are specific to PL/SQL, such as Booleans, associative arrays, and user-defined record types. The INTO clause may, however, contain a PL/SQL record.

Let's take a look at a few examples:

· Create an index:

EXECUTE IMMEDIATE 'CREATE INDEX emp_u_1 ON employee (last_name)';

It can't get much easier than that, can it?

· Create a stored procedure that will execute anyDDL statement:

· CREATE OR REPLACE PROCEDURE execDDL (ddl_string IN VARCHAR2)· IS· BEGIN· EXECUTE IMMEDIATE ddl_string;END;

With execDDL in place, I can create that same index as follows:

execDDL ('CREATE INDEX emp_u_1 ON employee (last_name)');

· Obtain the count of rows in any table, in any schema, for the specified WHERE clause:

· /* File on web: tabcount.sf */· CREATE OR REPLACE FUNCTION tabCount (· tab IN VARCHAR2,· whr IN VARCHAR2:= NULL,· sch IN VARCHAR2:= NULL)· RETURN INTEGER· IS· retval INTEGER;· BEGIN· EXECUTE IMMEDIATE· 'SELECT COUNT(*) · FROM ' || NVL (sch, USER) || '.' || tab ||· ' WHERE ' || NVL (whr, '1=1')· INTO retval;· RETURN retval;END;

So now I never again have to write SELECT COUNT(*), whether in SQL*Plus or within a PL/SQL program. Instead I can do the following:

BEGIN IF tabCount ('emp', 'deptno = ' || v_dept) > 100 THEN DBMS_OUTPUT.PUT_LINE ('Growing fast!'); END IF;

· Here's a function that lets you update the value of any numeric column in any table. It's a function because it returns the number of rows that have been updated.

· /* File on web: updnval.sf */· CREATE OR REPLACE FUNCTION updNVal (· tab IN VARCHAR2,· col IN VARCHAR2,· val IN NUMBER,· whr IN VARCHAR2:= NULL,· sch IN VARCHAR2:= NULL)· RETURN INTEGER· IS· BEGIN· EXECUTE IMMEDIATE· 'UPDATE ' || NVL (sch, USER) || '.' || tab ||· ' SET ' || col || ' =:the_value · WHERE ' || NVL (whr, '1=1')· USING val;· RETURN SQL%ROWCOUNT;END;

That is a very small amount of code to achieve all that flexibility! This example introduces the bind argument—after the UPDATE statement is parsed, the PL/SQL engine replaces the:the_value placeholder with the value in the val variable. Notice also that I am able to rely on the SQL%ROWCOUNT cursor attribute that I have already been using for static DML statements.

· Suppose that I need to run a different stored procedure at 9 AM each day of the week. Each program's name has the structure DAYNAME_set_schedule. Each procedure has the same four arguments: you pass in employee_id and hour for the first meeting of the day; it returns the name of the employee and the number of appointments for the day. I can use dynamic PL/SQL to handle this situation:

· /* File on web: run9am.sp */· CREATE OR REPLACE PROCEDURE run_9am_procedure (· id_in IN employee.employee_id%TYPE,· hour_in IN INTEGER)· IS· v_apptCount INTEGER;· v_name VARCHAR2(100);· BEGIN· EXECUTE IMMEDIATE· 'BEGIN ' || TO_CHAR (SYSDATE, 'DAY') || · '_set_schedule (:id,:hour,:name,:appts); END;'· USING IN · id_in, IN hour_in, OUT v_name, OUT v_apptCount;· · DBMS_OUTPUT.PUT_LINE (· 'Employee ' || v_name || ' has ' || v_apptCount ||· ' appointments on ' || TO_CHAR (SYSDATE));END;

As you can see, EXECUTE IMMEDIATE provides a very easy and accessible syntax!