Re: A Tool Similar to SQL7 EXISTS

>Hi,
>
>Is there a macro or utility which can be used in SQL to test existence
>of a table prior to dropping the table?
>
>There is a tool in Sybase and/or SQL7 which goes like this
>
>IF EXISTS (SELECT table_name from ....)
>DROP TABLE table_name
>
>
>Now, in Oracle you have to do lot more to find out if the table exists
>or not.
>I can use the DROP TABLE table_name and ignore the error message telling
>me that table does not exists.
>
>But, I hate seeing error messages passing by when you have a huge script
>going thru a big setup. The clients sometimes get spooked and start
>asking all kinda scared questions.
>
>So, if you know of an easy way to do this, please, let me know. Much
>appreciated.
>
>Parviz
>
>PS-There is an EXISTS which is to check a table item at a specific row.
>But, this is not going to do the trick.
>
>
You could write a procedure that simply executes the input string as a dynamci sql statement. The advantage is that you can trap the error much better...

...pseudo code...
procedure exec_sql (sql_string varchar ignore_error boolean) is begin
dbms_sql.open_cursor...
dbms_sql.parse(sql_string)...
begin
dbms_sql.execute;
exception
when others
if ignore_error then

   return
else
  raise
end if
end <2nd begin>
end<procedure>

There reason for 2 begins is to trap execution errors, rather than parse errors

The, in your sql script

exec exec_sql('drop mytable', true)
(will continue if it can't drop table)

exec exec_sql('create table...', false)
(will stop if it can't create table)

If you want to make a function specific to dropping a table, you could check for the table, rather than using a more generic procedure.

HTH
Dan Hekimian-Williams Received on Thu Feb 24 2000 - 02:58:46 CET