Execute immediate does not work with alter table
I want to add the column to the existing table.. I want to check if the column already exists in that table before doing alter table add column.. to avoid any errors during the upgrade. So I wrote a small pl/sql block to do this...We are using Oracle 8.1.7.4 for this test.
Declare n int;
begin
select COUNT(*) into n from USER_TAB_COLUMNS where TABLE_NAME=upper('ETTimeLine') AND COLUMN_NAME = UPPER('TL_Invc_Sts');
If n=0 then
execute immediate 'Alter table ETTimeLine add (TL_Invc_Sts number DEFAULT 20)';
End if;
End;
/
The strange thing is when I run this, it works and I can see this column in the table when I do desc ETTimeLine.
I do not have single quotes around this column when I create it, you can see that in the script above.
But when I use this column in the stored procedure in update statement I get following error when creating the stored procedure (compilation error) -
pls-00417 unable to resolve "'TL_Invc_Sts" as a column
CREATE OR REPLACE PROCEDURE pr_addInvoice
(---inpit parameters--- )
IS
--variables--
Begin
if inYES = 20 then
-- statements--
/*update the Invoice Status in the ETTimeLine Table*/
update ETTimeLine
set TL_Invc_Sts = 10
WHERE TL_ID in
(select Mat_TL_ID
FROM mwebmatrix
where Mat_ID in (select distinct InvItem_Mat_ID
from mwebInvoiceItem
where InvItem_Inv_ID = inInv_ID and InvItem_Type=10));
End if;
-- statements--
Commit;
End pr_addInvoice;
/
But when I run this update in SQL worksheet by itself without the procedure, it works.
So then I dropped this column and added it again using this script
Alter table ETTimeLine add (TL_Invc_Sts number DEFAULT 20);
Without using execute immediate this time and the procedure compiled !!!
Why, it does not work with execute immediate and only in the procedure use, I am stumped !
Thanks for help solving this in advance,
Sonali