| Problem with Execute Immediate and Alter Table in PL/SQL [message #191642] |
Thu, 07 September 2006 07:31  |
amuni
Messages: 2
Registered: September 2006
|
Junior Member |
|
|
Hello,
I have the following PL/SQL block:
DECLARE
mytemp number(10,0);
BEGIN
execute immediate 'ALTER TABLE T_FM_AUTOLOAD_THEME ADD ( TEMP NUMBER(10,0) )';
select TEMP into mytemp from t_fm_autoload_theme;
execute immediate 'ALTER TABLE T_FM_AUTOLOAD_THEME DROP COLUMN TEMP';
END;
/
The output is :
Error starting at line 1 in command:
DECLARE
mytemp number(10,0);
BEGIN
execute immediate 'ALTER TABLE T_FM_AUTOLOAD_THEME ADD ( TEMP NUMBER(10,0) )';
select TEMP into mytemp from t_fm_autoload_theme;
execute immediate 'ALTER TABLE T_FM_AUTOLOAD_THEME DROP COLUMN TEMP';
END;
Error report:
ORA-06550: line 6, column 9:
PL/SQL: ORA-00904: "TEMP": invalid identifier
ORA-06550: line 6, column 2:
PL/SQL: SQL Statement ignored
The Execute immediate for the Alter table works fine if i move it to a separate PL/SQL file.
The problem is Selecting the column added in the same PL/SQL script.
Any idea? How can i get around this?
Thanks
|
|
|
|
| Re: Problem with Execute Immediate and Alter Table in PL/SQL [message #191648 is a reply to message #191642] |
Thu, 07 September 2006 07:59  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Don't add and drop the column every time, just leave the column in there?
Can't be space considerations, since the table has only one row anyway. ( If it had more rows the "select TEMP into mytemp from t_fm_autoload_theme;" wouldn't work ).
I have tested it with
BEGIN
execute immediate 'ALTER TABLE test_table ADD ( TEMP NUMBER(10,0) )';
execute immediate 'ALTER TABLE test_table DROP COLUMN TEMP';
END;
On my database (Version 9.2.0.6), and that part seems to work ( at least syntax-wise, though I don't see any reason to do it. )
|
|
|
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
