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