Oracle Alter Table using Execute Immediate Not behaving as expected

user_2381424 0
Viewed 675

I have PLSQL block like below which add a dummy column , does an update and then drops and renames columns

BEGIN
NULL;
EXECUTE IMMEDIATE 'ALTER TABLE TESTMISMATCH ADD (TEMP_745634_1 VARCHAR2(4000 BYTE))';
UPDATE TESTMISMATCH SET TEMP_745634_1=TO_CHAR(A);
EXECUTE IMMEDIATE 'ALTER TABLE TESTMISMATCH DROP COLUMN A';
EXECUTE IMMEDIATE 'ALTER TABLE TESTMISMATCH RENAME COLUMN TEMP_745634_1 TO A';
End;

The first execute immediate statement is successful but the following UPDATE statement on the newly created column fails with the below error

ORA-06550: line 4, column 25:
PL/SQL: ORA-00904: "TEMP_745634_1": invalid identifier
ORA-06550: line 4, column 1:

But if I run the same without a plsql block ,all the statements are executed successfully, like below.

ALTER TABLE TESTMISMATCH ADD (TEMP_745634_1 VARCHAR2(4000 BYTE));
UPDATE TESTMISMATCH SET TEMP_745634_1=TO_CHAR(A);
ALTER TABLE TESTMISMATCH DROP COLUMN A;
ALTER TABLE TESTMISMATCH RENAME COLUMN TEMP_745634_1 TO A;

My initial suspicion is that with regard to PLSQL context and SQL context switch that happens between First "Execute Immediate" statement and Update statement.

alter-table plsql oracle sql
2 Answers

The issue is that Oracle has to compile the PL/SQL block before it can execute it. When it tries to compile the block, it finds that temp_745634_1 is not a valid column name and throws the error. This happens before Oracle can even try to execute the individual statements including the execute immediate.

If you want to refer to the newly created column in the same PL/SQL block where you created it, you'd need to defer compilation to runtime. In order to do that, you'd need to make the update statement use dynamic SQL. So if you do execute immediate 'update ... the PL/SQL block would work.

Justin_Cave_103970

Looks like changes you've made in the first step are not viewable until the block ended.

This will help you

BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE TESTMISMATCH ADD (TEMP_745634_1 VARCHAR2(4000 BYTE))';  
  execute immediate 'UPDATE TESTMISMATCH SET TEMP_745634_1=TO_CHAR(''A'')';
  EXECUTE IMMEDIATE 'ALTER TABLE TESTMISMATCH DROP COLUMN A';
  EXECUTE IMMEDIATE 'ALTER TABLE TESTMISMATCH RENAME COLUMN TEMP_745634_1 TO A';
End;
user_60336010
Related