Execute immediate does not work with alter table

[Sonali]

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

[balajiyes]

can u paste ur proc again...

[balajiyes]

Try this
Hope this helps...
create table test(a number, b number);

-- TO ADD A NEW COLUMN. IF THE INPUT COLUMN_NAME DOESN'T EXIST.

CREATE OR REPLACE PROCEDURE ALTER_TABLE_ADD (
P_TABLE_NAME VARCHAR2,
P_INPUT_COL_NAME VARCHAR2,
P_COL_DATA_TYPE VARCHAR2)
AS
COL_EXIST_CNT NUMBER;
BEGIN
SELECT COUNT(COLUMN_NAME) INTO COL_EXIST_CNT FROM ALL_TAB_COLUMNS
WHERE OWNER = 'JEGAN' AND TABLE_NAME = P_TABLE_NAME AND COLUMN_NAME = P_INPUT_COL_NAME;
IF COL_EXIST_CNT = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ' || P_TABLE_NAME || ' ADD ' || P_INPUT_COL_NAME || ' ' || P_COL_DATA_TYPE;
END IF;
END;

EXEC ALTER_TABLE_ADD ('TEST', 'COL1', 'NUMBER(9)');

-- TO MODIFY A COLUMN. IF THE INPUT COLUMN'S DATATYPE DOESN'T MATCH WITH THE EXISTING ONE.

CREATE OR REPLACE PROCEDURE ALTER_TABLE_MODIFY (
P_TABLE_NAME VARCHAR2,
P_INPUT_COL_NAME VARCHAR2,
P_COL_DATA_TYPE VARCHAR2)
AS
COL_EXIST_CNT NUMBER;
BEGIN
SELECT COUNT(COLUMN_NAME) INTO COL_EXIST_CNT FROM ALL_TAB_COLUMNS
WHERE OWNER = 'JEGAN' AND TABLE_NAME = P_TABLE_NAME AND COLUMN_NAME = P_INPUT_COL_NAME AND DATA_TYPE != P_COL_DATA_TYPE;
IF COL_EXIST_CNT = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ' || P_TABLE_NAME || ' MODIFY ' || P_INPUT_COL_NAME || ' ' || P_COL_DATA_TYPE;
END IF;
END;

EXEC ALTER_TABLE_MODIFY ('TEST', 'COL1', 'CHAR(2)');

CHECK THIS AND REVERT...

Cheers!

[Sonali]

Thanks, I will try that.. but the thing is my pl/sql black which creates the column with execute immediate seems to work, because i can see that column when i do desc. But when I use that column in standard stored procedure it does not find it...

This is what I am doing -

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;
/



But I will still give it a try.. doesn't hurt.

[Sonali]

Yeah, your script works, mine doesn't WHY ???
CREATE OR REPLACE PROCEDURE ALTER_TABLE_ADD (
P_TABLE_NAME VARCHAR2,
P_INPUT_COL_NAME VARCHAR2,
P_COL_DATA_TYPE VARCHAR2)
AS
COL_EXIST_CNT NUMBER;
BEGIN
SELECT COUNT(COLUMN_NAME) INTO COL_EXIST_CNT FROM ALL_TAB_COLUMNS
WHERE OWNER = 'JEGAN' AND TABLE_NAME = P_TABLE_NAME AND COLUMN_NAME = P_INPUT_COL_NAME;
IF COL_EXIST_CNT = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ' || P_TABLE_NAME || ' ADD ' || P_INPUT_COL_NAME || ' ' || P_COL_DATA_TYPE;
END IF;
END;

EXEC ALTER_TABLE_ADD ('TEST', 'COL1', 'NUMBER(9)');

Mine --
-----------

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;
/

[Sonali]

No yours does not work either..

EXECUTE ALTER_TABLE_ADD ('ETTIMELINE', 'TL_INVC_STS', 'NUMBER DEFAULT 20');


Its the DEFAULT in the execute immediate statement that makes it fail !! In yours and mine too...it will not give you error when you add the column using above statement, it will fail when you use it in other procedure for DML.

How do I get it to work ?

When I create this procedure which uses that column that I just added...

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;
/
I get this error

MGR-00072: Warning: PROCEDURE PR_ADDINVOICE created with compilation errors.
SQLWKS>
SQLWKS> show errors
Errors for PROCEDURE PR_ADDINVOICE:
LINE/COL ERROR
--------------------------------------------------------------------------------
731/6 PLS-00417: unable to resolve "TL_INVC_STS" as a column
730/2 PL/SQL: SQL Statement ignored




Thanks
Sonali

[balajiyes]

I works for me...

12:50:33 SQL> EXEC ALTER_TABLE_ADD ('TEST', 'COL1', 'NUMBER(9) default 20');

PL/SQL procedure successfully completed.

12:50:46 SQL> EXEC ALTER_TABLE_ADD ('TEST', 'COL3', 'NUMBER(10) default 20');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.40

12:52:42 SQL>EXEC ALTER_TABLE_ADD ('TEST', 'COL4', 'NUMBER default 20');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.40
12:54:25 SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
B NUMBER
COL1 NUMBER(9)
COL3 NUMBER(10)
COL4 NUMBER

can u send me ur pl/sql block or ur procedure to my mail.
I can trace the issue...

Cheers!

[TomazZ]

what does running this update standalone do?

[Sonali]

The update statement on it own without the procedure works 8.1.7.4 worksheet and SQL*plus .. also this thing works well in Oracle 9.2. Is this a bug in 8.1.7.4 then ?

Is there any other way I can do this ? I want to add the column to the existing table after I check if the column already exists or not ?


Thanks
Sonali

[abhaysk]

check permissions