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 [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> -----Original Message----- MaryAnn Atkinson Sent: Wednesday, July 23, 2003 4:15 PM To: Multiple recipients of list 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: Stephane Paquette 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).
