PL/SQL native dynamic SQL does not
implement the cost-saving paradigm

Finally compile the package Employees_Cur_Dynamic_Sql which again exposes just Fetch_All_Rows but now the formal parameter is the whole SQL statement, assumed not known until run time. The implementation is very similar to Employees_Cur_Ref_Cursor. The only difference is that the ref cursor is opened with a VARCHAR2 whose content is not known at compile-time.

Note: In the requirements scenario that this example supports, the SELECT list is assumed fixed, but everything else about the SQL statement is unknown until run-time. This would suggest the use of a strong ref cursor, but this is not yet supported with native dynamic SQL (up to Oracle9i). The attempt to code this fails to compile with PLS-00455: cursor 'MY_STRONG_CURSOR' cannot be used in dynamic SQL.

Create the procedure Show_Rows_For_Dept_Dynamic_Sql. And as above, use two concurrent sessions connected as programmer and observer and step through this test. This shows that the PL/SQL close statement for a native dynamic SQL cursor does the actions corresponding to Dbms_Sql.Close_Cursor. You can see this in "slow motion" as above by activating the line Wait.Go in Employees_Cur_Dynamic_Sql and running the test again.

Similarly, implicit native dynamic SQL cursors thus...

procedure P is
  v_last_name employees.last_name%type;
begin
  execute immediate
    'select last_name from employees where employee_id = 100'
    into v_last_name;
  Dbms_Output.Put_Line ( v_last_name );
end P;

...(both for SELECT and for DML) are not cached for re-use.

Note: Consider the requirements scenario where the SELECT statement is not known until run time, but where it will be repeated many times with different in-bind variables. (This would occur if all but the table name were known at compile-time.) There is currently (up to Oracle9i) no way to use native dynamic SQL to implement the cost-saving paradigm - it does not have the constructs to separate one-time parsing from repeated binding and execution. Nevertheless, Oracle recommends using native dynamic SQL rather than Dbms_Sql. Other factors in the implementations of the two approaches make native dynamic SQL the more performant choice in the majority of cases.