Information Technology Auditor (PA) Next Step Systems US-PA-Wayne Post A Job | Post A Resume

This article describes how to use native dynamic SQL (dynamic SQL for short), in your your applications. You learn simple ways to write programs that can build and process SQL statements "on the fly" at run time.

Introduction

Oracle8i introduces a new feature called native dynamic SQL, an alternative to DBMS_SQL. Using native dynamic SQL, you can place dynamic SQL statements directly into PL/SQL blocks. In most situations, native dynamic SQL can replace DBMS_SQL.

The EXECUTE IMMEDIATE statement parses and immediately executes a dynamic SQL statement or an anonymous PL/SQL block.

Syntax

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

where dynamic_string is a string expression that represents a SQL statement or PL/SQL block, define_variable is a variable that stores a SELECTed column value, record is a user-defined or %ROWTYPE record that stores a SELECTed row, and bind_argument is an expression whose value is passed to the dynamic SQL statement or PL/SQL block.

Except for multi row queries, the string can contain any SQL statement (without the terminator) or any PL/SQL block (with the terminator). The string can also contain placeholders for bind arguments.

Examples

sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING 20, 30, 40;

EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :n' USING 40;

plsql_block := 'BEGIN emp_stuff.raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500;

EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';

DBAsupport.com Home Page