Question: How does native dynamic
SQL work in PL/SQL? Can you show an example script
using native dynamic SQL?
Answer: The native dynamic SQL
statement is often done with the execute immediate
syntax. Also see my notes on
dynamic PL/SQL.
Native dynamic SQL is a simple and an efficient tool,
which was introduced in the Oracle version 9i as a
replacement to the DBMS_SQL API. Even though NATIVE DYNAMIC
SQL were created as a replacement for the DBMS_SQL API, it
lacks to perform some functionality which is effectively
done by the API that is covered in the DBMS_SQL section.
This method of programming is easy and concise to use,
as we need fewer lines of code to construct dynamic SQL in
NATIVE DYNAMIC SQL unlike the API. As this is a native
method, the PL/SQL interpreter has a built in support making
it more efficient than the DBMS_SQL API.
In NATIVE
DYNAMIC SQL, the EXECUTE IMMEDIATE and OPEN FOR statements
helps in parsing and executing the dynamic SQLs. This
chapter actively explains the EXECUTE IMMEDIATE statement
and the OPEN FOR statement is covered in the chapter
The Cursors and Ref-cursors in PL/SQL.
The
prototype for declaring the EXECUTE IMMEDIATE statement is
shown below,
EXECUTE IMMEDIATE <'Dynamic_string'>
[[BULK COLLECT] INTO
]
[USING [IN | OUT | IN OUT] Bind_argument
[, [IN | OUT | IN OUT] Bind_argument] ...]
[RETURNING |
RETURN [BULK COLLECT] INTO Bind_argument [, Bind_argument]
...];
- Dynamic_string can be either an SQL statement
or a PL/SQL block with constructive objects embedded in
them.
- Define_variable holds the result of the
Dynamic_string’s execution. For multi-row result, BULK
COLLECT INTO clause is used. For more information on
this clause, please refer the chapter 6, The PL/SQL
Collections.
- Bind_argument in the USING clause works as the
bind variable for the Dynamic_string with IN, OUT and IN
OUT parameter modes. These bind arguments cannot be used
for passing schema objects like table name and column
name.
- Bind_argument in the RETURNING INTO/ RETURN
INTO clause help in returning any column value(s) from
the Dynamic_string’s execution that are type compatible
to each other. This clause works only for the INSERT,
DELETE and UPDATE operations. For returning multi-row
column values, BULK COLLECT INTO clause is used. For
more information on this clause, please refer the
chapter 6, The PL/SQL Collections.
All the bind variables are to be placed only in the USING
clause and if not mentioned, the parameter mode is IN by
default. For the DML statements with the RETURNING INTO
clause, by default the parameter mode for the bind variable
will be OUT.
For more examples of native dynamic SQL, see my notes
here for
native dynamic SQL examples using execute immediate.
Here is an example of using a FORALL
statement to perform a native dynamic SQL statement:
dynamic_forall.sql
DECLARE
TYPE t_object_id_tab IS TABLE OF
bulk_collect_test.object_id%TYPE;
l_tab
t_object_id_tab;
BEGIN
-- Populate collection use in
forall.
SELECT object_id
BULK COLLECT INTO l_tab
FROM bulk_collect_test
WHERE rownum < 101;
FORALL i IN l_tab.first .. l_tab.last
EXECUTE IMMEDIATE
'UPDATE
bulk_collect_test
SET object_id = object_id
WHERE
object_id = :1'
USING l_tab(i);
END;
/
This script defines a collection,
populates it with some data from the test table and performs
a bulk dynamic update using native dynamic SQL (execute
immediate).
Native Dynamic SQL on long strings
datatypes
String literals in SQL are limited to
4000 characters. Your dynamic SQL is trying to concatenate
the CLOB as part of the SQL statement, turning it into a
string literal. If dynamic SQL were required, you'd need to
use bind variables.
See here for complete
details on
converting a long datatype to a CLOB.
Here is native dynamic SQL for a CLOB datatype:
EXECUTE IMMEDIATE 'INSERT INTO some_table(
clob_column ) VALUES( :1 )' USING p_clob_parameter
Catching errors in native dynamic SQL
Here we execute a DDL statement using native dynamic SQL
in PL/SQL and trap any non-zero SQLCODE from Oracle:
begin
execute immediate
'drop table TABLE1';
exception when others then null;
end;
In a PL/SQL cursor loop you can capture native dynamic
SQL errors like this:
for i in mycir loop
begin
execute immediate mystring (i);
exception
when others then myerror_proc
mymessage, sqlcode, sqlerrm)
end;
end loop;
