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
• Single Row Queries
• Dynamic Cursors
• Native Dynamic SQL vs DBMS_SQL

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

Hope this helps. Regards Tim...

Back to the Top.

Back to normal view: https://oracle-base.com/articles/8i/native-dynamic-sql