Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Native Dynamic SQL

Dynamic SQL allows an application to run SQL statements whose contents are not known until runtime. The statement is built up as a string by the application and is then passed to the server, in a similar way to the ADO interface in VB.

Generally dynamic SQL is slower than static SQL so it should not be used unless absolutely necessary. Also, since syntax checking and object validation cannot be done until runtime, code containing large amounts of dynamic SQL may be littered with mistakes but still compile.

The main advantage of dynamic SQL is that it allows you to perform DDL commands that are not supported directly within PL/SQL, such as creating tables. It also allows you to access objects that will not exist until runtime.

DDL Operations

Commands such as DDL operations that are not directly supported by PL/SQL can be performed using dynamic SQL.

BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE my_table;';
END;
/

Single Row Queries

The following is a silly example of a single row query.

DECLARE
  l_sql    VARCHAR2(100);
  l_ename  emp.ename%TYPE;
BEGIN
  l_sql := 'SELECT ename FROM emp WHERE empno = 1234';
  EXECUTE IMMEDIATE l_sql INTO l_ename;
END;
/

The USING clause allows bind variables to be used.

DECLARE
  l_sql    VARCHAR2(100);
  l_ename  emp.ename%TYPE;
BEGIN
  l_sql := 'SELECT ename FROM emp WHERE empno = :empno';
  EXECUTE IMMEDIATE l_sql INTO l_ename USING 1234;
END;
/

Dynamic Cursors

Sometimes the definition of the cursor may not be known until runtime. The following example shows this along with access of an object that it not currently present. It should compile in any schema as there are no direct references to database objects until runtime.

CREATE OR REPLACE 
PROCEDURE DynamicCursor (p_parameter IN VARCHAR2) IS
  TYPE cur_typ IS REF CURSOR;
  c_cursor cur_typ;
  l_query  VARCHAR2(1000);
  l_text   VARCHAR2(100);
BEGIN

  IF Length(p_parameter) > 10 THEN
    l_query := 'SELECT ''The parameter ('' || :parameter || '') is too long'' AS text FROM dual';
  ELSE
    l_query := 'SELECT first_name || '' '' || last_name FROM users WHERE user_type = :parameter';
  END IF;
  
  OPEN c_cursor FOR l_query USING p_parameter;
  LOOP
    FETCH c_cursor INTO l_text;
    EXIT WHEN c_cursor%NOTFOUND;
    -- process row here
  END LOOP;
  CLOSE c_cursor;
END;
/

Native Dynamic SQL vs DBMS_SQL

Native Dynamic SQL DBMS_SQL
Easy to use and concise. Often long-winded and awkward.
PL/SQL interpreter has built in support for Native Dynamic SQL so it is more efficient than DBMS_SQL. DBMS_SQL uses a Procedural API so it is generally slower than Native Dynamic SQL.
Supports user defined types. Does not support user defined types.
Supports FETCH INTO record types Does not support FETCH INTO record types
Not supported in client site code. Supported in client side code.
Does not support DESCRIBE_COLUMNS Supports DESCRIBE_COLUMNS
Does not support bulk Dynamic SQL, but it can be faked by placing all statements in a PL/SQL block. Supports bulk Dynamic SQL.
Only supports Single row Updates/Deletes with RETURNING clause. Supports Single and Multiple row Updates/Deletes with RETURNING clause.
Does not support SQL statements bigger than 32K Does support SQL statements bigger than 32K
Parse required for every execution Parse once, execute many possible

Hope this helps. Regards Tim...

Back to the Top.