Re: Is EXECUTE IMMEDIATE a good choise for DDL/SCL statements?

Personally I use this sort of technique in preference to anything else. Simple scripts (SQL or shell calling SQL) do
not allow for good error-handling, whereas a PL/SQL approach allows for layers of
traps and retries.

(Not to mention code re-use etc...)

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Stefan Schindler wrote in message <38A028DF.B19C1942_at_self.de>...
>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> ....
>;
> END;
>/
>
>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 - 13:14:15 CST