Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Is EXECUTE IMMEDIATE a good choise for DDL/SCL statements?
NT 4.0 / Oracle 8.1.5
Let's assume I'd like to "create or replace" a tablespace. To do so I use SQL*Plus running a script.
Normally it would read:
DROP TABLESPACE tbs_name;
CREATE TABLESPACE tbs_name .....
:
I've choosen another way:
:
BEGIN
<some overhead that forced me to use this way :-)) > IF <allready exists> THEN EXECUTE IMMEDIATE 'DROP TABLESPACE ' || <tbs_name>; END IF; EXECUTE IMMEDIATE 'CREATE TABLESPACE ' || <tbs_name> ....;
I'd like to know whether my way might cause trouble ( as one college with 7.x background suggested - he couldn't be precise anyway :-{ ).
The only points I could imagine to use the first way are:
* PLSQL packages have to be installed (so you cannot use it while
installing databases)
* from Oracle documentation
Caution: You can execute a dynamic SQL statement repeatedly using new
values for the bind arguments. However, you incur some overhead because
EXECUTE IMMEDIATE re-prepares the dynamic string before every execution.
Thanks
Stefan
schindler_at_self.de Received on Tue Feb 08 2000 - 08:31:59 CST