ORA-00904 INVALID IDENTIFIER oracle-CodePudding

I would like to update a table using a column that may not exists. I am doing this but getting an error, I tried to handle but still getting an exception. Is there a way to go about this? to update a table only if a column exists? I am a beginner in Oracle PL/SQL, any help would be appreciated. Thanks.

declare
  column_not_exists_exception exception;
  pragma exception_init (column_not_exists_exception , -06550);
begin
 update table1 
 set column = case 
    when column_may_not_exists = 0 then 0 
    when column_may_not_exists = 1 then 1
    else 0 
 end;
exception when column_not_exists_exception then
 dbms_output.put_line('error column does not exists');
end;

The error I get is ORA-06550 and ORA-00904 : "column_may_not_exists" : invalid identifier , cause: usually a pl/sql compilation error

CodePudding user response：

That is certainly a strange requirement, but since PL/SQL must be valid at compilation time, you can't reference a non-existent column directly.

Thus you can do the following

SQL> create table t ( x int, y int );

Table created.

SQL> insert into t values (0,0);

1 row created.

SQL>
SQL> set serverout on
SQL> declare
  2    column_not_exists_exception exception;
  3    pragma exception_init (column_not_exists_exception , -904);
  4  begin
  5   execute immediate '
  6     update t
  7     set y = case
  8      when y = 0 then 10
  9      when y = 1 then 20
 10      else 30
 11   end';
 12   dbms_output.put_line('All good');
 13  exception
 14    when column_not_exists_exception then
 15     dbms_output.put_line('error column does not exists');
 16  end;
 17  /
All good

PL/SQL procedure successfully completed.

SQL>
SQL> set serverout on
SQL> declare
  2    column_not_exists_exception exception;
  3    pragma exception_init (column_not_exists_exception , -904);
  4  begin
  5   execute immediate '
  6     update t
  7     set z = case
  8      when z = 0 then 10
  9      when z = 1 then 20
 10      else 30
 11   end';
 12   dbms_output.put_line('All good');
 13  exception
 14    when column_not_exists_exception then
 15     dbms_output.put_line('error column does not exists');
 16  end;
 17  /
error column does not exists

PL/SQL procedure successfully completed.