Execute immediate does not work with alter table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Execute immediate does not work with alter table

  1. #1
    Join Date
    Jan 2003
    Location
    newton, ma, usa
    Posts
    15

    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

  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    can u paste ur proc again...
    Cheers!
    OraKid.

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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!
    Cheers!
    OraKid.

  4. #4
    Join Date
    Jan 2003
    Location
    newton, ma, usa
    Posts
    15
    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.

  5. #5
    Join Date
    Jan 2003
    Location
    newton, ma, usa
    Posts
    15
    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;
    /

  6. #6
    Join Date
    Jan 2003
    Location
    newton, ma, usa
    Posts
    15
    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

  7. #7
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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!
    Cheers!
    OraKid.

  8. #8
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    what does running this update standalone do?
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  9. #9
    Join Date
    Jan 2003
    Location
    newton, ma, usa
    Posts
    15
    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

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    check permissions
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width