home

Doing SQL from PL/SQL: Best and Worst Practices

Comments in the embedded SQL are preserved in the generated SQL only if they use the
/*+ ... */ hint syntax.

When writing an embedded SQL statement, always establish an alias for each
from list item and always qualify each column with the appropriate alias. Always
qualify the name of every identifier that you intend the be resolved in the
current PL/SQL unit with the name of the block in which it is declared. If this
is a block statement, give it a name using a label. The names of the aliases and
the PL/SQL bocks must all be different. This inoculates against name capture
when the referenced tables are changed and, as a consequence, increases the
likelihood that the fine-grained dependency analysis will conclude that the
PL/SQL unit need not be invalidated.

-- Code_1 Basic_Embedded_Sql.sql
for j in 1..10 loop
v1 := f(j);
insert into t(PK, v1) values(j, b.v1);
end loop;
commit

Here, b is the name of the block in which the variable v1 is declared

-- Code_2 Many_Row_Select.sql
for r in (
select a.PK, a.v1
from t a
where a.PK > Some_Value
order by a.PK)
loop
Process_One_Record(r);
end loop;

-- Code_3 Single_Row_DML.sql
<<b>>declare
Some_Value t.PK%type := 42;
The_Result t%rowtype;
begin
select a.*
into b.The_Result
from t a
where a.PK = b.Some_Value;
The_Result.v1 := 'New text';
update t a
set row = b.The_Result
where a.PK = b.The_Result.PK;
The_Result.PK := -Some_Value;
insert into t
values The_Result;
end;

-- Code_6 Fine_Grained_Dependency_Test.sql
select v1
into l_v1
from t where PK = p_PK;

-- Code_7 Single_Row_DML.sql
<<b>>declare
Some_Value t.PK%type := 42;
The_Result t%rowtype;
begin
select b.*
into b.The_Result
from t b
where b.PK = b.Some_Value;
DBMS_Output.Put_Line(The_Result.n1);
end;

Code_8 shows a simple example of native dynamic SQL. The use of the word
native in the name of the method denotes the fact that it is implemented as a
PL/SQL language feature.
Notice that the alter session SQL statement is not supported by embedded SQL
and so the use of a method where the PL/SQL compiler does not analyze the
SQL statement (as Code_9 dramatically demonstrates) is mandated. The term
dynamic SQL is universally used to denote such a method, but the word
“dynamic” is arguably misleading. It was chosen because the text of the
SQL statement that is executed this way may be constructed at run time; but, of
course, it need not be. In Code_8, the SQL statement is fixed at compile time; this
is emphasized by the use of the constant keyword.
A generic PL/SQL best practice principle urges this:
Principle_2
Use the constant keyword in the declaration of any variable that is not changed
after its initialization21. Following this principle has no penalty because the
worst that can happen is that code that attempts to change a constant will fail to
compile — and this error will sharpen the programmer’s thinking. The
principle has a clear advantage for readability and correctness22.
In the context of dynamic SQL, it is especially valuable to declare the text of the
SQL statement as a constant when this is possible because doing so reduces the
surface area of attack for SQL injection23.
Notice, in this connection, that p’s authid property is explicitly set to Current_User.
If the authid clause is omitted, then the default value for the property, Definer, is
20. A definer’s rights PL/SQL unit always sees just those privileges that have been granted
explicitly to its Owner together with those that have been granted explicitly to public. An
invoker’s rights PL/SQL unit sees privileges that depend on the state of the call stack. When
a definer’s rights PL/SQL unit is on the stack, then the invoker’s rights PL/SQL unit sees
exactly the same privileges as the definer’s rights PL/SQL unit, or view, that is closest on the
stack. When only invoker’s rights PL/SQL units are on the call stack, each sees those privileges
that the Current_User has directly and those that the Current_User has via public together with
those that the Current_User has via all currently enabled roles. Therefore, even when an
invoker’s rights PL/SQL unit identifies an object using a schema-qualified name, ORA-00942
might still occur at run time.
21. It is possible for the PL/SQL compiler to detect this case (except when the variable is declared
at global level in a package spec). Enhancement request 6621216 asks for a compiler warning
for this case.
22. Using the constant keyword can, under some circumstances, tell the PL/SQL compiler that
particular optimizations are safe where, without this information, they would have to be
assumed to be unsafe.
-- Code_8 Basic_NDS.txt

   procedure p authid Current_User is
   SQL_Statement constant varchar2(80) := q'[
   alter session
   set NLS_Date_Format = 'dd-Mon-yyyy hh24:mi:ss'
   ]';
   begin
   execute immediate SQL_Statement;
   DBMS_Output.Put_Line(Sysdate());
   end p;


