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)
[EMAIL PROTECTED]
[EMAIL PROTECTED]
============================================================================================================
[EMAIL PROTECTED]
oo.com To: [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: comments on EXECUTE
IMMEDIATE
ity.com
07/23/2003
03:14 PM
Please respond
to ORACLE-L
--- Stephane Paquette <[EMAIL PROTECTED]> wrote:
> execute immediate IS dynamic sql, it is just a new simpler synthax.
> 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
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MaryAnn Atkinson
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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 may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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 may
also send the HELP command for other information (like subscribing).
