Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Execute Immediate and Alter Table in PL/SQL  () 1 Vote
Problem with Execute Immediate and Alter Table in PL/SQL [message #191642] Thu, 07 September 2006 07:31 Go to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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. )

Re: Problem with Execute Immediate and Alter Table in PL/SQL [message #191959 is a reply to message #191648] Sat, 09 September 2006 04:53 Go to previous messageGo to next message
amuni
Messages: 2
Registered: September 2006
Junior Member
Thank you.
Yes - i moved the column additions to a separate script,and that works.

But i dont really understand why you cant Alter a table, add a column and immediately use the same column in subsequent Select statements in the same PL/SQL script.

Dont know if there is some Global/local business in PL/SQL.
But am sorted for now Smile
Re: Problem with Execute Immediate and Alter Table in PL/SQL [message #191968 is a reply to message #191959] Sat, 09 September 2006 07:33 Go to previous message
aorehek
Messages: 52
Registered: August 2006
Member
During parse operation column does not exist in the table, so script fails with semantic error. Oracle does not parse dynamic sql. It is parsed in runtime.
Previous Topic: ORA-12560: TNS:protocol adapter error
Next Topic: CREATE ...?
Goto Forum:
  


Current Time: Fri Dec 09 17:20:44 CST 2016

Total time taken to generate the page: 0.44977 seconds