Source URL : http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=62592.1
Dynamic SQL in Oracle8i is provided via two different methods. The first is the introduction of embedded dynamic sql with the "EXECUTE IMMEDIATE" statement. The second method, for queries only, is an extension of the functionality of ref cursors so that a statement may be passed at runtime instead of being given declaratively.
Method 1: EXECUTE IMMEDIATE
Syntax (pre 8.1.6):
EXECUTE IMMEDIATE dynamic_sql_string
[INTO {define_var1 [, define_var2] ... | plsql_record}]
[USING [IN | OUT | IN OUT] bind_arg1 [,
[IN | OUT | IN OUT] bind_arg2] ...];
From 8.1.6 the syntax has been extended to include a "returning into" clause:
EXECUTE IMMEDIATE dynamic_sql_string
[INTO {define_var1 [, define_var2] ... | plsql_record}]
[USING [IN | OUT | IN OUT] bind_arg1 [,
[IN | OUT | IN OUT] bind_arg2] ...]
[{RETURNING | RETURN} INTO bind_arg3 [, bind_arg4] ...];
where "dynamic_sql_string" may be any of non-query DML, single row query, anonymous PL/SQL block, call statement, transaction control statement, DDL or session control command.
An EXECUTE IMMEDIATE statement effectively:
(i) prepares the given string
(ii) binds any arguments passed in the USING clause
(iii) executes the statement
(iv) if the statement is a query, fetches the single row into the define variables
or PL/SQL record given in the INTO clause
(v) deallocates and clears up as necessary.
The embedded dynamic SQL method is very similar to that used with dynamic methods 1 and 2 of the precompilers, the main difference being the addition of the INTO clause. This is provided to allow EXECUTE IMMEDIATE to be used to execute queries that return only a single row of data. If more than one row matches the search criteria a "too_many_rows" exception will be raised. Similarly if no rows are returned the result is a "no_data_found" exception. In these cases the ref cursor method should be used instead.
If a function or procedure executes dynamic SQL via the DBMS_SQL package then no purity levels may be asserted since there is no way to tell at compile time what the statement will do. Thus DBMS_SQL.EXECUTE has no pragma restrict_references clause and so neither may anything that calls it. Because there are no other packages involved when using EXECUTE IMMEDIATE, the compiler simply checks if the statement contains an INTO clause. If so this indicates a query and so an RNDS (Reads No Database State) purity level may not be asserted. All other purity checking is left until runtime when the dynamic sql statement is known (see example (xii) below).
Using Pragma RESTRICT_REFERENCES
A function called from SQL statements must obey certain rules meant to control side effects. (See "Controlling Sides Effects".) To check for violations of the rules, you can use the pragma RESTRICT_REFERENCES. The pragma asserts that a function does not read and/or write database tables and/or package variables. (For more information, See Oracle8i Application Developer's Guide - Fundamentals.)
However, if the function body contains a dynamic INSERT, UPDATE, or DELETE statement, the function always violates the rules "write no database state" (WNDS) and "read no database state" (RNDS). That is because dynamic SQL statements are checked at run time, not at compile time. In an EXECUTE IMMEDIATE statement, only the INTO clause can be checked at compile time for violations of RNDS.
EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING NULL;However, this statement fails with a bad expression error because the literal NULL is not allowed in the USING clause. To work around this restriction, simply replace the keyword NULL with an uninitialized variable, as follows:
DECLARE a_null CHAR(1); -- set to NULL automatically at run time BEGIN EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING a_null; END;
Advantages of Dynamic SQL over DBMS_SQL
---------------------------------------
(i) Easier to use.
(ii) Faster.
(iii) Supports object types and collections.
(iv) Can assert purity levels that will be checked at runtime.
(v) Can return results of a query into PL/SQL record types.
(vi) Can open a dynamic ref cursor in PL/SQL and then fetch from it
using OCI, the precompilers etc.
Disadvantages
-------------
(i) Dynamic statements are limited to 32K.
(ii) There is no bulk bind capability.
(iii) There is no method of performing piecewise fetches of longs.
(iv) There is no DESCRIBE capability for describing select list
items.
(v) There is no equivalent of such functions as
DBMS_SQL.LAST_ERROR_POSITION, DBMS_SQL.LAST_SQL_FUNCTION_CODE
etc.
(vi) It is not possible to change the values of bind variables
without reissuing the execute or open (and so reparsing) the
statement. Note that although a reparse is performed, if the
SQL is already in the shared pool then this will simply be a
"soft" parse with minimum overhead.
Other Restrictions
------------------
(i) The new bulk bind syntax is not permissable with either the
EXECUTE IMMEDIATE or ref cursor methods.
(ii) Pre 8.1.5 the new bulk fetch operations may only be used with ref
cursors. With 8.1.5+ neither method may be used with bulk collection.
(iii) It is not possible to bind variables to place holders by name.
(iv) PL/SQL datatypes cannot by used as bind or define variables,
with the exception of PL/SQL record types which may be used in
INTO clauses.
(v) Where CURRENT OF may not be used.
Description : Execute Immediate multi binds
-- execute immediate will NOT allow me to pass a TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER as a bind var list...
-- I have figured out a way to do what I need...
declare
lvs_statement varchar2(200);
lvs_block varchar2(200);
lvs_value varchar2(20);
lvn_row_count number;
begin
lvs_value := '-1,-1';
lvs_statement := 'delete from item_dim where item_key = :b1 and vendor_key = :b2';
lvs_block := 'begin EXECUTE IMMEDIATE :str USING ' || lvs_value ||'; :row_count:=sql%rowcount; end;';
EXECUTE IMMEDIATE lvs_block USING lvs_statement, out lvn_row_count;
dbms_output.put_line('Deleted '||to_char(lvn_row_count));
end;
DECLARE
ls_newline VARCHAR2(50) := CHR(10);
ln_rc NUMBER := Null;
ln_cursor_handle NUMBER := Null;
ls_sql VARCHAR2(2000) := Null;
ln_contract_id CONTRACT.CONTRACT_ID%TYPE := 7809383;
ln_product_version_id CONTRACT.PRODUCT_VERSION_ID%TYPE := Null;
ls_sales_channel CONTRACT.SALES_CHANNEL%TYPE := Null;
ld_last_modified CONTRACT.LAST_MODIFIED%TYPE := Null;
BEGIN
ls_sql :=
'
SELECT contract.product_version_id,
contract.sales_channel,
contract.last_modified
FROM contract
WHERE ( contract_id = :n_contract_id )
';
-- Get a cursor handle
ln_cursor_handle := DBMS_SQL.OPEN_CURSOR;
-- Parse the statement
DBMS_SQL.PARSE(ln_cursor_handle,ls_sql,DBMS_SQL.NATIVE);
-- Bind n_contract_id
DBMS_SQL.BIND_VARIABLE(ln_cursor_handle,'n_contract_id',ln_contract_id);
-- Define the ProductVersionID column
DBMS_SQL.DEFINE_COLUMN(ln_cursor_handle,1,ln_product_version_id);
-- Define the SalesChannel column
DBMS_SQL.DEFINE_COLUMN(ln_cursor_handle,2,ls_sales_channel,column_size => 4);
-- Define the LastModified column
DBMS_SQL.DEFINE_COLUMN(ln_cursor_handle,3,ld_last_modified);
-- Execute the statement (ignore the return code)
ln_rc := DBMS_SQL.EXECUTE(ln_cursor_handle);
LOOP
BEGIN
IF (DBMS_SQL.FETCH_ROWS(ln_cursor_handle) = 0) THEN
EXIT;
ELSE
DBMS_SQL.COLUMN_VALUE(ln_cursor_handle,1,ln_product_version_id);
DBMS_SQL.COLUMN_VALUE(ln_cursor_handle,2,ls_sales_channel);
DBMS_SQL.COLUMN_VALUE(ln_cursor_handle,3,ld_last_modified);
END IF;
common_func.display_output(s_calling_application_in => common_func.C_AT_NOT_WEB_APP,
s_output_text_in =>
'ProductVersionID : '||TO_CHAR(ln_product_version_id)||ls_newline||
'SalesChannel : '||ls_sales_channel||ls_newline||
'LastModified : '||ld_last_modified);
EXCEPTION
WHEN OTHERS THEN
common_func.display_output(
s_calling_application_in => common_func.C_AT_NOT_WEB_APP,
s_output_text_in =>
'Problem with Fetch'||ls_newline||
'SQLERRM : '||SQLERRM||ls_newline);
END;
END LOOP;
-- Close the cursor
DBMS_SQL.CLOSE_CURSOR(ln_cursor_handle);
EXCEPTION
WHEN OTHERS THEN
common_func.display_output(s_calling_application_in => common_func.C_AT_NOT_WEB_APP,
s_output_text_in => 'SQLERRM : '||SQLERRM);
-- Close the cursor
IF (DBMS_SQL.IS_OPEN(ln_cursor_handle)) THEN
DBMS_SQL.CLOSE_CURSOR(ln_cursor_handle);
END IF;
END;