CODEWITHSUNDEEP
oracle-databaseplsql
georob67
georob67

Reputation: 13

oracle exception not going to exception block

I need to update some code which uses dynamic sql and potentially could have duplicate column names in the columns of the insert statement.

So I wanted to handle this, ORA-00957: Duplicate Column name. This error does not get handled by the most generic "when others" in the exception block. If I make it test a unique constraint violation it does.

Following some test code:

create table animal (id number , animal_type number, 
  animal_name varchar2(20), constraint id primary key(id));
-----------------------------------------------------
begin
for i in 1.. 100 loop
    insert into animal(id, animal_type, animal_name) 
      values(i,floor(dbms_random.value(1,30)),'animal'||i);
end loop;
end;
-----------------------------------------------------
DECLARE
--  e_duplicate_column exception;
--  pragma exception_init(e_duplicate_column,-957);
BEGIN
    insert into animal(id, animal_name, animal_name) 
      values(1000003, 'animal 1000003', 'animal 1000003');
EXCEPTION
WHEN OTHERS THEN
    dbms_output.put_line(SQLCODE);
    dbms_output.put_line(SQLERRM);
END;

I was trying to get the codes here as the pragma wasn't working(i.e. arriving) either. But that's not happening if it doesn't even get to "when others".

Any insights are appreciated. Cheers, Robbert

PS oracle 12C, tried on sqldeveloper and toad

Upvotes: 1

Views: 265

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17944

Your test code does not use dynamic SQL, which is required to generate an ORA-00957 error. Without dynamic SQL, Oracle will throw the ORA-00957 when it compiles your block, which I think you are misinterpreting as Oracle actually running you block and skipping the exception handler.

Try this instead as a test (make sure you have DBMS output enabled in your client!):

DECLARE
--  e_duplicate_column exception;
--  pragma exception_init(e_duplicate_column,-957);
BEGIN
    execute immediate q'[insert into animal(id, animal_name, animal_name) values(1000003, 'animal 1000003', 'animal 1000003')]';
EXCEPTION
WHEN OTHERS THEN
    dbms_output.put_line(SQLCODE);
    dbms_output.put_line(SQLERRM);
END;

-957
ORA-00957: duplicate column name

Upvotes: 2

Related Questions