PL/SQL - Dynamic SQL

Card Puncher Data Processing

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 apps/search/search.jsp package.

Parameters, Bind Variable

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.

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;
/

Syntax

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;

Documentation





Discover More
Card Puncher Data Processing
How you can use a bind variable in SQLPlus

This page is specifically about the usage of SQL bind variable in SQLPlus.
Card Puncher Data Processing
PL/SQL - OPEN-FOR, FETCH, and CLOSE statements

The OPEN-FOR statement executes the query associated with a cursor variable. It's an important statement of the dynamic sql Management. It allocates database resources to process the query and identifies...
Card Puncher Data Processing
PL/SQL - (Procedure Language|PL) SQL

PlSql is the development language of the oracle database. SQL was designed from the start to operate on SETS (ie parallel task) whereas PL/SQL brought a lot in terms of exception handling. PL/SQL...
Card Puncher Data Processing
PL/SQL - Execute Immediate

The immediate statement is used in several areas : SQL dynamic for DML (SELECT, UPDATE, INSERT, DELETE) and DDL (CREATE, ALTER, DROP) Execution of PlSql program. You can use the RETURNING...



Share this page:
Follow us:
Task Runner