RE: comments on EXECUTE IMMEDIATE

From: Stephane Paquette <stephane.paquette_at_standardlife.ca>
Date: Wed, 23 Jul 2003 15:31:54 -0400
Message-Id: <25998.339234@fatcity.com>

I just took 5 seconds to write an example. If you want a real life example, write a stored proc to truncate table. All DBA have done this one to allow the developpers to truncate any table in a schema.

You can do it the old dynamic style or use execute immediate which is less hassle.

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187 stephane.paquette_at_standardlife.ca <mailto:stephane.paquette_at_standardlife.ca>

-----Original Message-----
From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of MaryAnn Atkinson
Sent: Wednesday, July 23, 2003 4:15 PM
To: Multiple recipients of list ORACLE-L Subject: RE: comments on EXECUTE IMMEDIATE

Stephane Paquette <stephane.paquette_at_standardlife.ca> 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: 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 may

Received on Wed Jul 23 2003 - 14:31:54 CDT