1

I am using Oracle 12c and I am not interesting to have an error while droping my table 'CONTINENT' in case it doesn't exist.

I did this 

set echo on
set serveroutput on
alter session set current_schema=WORK_ODI;
set verify off
set pause off
--
WHENEVER SQLERROR CONTINUE  

drop table  CONTINENT;

COMMIT;

EXIT;

but it coudn't skipe the error when the table doesn't exist, how can I fix this problem please

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Abderrahim
  • 651
  • 2
  • 11
  • 25
  • 2
    Unrelated, but: you don't need a commit after DDL in Oracle. –  Feb 01 '16 at 11:06
  • I don't understand you well you mean I have to delete commit, if yes I did it but the error still here – Abderrahim Feb 01 '16 at 11:13
  • You get an error but it doesn't stop the script from continuing so what's your issue? the fact it outputs it at all? http://pastebin.com/YaK1Bv2j This looks like the solution you want: http://stackoverflow.com/questions/3851246/suppress-ora-00942-errors-in-ddl-create-scripts – bob dylan Feb 01 '16 at 11:14

1 Answers1

8

It is a bad idea to DROP and CREATE tables on the fly. Anyway, if you really want to do it, then you need to (ab)use EXECUTE IMMEDIATE to do this in PL/SQL.

For example, you could write an anonymous block:

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE table_name';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

On a side note, DROP is a DDL statement which will implicitly commit, so there's no need to explicitly mention COMMIT.

bob dylan
  • 1,458
  • 1
  • 14
  • 32
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • To reduce the script length (if you are dropping several tables) you could put it in a procedure, – Klas Lindbäck Feb 01 '16 at 11:14
  • "so need to to explicitly mention COMMIT" or rather there is no need – Kamil Feb 01 '16 at 12:14
  • @Kamil Thanks, it was a typo. – Lalit Kumar B Feb 01 '16 at 12:39
  • I don't think it is a problem to skip the drop of a table if there is no table. But I think the problem with your script is one does not see in the log file if a table was dropped or if there was no table. Sometime it may useful you that you can extract this information from your log files. – miracle173 Feb 01 '16 at 13:02
  • @miracle173 And that is what OP's requirement is. Well, it does help in test/dev environments where you drop and recreate objects every now and then. Also, at times you would need to put the DROP before CREATE to make the script re-executable. Then, the first time you know that the table doesn't exist, hence you deliberately want it to be skipped. – Lalit Kumar B Feb 01 '16 at 13:53
  • No, that is not his requirement. The requirement is not to execute a DROP statement if no table exists. That does not mean not to see if a DROP statement is executed. – miracle173 Feb 02 '16 at 02:11
  • @miracle173 Not sure where you read that requirement. I can't see OP saying that anywhere. Anyway, you could always check whether a table exists or not in *_tables view. – Lalit Kumar B Feb 02 '16 at 06:18