Principle_2
Use the constant keyword in the declaration of any variable that is not changed
after its initialization21. Following this principle has no penalty because the
worst that can happen is that code that attempts to change a constant will fail to
compile — and this error will sharpen the programmer’s thinking. The
principle has a clear advantage for readability and correctness22.



Principle_3
Always specify the authid property explicitly in every PL/SQL unit; choose
between definer’s rights and invoker’s rights after a careful analysis of the
purpose of the unit24.

Code_9 shows a counter-example of native dynamic SQL.

- Code_9 Basic_NDS.txt

procedure p(Input in varchar2) authid Current_User is

SQL_Statement constant varchar2(80) := 'Mary had... ';

begin

execute immediate SQL_Statement||Input;

end p;




-- Code_10 NDS_Select_Into.sql
procedure b(The_Table in varchar2, PK t.PK%type)
authid Current_User
is
Template constant varchar2(200) := '
select a.v1 from &&t a where a.PK = :b1';
Stmt constant varchar2(200) := Replace(
Template, '&&t',
Sys.DBMS_Assert.Simple_Sql_Name(The_Table));
v1 t.v1%type;
begin
execute immediate Stmt into v1 using PK;
...
end b;

Principle_4
References to objects that Oracle Corporation ships with Oracle Database
should be dot-qualified with the Owner. (This is frequently, but not always, Sys.)
This preserves the intended meaning even if a local object, whose name
collides with that of the intended object, is created in the schema which will be
current when name resolution is done.

-- Code_11 NDS_Select_Into.sql
b('t', 42);
b('T', 42);
b('"T"', 42);

The invocation shown in Code_12 fails with ORA-44003.

-- Code_12 NDS_Select_Into.sql
b('"USR"."T"', 42);

-- Code_13 NDS_Select_Into.sql
select a.v1 from "USR"."T" a where a.PK = :b1

Principle_5

Strive always to use only SQL statements whose text is fixed at compile time.
For select, insert, update, delete, merge, or lock table statements, use embedded SQL.
For other kinds of statement, use native dynamic SQL. When the
SQL statement text cannot be fixed at compile time, strive to use a fixed syntax
template and limit the run-time variation to the provision of names. (This
implies using placeholders and making the small effort to program the
binding.) For the names of schema objects and within-object identifiers like
column names, use Sys.DBMS_Assert.Simple_Sql_Name(). If exceptional
requirements mandate the use of a literal value rather than a placeholder, use
Sys.DBMS_Assert.Enquote_Literal(). For other values (like, for example, the
value for NLS_Date_Format in Code_8) construct it programatically in response
to parameterized user input.
Finally in this section, recall that execute immediate is not the only construct that
implements native dynamic SQL; the the open Cur for

