| Error message : undefined column [message #10076] |
Wed, 31 December 2003 07:36  |
Canac
Messages: 5
Registered: October 2003
|
Junior Member |
|
|
I'm Using Oracle 8 :
The connect user has DBA provileges
But when executing the following script :
Connect MySchema/MyPsswrd@orcl;
Declare IfExists number (3,0);
BEGIN
select count(*) into IfExists from User_tab_columns where table_name='MYTBL' and column_name = 'MYCOL';
IF IfExists = 0 then
Execute Immediate
'ALTER TABLE MYTBL ADD MYCOL VARCHAR2(30)';
Commit;
END IF;
INSERT INTO MYTBL
(MyKey,MyCol1,MyCol2,MyCol3,MYCOL)
SELECT MyTbl_SEQUENCE.NEXTVAL,p.Col1,p.Col2,p.Col3,p.Col4
from Schema1.PTBL p ;
COMMIT;
END;
I get the following error :
Connected.
MyKey,MyCol1,MyCol2,MyCol3,MyCol)
*
ERROR at line 18:
ORA-06550: line 18, column 34:
PLS-00390: undefined column 'MyCol' in INSERT statement
ORA-06550: line 16, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 47, column 28:
PLS-00414: no column 'MyCol' in table
ORA-06550: line 40, column 1:
PL/SQL: SQL Statement ignored
|
|
|
|
| Re: Error message : undefined column [message #10078 is a reply to message #10076] |
Wed, 31 December 2003 10:39  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You cannot have a static reference in PL/SQL to an object (table, column, whatever) that does not exist at compile time, or in your case - runtime.
You will need to use dynamic SQL for the insert statement as well.
Also, you do not need the commit after the ALTER TABLE.
An alternative to the column count approach is to just attempt the ALTER and trap for an error (which will occur if the column already exists).
begin
execute immediate 'ALTER TABLE MYTBL ADD MYCOL VARCHAR2(30)';
exception
when others then null; -- or trap explicitly for the 'column already exists' error
end; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
