Re: to avoid errors while dropping not existing objects

fireball wrote:
> Uzytkownik <frank.van.bortel_at_gmail.com> napisal w wiadomosci
>
> > Ignore any messages.
>
> I need to test it, anyway.
> (at the end, I don't like error messages like that in my script logs)

It's a valid reason. I've seen it done and the intention was to avoid false errors during deployment; making it easier to spot the real ones. If that's your objective, you may want to try the following:

1. create a script drop_table.sql rem rem Usage: @drop_table <owner> <table_name> rem Parameters are case-sensitive rem

declare
  TABLE_NOT_EXISTS exception;
  pragma EXCEPTION_INIT( TABLE_NOT_EXISTS, -942 ); begin

  execute immediate 'drop table "&1"."&2"';

exception
when TABLE_NOT_EXISTS then
  null;
end;
/
<EOF>

2. test it

SQL> create table "_XXX" (n number) tablespace users;

Table created.

SQL> select * from "_XXX";

no rows selected

SQL> show user
USER is "SYSTEM"
SQL> REM be carefull with the parameters SQL> @drop_table system _xxx

PL/SQL procedure successfully completed.

SQL> REM table still exists
SQL> select * from "_XXX";

no rows selected

SQL> rem drop it for real
SQL> @drop_table SYSTEM _XXX

PL/SQL procedure successfully completed.

SQL> select * from "_XXX";
select * from "_XXX"

              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> REM drop non-existent table, no error message SQL> @drop_table SYSTEM _XXX

PL/SQL procedure successfully completed.

If you don't have any case-sensitive names you can make life easier by using:
execute immediate 'drop table ' || upper('&1') || '.' || upper('&2');

> thank you in advance

Cheers,
Igor Received on Tue Oct 10 2006 - 21:59:36 CDT