Code_17 uses the DBMS_Sql API.
Notice that the calls to Open_Cursor(), Parse(), Define_Column(), and Close_Cursor()
are done outside the loop while the calls to Bind_Variable() and to
Execute_And_Fetch() are done inside the loop. This saves the cost of repeatedly
parsing the same SQL statement30.
Code_18 uses native dynamic SQL.
Notice how much shorter and more transparent Code_18 is than Code_17; this, of
course, improves that probability that correctly expresses the programmer’s
intention. Moreover, Code_18 runs about twice as fast as Code_17 on a 11,000
row test table, and at about the same speed as the equivalent embedded SQL
approach.
However, the DBMS_Sql API supports some requirements for executing a
SQL statement that cannot be met by native dynamic SQL31. These are they:
30. The call to Parse() attempts to find a sharable SQL structure in the shared pool with the same
statement text that has the same meaning. If none is found, a so-called hard parse occurs. This
is famously expensive. But even the task of establishing that there does already exist a suitable
shareable structure, the so-called soft parse, incurs a cost. A re-work of Code_17 that moves the
Open_Cursor(), Parse(), Define_Column(), and Close_Cursor() calls into the loop runs about three
times as slowly as does Code_17 as presented on the 11,000 row test table used for the
experiment. (Examination of appropriate statistics shows that the repeated parsing is indeed
soft.)
31. Conversely, almost every requirement for executing a SQL statement that can be met by
native dynamic SQL can also be met by the DBMS_Sql API. Oracle Database 11g brought a
number of enhancements to the DBMS_Sql API: Parse() has new overload with a clob formal
for the SQL statement; the select list may include columns of user-defined types; bind
arguments of user-defined types are supported; and a DBMS_Sql numeric cursor may by
transformed to a ref cursor. (A ref cursor may also be transformed to a DBMS_Sql numeric cursor.)
There is one exception: the select list cannot be bulk fetched into a collection whose datatype is
user-defined; rather, one of the collection types defined in the DBMS_Sql package spec must
be used.
-- Code_17 DBMS_Sql_Vs_Exec_Im.sql
declare
Cur integer := DBMS_Sql.Open_Cursor(Security_Level=>2);
Dummy integer;
begin
DBMS_Sql.Parse(Cur, Stmt, DBMS_Sql.Native);
DBMS_Sql.Define_Column(Cur, 1, n1);
for j in 1..No_Of_Rows loop
DBMS_Sql.Bind_Variable(Cur, ':b1', j);
Dummy := DBMS_Sql.Execute_And_Fetch(Cur, true);
DBMS_Sql.Column_Value(Cur, 1, n1);
...
end loop;
DBMS_Sql.Close_Cursor(Cur);
end;

-- Code_18 DBMS_Sql_Vs_Exec_Im.sql
for j in 1..No_Of_Rows loop
execute immediate Stmt
into n1 using j;
...
end loop;

-- Code_19 Returning_Into.sql

Stmt constant varchar2(200) := q'[
update t
set t.v1 = 'New '||t.v1
where t.PK = :i1
returning t.v1 into :o1]';
begin
...
execute immediate Stmt using in PK, out v1;

Principle_6
For dynamic SQL, always use native dynamic SQL except when its
functionality is insufficient; only then, use the DBMS_Sql API. For select, insert,
update, delete, and merge statements, native dynamic SQL is insufficient when the
SQL statement has placeholders or select list items that are not known at
compile time33. For other kinds of SQL statement, native dynamic SQL is
insufficient when the operation is to be done in a remote database.

...
...
Stmt constant varchar2(200) := q'[
update t
set t.v1 = 'New '||t.v1
where t.PK = :i1
returning t.v1 into :o1]';
begin
...
execute immediate Stmt using in PK, out v1;
...
...
Stmt constant varchar2(200) := q'[
update t
set t.v1 = 'New '||t.v1
where t.PK = :i1
returning t.v1 into :o1]';
begin
...
execute immediate Stmt using in PK, out v1;
...

Principle_7
Avoid using concatenated literals in a dynamically created SQL statement;
rather, use placeholders in place of literals, and then at run time bind the values
that would have been literals. This maximizes the reuse of
sharable SQL structures.

-- Code_20 Explicit_Cursor_vs_Cursor_Variable.sql
package Pkg1 is
type Result_t is record(PK t.PK%type, v1 t.v1%type);
cursor Cur_Proc(PK in t.PK%type) return Result_t;
...
end Pkg1;

• strong ref cursor
This is a datatype declared, for example, as is Strong_Cur_t in Code_23.
A strong ref cursor is specific about the number and the datatypes of the select list
items that its select statement must define. A cursor variable whose datatype is a
strong ref cursor can be opened only using embedded SQL.

