PL/SQL - Dynamic SQL

> Procedural Languages > PL/SQL - (Procedure Language|PL) SQL

1 - About

SQL statements that are only know until run time are called dynamic SQL statements.

To process a dynamic sql statement, you can use the following Native dynamic SQL statement:

The dynamic SQL use intensively the concept of bind variable to improve performance and scalability.

The Native dynamic SQL code is more compact and much faster than calling the DBMS_SQL package.

Advertising

3 - Parameters, Bind Variable

3.1 - Passing Schema Object Names (Table, ...)

Suppose you need a procedure that accepts the name of any database table, then drops that table from your schema. You must build a string with a statement that includes the object names, then use EXECUTE IMMEDIATE to execute the statement:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;
/

Use concatenation to build the string, rather than trying to pass the table name as a bind variable through the USING clause.

3.2 - Passing Nulls to Dynamic SQL

The literal NULL is not allowed in the USING clause. To work around this restriction, replace the keyword NULL with an uninitialized variable:

DECLARE
   a_null CHAR(1); -- set to NULL automatically at run time
BEGIN
   EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING a_null;
END;
/

4 - Syntax

4.1 - When to Use or Omit the Semicolon with Dynamic SQL

When building up a single SQL statement in a string, do not include any semicolon at the end.

When building up a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block.

For example:

BEGIN
   EXECUTE IMMEDIATE 'dbms_output.put_line(''No semicolon'')';
   EXECUTE IMMEDIATE 'BEGIN dbms_output.put_line(''semicolons''); END;';
END;
Advertising

5 - Documentation

lang/plsql/dynamic_sql.txt · Last modified: 2017/09/28 15:01 by gerardnico