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

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 - 15:31:59 CET