• weak ref cursor
This is a datatype declared, for example, as is Weak_Cur_t in Code_24.

-- Code_24 Explicit_Cursor_vs_Cursor_Variable.sql
type Weak_Cur_t is ref cursor;

-- Code_25 Explicit_Cursor_vs_Cursor_Variable.sql
package Pkg2 is
type Result_t is record(PK t.PK%type, v1 t.v1%type);
type Cur_t is ref cursor
$if $$Embedded $then return Result_t;
$else ;
$end
function New_Cursor(
PK in t.PK%type)
return Cur_t;
...
end Pkg2;

-- Code_26 Explicit_Cursor_vs_Cursor_Variable.sql
package body Pkg2 is
function New_Cursor(
PK in t.PK%type)
return Cur_t
is
Cur_Var Cur_t;
begin
open Cur_Var for
$if $$Embedded $then
select a.PK, a.v1
from t a
where a.PK > New_Cursor.PK
order by a.PK;
$else
'
select a.PK, a.v1
from t a
where a.PK > :b1
order by a.PK'
using in New_Cursor.PK;
$end
return Cur_Var;
end New_Cursor;
...
end Pkg2;

-- Code_28 RC_Producer_Consumer.sql
if Cur_Var%IsOpen then
close Cur_Var;
end if;

Principle_9
When the approaches that this paper recommends are followed, the only
useful explicit cursor attribute is Cur%IsOpen. There is never a need to use the
other explicit cursor attributes. The only scalar implicit cursor attribute of interest is
Sql%RowCount. Always observe this in the PL/SQL statement that immediately
follows the statement that executes the SQL statement of interest using an
implicit cursor. The same rationale holds for the Sql%Bulk_RowCount collection.
Sql%Bulk_Exceptions must be used only in the exception handler for the
Bulk_Errors exception; place this in a block statement that has the forall statement
as the only statement in its executable section.

Principle_10
When discussing a PL/SQL program, and this includes discussing it with
oneself, commenting it, and writing its external documentation, aim to avoid
the unqualified use of “cursor”. Rather, use the appropriate term of art: session
cursor, implicit cursor, explicit cursor, cursor variable, or DBMS_Sql numeric cursor. The
discipline will improve the quality of your thought and will probably, therefore,
improve the quality of your programs.

-- Code_29 Many_Row_Select.sql
-- Cur is already open here.
-- The fetch syntax is the same for
-- an explicit cursor and a cursor variable.
loop
fetch Cur bulk collect into Results limit Batchsize; -- The for loop doesn't run when Results.Count() = 0
for j in 1..Results.Count() loop
Process_One_Record(Results(j));
end loop;
exit when Results.Count() < Batchsize;
end loop;
close Cur;

-- Code_31 Entire_Bulk_Fetch.sql
...
$if $$Approach = 1 $then
cursor Cur is
select a.PK, a.v1
from t a
where a.PK > b.Some_Value
order by a.PK;
$elsif $$Approach = 2 or $$Approach = 3 $then
Cur Sys_Refcursor;
$if $$Approach = 3 $then
Stmt constant varchar2(200) := '
select a.PK, a.v1
from t a
where a.PK > :b1
order by a.PK';
$end
$end
begin
$if $$Approach = 1 $then
open Cur;
$elsif $$Approach = 2 $then
open Cur for
select a.PK, a.v1
from t a
where a.PK > b.Some_Value
order by a.PK;
$elsif $$Approach = 3 $then
open Cur for Stmt using Some_Value;

$end
...

-- Code_33 Many_Row_Select.sql
declare
Stmt constant varchar2(200) := '
select a.PK, a.v1
from t a
where a.PK > :b1
order by a.PK';
Target_Varray_Too_Small ...
begin
execute immediate Stmt
bulk collect into Results
using Some_Value;
exception when Target_Varray_Too_Small then
Raise_Application_Error(-20000,
'Fatal: Business Rule 12345 violated.');
end;

