| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: comments on EXECUTE IMMEDIATE
The EXECUTE IMMEDIATE is part of the Native Dynamic SQL, which has the SQL
built into the PL/SQL kernel. This is faster than regular Dynamic SQL.
In the example :
Sql_Stmt := 'UPDATE table
SET col1 = :parm1
WHERE col2 = :parm2';
EXECURE IMMEDIATE Sql_Stmt USING parm1, parm2;
In the above example the Native Dynamic SQL (NDS) is using bind variables
(you remember what those are right?) which allows for easier reuse of the
SQL statement.
With the bind variables the SQL statements will not have to be reparsed
since only the value of the bind variables change. This offers a faster
execution of the query.
So , wheneve possible use NDS for faster execution (most of the time) and
less work in the processing part (reparsing, etc.).
RWB
Reginald W. Bailey
IBM Global Services - ETS SW GDSD - Database Management
Your Friendly Neighborhood DBA
713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager)
reginald.w.bailey_at_jpmorgan.com
baileyre_at_us.ibm.com
maryann_30_at_yah
oo.com To: ORACLE-L_at_fatcity.com
Sent by: cc:
ml-errors_at_fatc Subject: RE: comments on EXECUTE IMMEDIATE
ity.com
07/23/2003
03:14 PM
Please respond
to ORACLE-L
> declare
> v_count number;
> begin
> execute immediate 'select count(*) from dba_objects'
> into v_count;
> dbms_output.put_line ('Count:'||v_count);
> end;
> Count:3681
I wouldnt use an execute immediate statement on a case like that. I would just leave it as:
SELECT COUNT(*)
FROM DBA_OBJECTS
INTO v_Count;
I have a feeling Oracle wanted to provide different kind of functionality with the execute immediate.
I saw somewhere else a case like the following:
Sql_Stmt := 'UPDATE table
SET col1 = :parm1
WHERE col2 = :parm2';
EXECURE IMMEDIATE Sql_Stmt USING parm1, parm2;
which again I dont see any advantage. I would have just coded the UPDATE statement without any indirection. Both above examples give me indirection, thats all, which I dont really think I gain anything by incorporating, actually I feel I am losing...
thx
maa
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: maryann_30_at_yahoo.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You mayReceived on Wed Jul 23 2003 - 17:36:14 CDT
 |
 |