Handling exceptions oracle creating table

Question

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

You handle exceptions to execute immediate. It doesn't matter what the underlying statement is. The exception handling code would deal with that in PL/SQL. — Gordon Linoff
– Gordon Linoff, Commented Nov 9, 2018 at 15:37
@GordonLinoff can i still use savepoint normally if the statement fails? — Sapo121
– Sapo121, Commented Nov 9, 2018 at 17:06

Boneist · Accepted Answer · 2018-11-09 15:48:40Z

1

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.

answered Nov 9, 2018 at 15:48

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

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

1 Comment

Sapo121 Over a year ago

thats what im doing, its part of a job im creating, so i could create a new procedure for my create table. thanks

Collectives™ on Stack Overflow

Handling exceptions oracle creating table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related