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