Execute Immediate to Add a Column and Insert Data in this New Column

Question

I have to create a plsql block which will add a new column on a table ( with dynamic Sql) and then it will insert some data in that new added column.

When I execute my block, I got a message that says that my column is not defined, even if it has been added before the insert statement.

Do you have any idea of what could be causing this problem?

It's like my insert statement doesn't know that the new column has been added.

Answer 1

If you add new column and update/insert data in the new column using dynamic SQL then it will work. Example -

begin
  execute immediate 'ALTER TABLE table_name ADD column_name column-definition'; 
  execute immediate 'update table_name set column_name =''sample-data''';
  COMMIT ;
end;

But, if you add new column using dynamic SQL and use static SQL to update/insert then it will throw error message.
Reason - The static SQL is validated before its execution.
Example -

begin
  execute immediate 'ALTER TABLE table_name ADD column_name column-definition'; 
  update table_name set column_name ='sample-data';
  COMMIT ;
end;