SQL Invalid Identifier when trying to run simple script

Question

I'm trying to run the following Oracle SQL script which should create a new column, TEMPLATE, and populate it with some base values, but I'm getting an error.

DECLARE  v_cnt INT; 

BEGIN
    select count(*) into v_cnt 
    from user_tab_columns 
    where upper(table_name) = upper('myTable') and upper(column_name) = upper('template');

    IF(v_cnt = 0) THEN
        EXECUTE IMMEDIATE 'ALTER TABLE MYTABLE ADD TEMPLATE NVARCHAR2(255)';

        UPDATE MYTABLE 
        SET TEMPLATE = 'BASE VALUE';
    END IF; 
  END; /

The error that I'm getting is

ORA-06550: line 10, column 37:
PL/SQL: ORA-00904: "TEMPLATE": invalid identifier

When trying to run these statements manually, everything seems to be working fine. I believe the issue is related to the EXECUTE IMMEDIATE portion, with which I'm not familiar. After preliminary Googling I can't determine that this statement is asynchronous or anything like that, so in theory there should be no error thrown there.

Kon · Accepted Answer · 2014-06-16 15:43:43Z

2

I've found the solution, shortly after posting the question. It is actually a compilation issue. The Oracle compiler reads the entire script before execution and throws an exception because I'm trying to explicitly update a column that, at the time immediately before the script running, does not exist.

The solution was to put the UPDATE statements in EXECUTE IMMEDIATE blocks.

....
EXECUTE IMMEDIATE 'UPDATE EGPL_USER_ROLE SET TEMPLATE = ''BASE VALUE''';
....

answered Jun 16, 2014 at 15:43

Kon

10.8k6 gold badges46 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Patrick Bacon · Accepted Answer · 2014-06-16 16:02:41Z

0

The column name you are attempting to add, TEMPLATE, is a reserved word. See ORA-00904 and see the reserved word list (http://docs.oracle.com/cd/E10530_01/doc/epm.931/html_esb_techref/maxl/ddl/keywords.htm).

Change the name to 'A_TEMPLATE' and it will execute.

answered Jun 16, 2014 at 16:02

Patrick Bacon

4,7101 gold badge31 silver badges36 bronze badges

3 Comments

Kon Over a year ago

I'm not sure if this is the case. I've made the changes as I noted below and everything is working fine.

Patrick Bacon Over a year ago

Yes, I commented out the line, UPDATE MYTABLE SET TEMPLATE = 'BASE VALUE';, when I made my change. I agree with your solution.

Alex Poole Over a year ago

That list of keywords is for Hyperion, not the RDBMS. TEMPLATE isn't in the 11gR2 list. (Makes a change from someone linking to MySQL docs though *8-).

Collectives™ on Stack Overflow

SQL Invalid Identifier when trying to run simple script

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related