sql exception handling

Question

CREATE OR REPLACE PROCEDURE p_createLocaltable
IS
  table_already_exist EXCEPTION;
  PRAGMA  EXCEPTION_INIT (table_already_exist, -00955);
BEGIN
  create table local_table as
  select * from supplied_table 
  where rownum < 1;
EXCEPTION
  when table_already_exist then
    DBMS_OUTPUT.put_line('Table already exists , does not need to recreate it');
END;

can anyone see any problem of the above code?

Answer 1

You cannot do DDL in a PL/SQL block like that. You'll need to use execute immediate.

You would need to do it like this

CREATE OR REPLACE PROCEDURE p_createLocaltable
IS
  table_already_exist EXCEPTION;
  PRAGMA  EXCEPTION_INIT (table_already_exist, -00955);
BEGIN
  execute immediate 'create table local_objects as select * from all_objects where 1=0';
EXCEPTION
  when table_already_exist then
    DBMS_OUTPUT.put_line('Table already exists , does not need to recreate it');
END;

Check the orafaq page on this