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));
PRAGMA EXCEPTION_INIT (e_invalid_clm, -904);
-- 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)';
WHEN e_invalid_clm THEN
dbms_output.put_line('We will nothing!!');
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
v_sqlerrm := SQLERRM;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL> set serveroutput on
SQL> DROP TABLE t_Test;
SQL> CREATE TABLE t_test (v_clm NUMBER, v_clm1 VARCHAR2(10));
2 v_sqlcode NUMBER;
3 v_sqlerrm VARCHAR2(400);
4 e_invalid_clm EXCEPTION;
5 PRAGMA EXCEPTION_INIT (e_invalid_clm, -904);
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)’;
12 WHEN e_invalid_clm THEN
14 dbms_output.put_line(‘We will nothing!!’);
15 WHEN OTHERS THEN
16 v_sqlcode := SQLCODE;
17 v_sqlerrm := SQLERRM;
We will nothing!!
PL/SQL procedure successfully completed