There were a similar question in the otn forums today. Ok, when I have to run a script in production, the operators complain about errors like ORA-00942 table or view does not exist. Of course I can provide some documentation to explain what can be ignored, but then they then tend to ignore all ORA errors.
A script to create table t will drop table t if existant. There is no CREATE OR REPLACE TABLE command. So I will simply check the dictionary and drop only if existant.
exec for f in (select 1 from user_tables where
table_name='T') loop execute immediate
'drop table t cascade constraints'; end loop
create table t(x number);
For context, directory, function, indextype (9iR1), java, library, operator, outline, package, procedure, synonym (9iR2), trigger, type, view, it is possible to use the create or replace syntax.
create or replace public synonym x for y;
For indexes (in case they are not dropped with the table)
exec for f in (select 1 from user_indexes where
index_name='I') loop execute immediate
'drop index i'; end loop
create index i on t(x);
For sequences
exec for f in (select 1 from user_sequences where
sequence_name='S') loop execute immediate
'drop sequence s'; end loop
create sequence s;
Let’s try
SQL> exec for f in (select 1 from user_tables where tabl
PL/SQL procedure successfully completed.
SQL> create table t(x number);
Table created.
SQL> exec for f in (select 1 from user_indexes where ind
PL/SQL procedure successfully completed.
SQL> create index i on t(x);
Index created.
SQL> exec for f in (select 1 from user_sequences where s
PL/SQL procedure successfully completed.
SQL> create sequence s;
Sequence created.
End of complaints from production people 
Thanks for the tips. I use something along the lines of
FOR v_objects IN c_objects
LOOP
v_sql := ‘DROP ‘||v_objects.object_type||’ ‘||c_owner||’.’;
v_sql := v_sql||'”‘||v_objects.object_name||'”‘;
BEGIN
EXECUTE IMMEDIATE v_sql;
END LOOP;
Where v_objects is a query against user/all objects. I limit user/all objects by object name/type/owner/etc.
This solution requires that the object owner will have the CREATE/DROP privilege directly granted (not through a role). Unfortunately this is not always the case.
What version of Oracle does this work for?
I tried it for 10gR2 and got the errors:
SQL> select 1 from user_tables where
table_name=’T’ 2 ;
1
———-
1
SQL> exec for f in (select 1 from user_tables where
table_name=’T’) loop execute immediate
‘drop table t cascade constraints’; end loop
create table t(x number);
BEGIN for f in (select 1 from user_tables where; END;
*
ERROR at line 1:
ORA-06550: line 1, column 47:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 1, column 16:
PL/SQL: SQL Statement ignored
ORA-06550: line 1, column 48:
PLS-00103: Encountered the symbol “;” when expecting one of the following:
loop
SQL> SP2-0734: unknown command beginning “table_name…” – rest of line ignored.
SQL> SP2-0734: unknown command beginning “‘drop tabl…” – rest of line ignored.
SQL> create table t(x number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL>
🙁
exec must be in one line, sorry for all those formatting annoyances…
about drop granted to a role, i will have a look
Thanks! Just figured it out and found you already replied.
Alternatively,
exec for f in (select 1 from user_tables where table_name=’T’) –
loop execute immediate ‘drop table t cascade constraints’; –
end loop;
create table t(x number);
You do not need drop privileges to drop objects owned by you.
SQL> create user mdinh identified by m default tablespace sysaux;
User created.
SQL> alter user mdinh quota unlimited on sysaux;
User altered.
SQL> create role apps;
Role created.
SQL> grant create session, create table to apps;
Grant succeeded.
SQL> grant apps to mdinh;
Grant succeeded.
SQL> create table mdinh.t(id int);
Table created.
SQL> connect mdinh/m
Connected.
SQL> exec for f in (select 1 from user_tables where table_name=’T’) –
loop execute immediate ‘drop table t cascade constraints’; –
end loop;
create table t(x number);
> >
PL/SQL procedure successfully completed.
SQL>
Table created.
SQL> desc t;
Name Null? Type
—————————————– ——– —————————-
X NUMBER
SQL>
You must be carful using this contruct.
The Statement CREATE OR REPLACE VIEW …
is atomic – it’s assured that the same object is replaced.
With the additional for construct it is not. Thinking of scripts with a lot of table ddl and the copy/paste technology 😉
A better approach would be something like this :
BEGIN util_ddl.create_or_replace_table(‘CREATE TABLE …’); END;
/
The procedure extracts the tables name and drops it if it’s exist.
The only thing is : it this allowd in production environment?
because the Tables DDL is processed in a black box?
cheers
Karl
Pierre,
this is right, I could imagine something like :
drop.sqldrop table &1;
nodrop.sql— do nothing
SQL> create table t( x number);
Table created.
SQL> col to_drop_or_not_to_drop new_v _drop nopri
SQL> select decode(count(*),0,'nodrop','drop')
2 to_drop_or_not_to_drop from all_tables
3 where owner='AUDBA' and table_name='T';
SQL> @ &_drop AUDBA.T
old 1: drop table &1
new 1: drop table AUDBA.T
Table dropped.
SQL> select decode(count(*),0,'nodrop','drop')
2 to_drop_or_not_to_drop from all_tables
3 where owner='AUDBA' and table_name='T';
SQL> @ &_drop AUDBA.T
SQL>
mdinh is right too, Pierre meant, if DROP ANY TABLE is granted to a role
Hi All,
If the problem was about voiding the errors spitted out at the SQL*PLUS command prompt, another approach would be to write an anonymous PL/SQL block where you deal with any exception adopting the “EXCEPTION WHEN OTHERS THEN NULL;” deprecated code style.
OTN forum comes into help to give an example of the technique.
Read Paul M. thread reply if you want to go this way dropping DB objects.
Link follow..
http://forums.oracle.com/forums/thread.jspa?messageID=1910163&tstart=0
Best regards, GA.
yes, it is a good approach too 😉