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?

6/3 PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe — user297285
– user297285, Commented Apr 4, 2010 at 8:14

Matthew Watson · Accepted Answer · 2010-04-04 09:38:53Z

4

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

answered Apr 4, 2010 at 9:38

Matthew Watson

14.3k9 gold badges65 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql exception handling

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related