Oracle - Handling Exceptions to Oracle Queries

In Oracle DB, if we already have data in the table and would like to use exceptions to handle errors, please follow below approach:

declare
 e_col_exists exception;
 pragma exception_init(e_col_exists,-1430);
 e_invalid_identifier exception;
 pragma exception_init(e_invalid_identifier,-904);
begin
  begin
    execute immediate 'alter table tbl_questions add value_new varchar2(4000 CHAR) null';
    exception
      when e_col_exists then
        null;
  end;
  begin
    execute immediate 'update tbl_questions set value_new = value';
  end;
  begin
    execute immediate 'alter table tbl_questions drop column value';
    exception
      when e_invalid_identifier then
        null;
  end;
  begin
    execute immediate 'alter table tbl_questions rename column value_new to value';
    exception
      when e_invalid_identifier then
        null;
  end;
end;  
/
------
Explanation:
1. e_col_exists exception:
This exception is likely used to handle situations where a column referenced in your code doesn't actually exist in the table you're trying to access.
The pragma exception_init line associates the exception with the error code -1430. This error code typically corresponds to the ORA-01430 error message, which indicates "column does not exist".
2. e_invalid_identifier exception:
This exception is likely used to handle situations where an identifier (e.g., a variable name, column name, etc.) used in your code is invalid or doesn't follow the naming conventions.
The pragma exception_init line associates the exception with the error code -904. This error code typically corresponds to the ORA-00904 error message, which indicates "invalid identifier".
By defining these custom exceptions, you can make your code more robust and easier to maintain. When either of these exceptions is raised, your code can handle the error gracefully instead of crashing or producing unexpected results.
Here are some additional points to note:
You can define custom exceptions to handle any specific error conditions you want to anticipate in your code.
It's generally considered good practice to define custom exceptions for situations that are specific to your application logic and not already covered by standard Oracle error codes.
You can use exception handlers to trap these custom exceptions and take appropriate actions when they occur.
I hope this explanation helps! Feel free to ask if you have any other questions.