Non-Predefined Exception Example

One of my friend asked me following question.

“I want to drop two columns of a table. So I wrote a basic drop command.

“alter table t_test drop (col_name1, col_name2)”

However, If this command is called more than once. I do not want an error message. What can I do for this?”

It’s a strange request, but this will learn us how to write a non-predefined  exception to solve this kind of requests. We can handle ORA-XXXXX exception as we want. We name them this numbered exceptions and handle them.  I will write a post about exception types as soon as possible. 

 When we try to drop a column that does not exists on the table. We get following error: ORA–00904: invalid column name. It’s error number “-904”. We will use this number to define the exception.

DROP TABLE t_Test;
CREATE TABLE t_test (v_clm NUMBER, v_clm1 VARCHAR2(10));

DECLARE
  v_sqlcode NUMBER;
  v_sqlerrm VARCHAR2(400);
  e_invalid_clm EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_invalid_clm, -904);
BEGIN
  -- these col_name1, col_name2 are not column of t_test table!! I mean, After first run, the second run will not raise an error.
  EXECUTE IMMEDIATE 'alter table t_test drop (col_name1, col_name2)';
EXCEPTION
  WHEN e_invalid_clm THEN
    NULL;
    dbms_output.put_line('We will nothing!!');
  WHEN OTHERS THEN
    v_sqlcode := SQLCODE;
    v_sqlerrm := SQLERRM;
    dbms_output.put_line(v_sqlerrm);
    RAISE;
END;

 
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
 
SQL> set serveroutput on
SQL>
SQL> DROP TABLE t_Test;
 
Table dropped
SQL> CREATE TABLE t_test (v_clm NUMBER, v_clm1 VARCHAR2(10));
 
Table created
SQL> DECLARE
  2       v_sqlcode NUMBER;
  3       v_sqlerrm VARCHAR2(400);
  4       e_invalid_clm EXCEPTION;
  5       PRAGMA EXCEPTION_INIT (e_invalid_clm, -904);
  6 
  7  BEGIN
  8       — these col_name1, col_name2 are not column of t_test table!!!
  9       EXECUTE IMMEDIATE ‘alter table t_test drop  (col_name1, col_name2)’;
 10 
 11  EXCEPTION
 12       WHEN e_invalid_clm THEN
 13            NULL;
 14            dbms_output.put_line(‘We will nothing!!’);
 15       WHEN OTHERS THEN
 16            v_sqlcode := SQLCODE;
 17            v_sqlerrm := SQLERRM;
 18            dbms_output.put_line(v_sqlerrm);
 19            RAISE;
 20  END;
 
 21  /
 
We will nothing!!
 
PL/SQL procedure successfully completed
 
SQL>

Advertisement