Re: Ignore ORA 942 Error

"Neil" <nmdavies1104_at_hotmail.com> wrote in message news:caef6318.0410120207.4ae42d2a_at_posting.google.com...
> I have a unix script that includes a DROP TABLE and CREATE TABLE
> command in SQL Plus.it works fine if the table to drop is there. If
> not the script ends. Can anyone advise how I can ignore the
> "ORA-00942: table or view does not exist" error and continue to the
> next command?

Create a file : drop_table.sql
== drop_table.sql ===
set verify off
DECLARE
   c_table_name varchar2(50) := upper('&1'); BEGIN
   FOR trec IN (select null

                from user_tables
                where table_name = c_table_name)
   LOOP
      execute immediate 'drop table '||c_table_name||'  cascade constraints'

   END LOOP;
END;
/

Call script from your sql:
@drop_table table_name

Or create a procedure:
CREATE OR REPLACE PROCEDURE drop_table (v_table_name VARCHAR2) AS
BEGIN
   FOR trec IN (select null

                from user_tables
                where table_name = upper(v_table_name))
   LOOP
      execute immediate 'drop table '||v_table_name||'  cascade constraints'

   END LOOP;
END;
/

Call script from your sql:
exec drop_table('table_name');

Regards,
Rob Received on Tue Oct 12 2004 - 06:51:10 CDT