How to avoid ORA errors when dropping inexistant objects

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 :mrgreen:

11 thoughts on “How to avoid ORA errors when dropping inexistant objects

  1. neil

    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.

  2. Pierre Forstmann

    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.

  3. mdinh

    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>

  4. mdinh

    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);

  5. mdinh

    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>

  6. Karl Reitschuster

    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

  7. Laurent Schneider Post author

    Pierre,
    this is right, I could imagine something like :

    drop.sql
    drop 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>

  8. Giorgio Arata

    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.

Comments are closed.