EXECUTE IMMEDIATE

Execute a dynamic SQL statement or anonymous PL/SQL block.

Syntax:

   EXECUTE IMMEDIATE dynamic_sql_string
      [INTO {define_variable,… | INTO record_name}]
         [USING
              [IN|OUT|IN OUT] bind_argument,…]
                  [RETURN[ING] INTO
                                   bind_argument,…];

dynamic_sql_string : The SQL statement string or PL/SQL block 

define_variable : One variable receives each column 
                  value returned by the query.

record_name     : A record based on a user-defined TYPE 
                  or %ROWTYPE that receives an entire row
                  returned by a query

bind_argument   : An expression whose value is passed to the
                  SQL statement or PL/SQL block INTO clause 
                  Use for single-row queries; for each column value
                  returned by the query, you must supply an
                  individual variable or field in a record of
                  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 only relevant for PL/SQL, 
                  however; the default is IN, which is the only
                  kind of bind argument you would have for
                  SQL statements.

You cannot use EXECUTE IMMEDIATE for multiple-row queries.

If "dynamic_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, such as CREATE TABLE).

The "dynamic_sql_string" can 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, pass a Boolean, or a NULL literal value, you can however pass a variable of the correct type that has a value of NULL. Execute Immediate cannot run queries whose length is greater than 32 KB

Example

begin
execute immediate 'purge recyclebin';
end;

"Everyone wants results, but no one is willing to do what it takes to get them" ~ Dirty Harry

Related Oracle Commands:

DBMS_SQL
EXEC
Equivalent SQL Server command: exec sp_executesql 'sql Statement'


 
Copyright © 1999-2024 SS64.com
Some rights reserved