-- Code_34 Entire_Bulk_Fetch.sql
-- Cur is already open here.
-- The fetch syntax is the same for
-- an explicit cursor and a cursor variable.
fetch Cur bulk collect into Results;
close Cur;

Code_40 shows an example using embedded SQL.
Code_41 shows an example using native dynamic SQL.
Notice the symmetry between Code_40 and Code_32 and between Code_41 and
Code_33; each example uses a cursor-less PL/SQL construct. And, as with
Code_34 and again just for the sake of comparison, Code_42 shows the
entire bulk fetch flavor that uses an identified cursor, Cur.
Code_43 shows the three ways to establish the identified cursor Cur so that Code_42
is viable. It is very similar to that shown in Code_31 for the multirow case.
As with the multirow case, the total code volume required for the Code_42
approach is noticeably greater than Code_40 or Code_41, to which these versions
are functionally equivalent. The corresponding arguments apply77.
Notice that the very nature of a scenario where the binding requirement is not
known until run time determines that the query is likely to return more than one
row. So the DBMS_Sql API is never appropriate for the case where the
-- Code_40 Single_Row_Select.sql
select a.PK, a.v1
into b.The_Result
from t a
where a.PK = Some_Value;
-- Code_41 Single_Row_Select.sql
declare
Stmt constant varchar2(200) := '
select a.PK, a.v1
from t a
where a.PK = :b1';
begin
execute immediate Stmt
into The_Result
using Some_Value;

-- Code_42 Single_Row_Select.sql
-- Cur is already open here.
-- The fetch syntax is the same for
-- an explicit cursor and a cursor variable.
fetch Cur into The_Result;
close Cur;

-- Code_43 Single_Row_Select.sql
$if $$Approach = 1 $then
cursor Cur is
select a.PK, a.v1
from t a
where a.PK = b.Some_Value;
$elsif $$Approach = 2 or $$Approach = 3 $then
Cur Sys_Refcursor;
$if $$Approach = 3 $then
Stmt constant varchar2(200) := '
select a.PK, a.v1
from t a
where a.PK = :b1';
$end
$end
begin
$if $$Approach = 1 $then
open Cur;
$elsif $$Approach = 2 $then
open Cur for
select a.PK, a.v1
from t a
where a.PK = b.Some_Value;
$elsif $$Approach = 3 $then
open Cur for Stmt using Some_Value;

$end

Principle_14
When exactly one row is to be selected, fetch the row in a single step. Use the
cursor-less PL/SQL constructs select... into when embedded SQL is possible,
and execute immediate... into when dynamic SQL is needed. Take advantage of
the regrettable No_Data_Found exception and the unexpected Too_Many_Rows
exception.

Principle_16
Restrict the object types that Some_App_API’s private synonyms exposes to
just PL/SQL units. Hide all the tables in other schemas, and do not grant any
privileges on these to Some_App_API78.

update t a
set a.n1 = case n1_Specified
when 0 then a.n1
else Update_T_Row.n1
end,
a.v1 = case v1_Specified
when 0 then a.v1
else Update_T_Row.v1
end
where a.PK = Update_T_Row.PK;

Beginners might write code like Code_60

-- Code_60 Forall.sql
for ... loop
...
PK := ...
n1 := ...
...
update t a
set a.n1 = b.n1, ...
where a.PK = b.PK;
end loop;

-- Code_61 Forall.sql
for ... loop]
...
PKs(j) := ...
n1s(j) := ...
...
end loop;
forall j in 1..PKs.Count() loop
update t a
set a.n1 = b.n1s(j), ...
where a.PK = b.PKs(j);

-- Code_71 Batched_Bulk_Fetch_With_Forall_Update.sql
   cursor Cur is
     select a.v1 from t a for update;
   v1 t.v1%type;
 begin
   open Cur;
   loop
     fetch Cur into v1;
     exit when Cur%NotFound;
     v1 := f(v1);
     update t a
     set a.v1 = v1
     where current of Cur;
   end loop;
   close Cur;