PL/SQL - Dynamic 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.

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

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:

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

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:

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

5 - Documentation

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap