CODEWITHSUNDEEP
sqloracle-databaseplsqlexception
Sapo121
Sapo121

Reputation: 71

Handling exceptions oracle creating table

I was wondering if there is someway i can handle a exception if i get an error while creating a table during a procedure.

IF testes=0 then                                                                                
stmt:= 'create table ' || prefix || SII_BCK_TAB_ID_SEQ.nextval || ' AS SELECT * FROM '|| n_tab || ' WHERE 1=0';
EXECUTE IMMEDIATE stmt;

can i create a exception after executing the statement? what is the best process to handle errors while creating a table? or is it the same as handling dml statements?

can i insert something like savepoints? thank you

Upvotes: 0

Views: 1995

Answers (1)

Boneist
Boneist

Reputation: 23578

If I were you, I'd create a separate procedure to handle the table creation and then have an exception clause. This makes it modular code, then; easy to unit test, etc.

e.g.: the procedure would look something like:

PROCEDURE create_table (in_new_table_name in varchar2,
                        in_old_table_name in varchar2)
is
  E_TAB_EXISTS EXCEPTION;
  PRAGMA EXCEPTION_INIT(E_TAB_EXISTS,-955);
BEGIN
  execute immediate 'create table ' || in_new_table_name || ' AS SELECT * FROM '|| in_old_table_name || ' WHERE 1=0';
EXCEPTION
  WHEN E_TAB_EXISTS THEN
    NULL;
END create_table;

and you would call it like:

If testes = 0 then
  create_table(in_new_table_name => prefix || sii_bck_tab_id_seq.nextval,
               in_old_table_name => n_tab);
  ...
end if;

Ideally, you're creating your code in a package, and can simply have the new procedure as a separate procedure in the package.

Upvotes: 1

Related Questions