Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    1

    Unanswered: ORA-01007: variable not in select list (Err in accessing dynamically altered table)

    Hi All, This is Kushal

    I am a new user to this group.

    I am facing a problem.

    Problem :- I have written a stored procedure in which the structure of one table is decided at runtime. Now I am querying that table through a cursor. My problem is The cursor opens everytime properly for one parameter value but it gives error the very first time for other parameter value. Now if I execute it again for the same parameter value it runs without any error

    Do please help. I have given comment at the point where error is coming

    A Part of my prcedure is :---

    PROCEDURE BUILD_TEMP_COMM_SCHEDULE(SERVICE_MST_FK_IN IN NUMBER)
    AS

    /*
    ...

    Some Cursors are defined here
    */
    CURSOR CUR_NPC_SCHEDULE IS SELECT * FROM TEMP_COMMERCIAL_SCHEDULE TC_Sch ORDER BY TC_Sch.SNO;

    /* Note :-- SNO field is never changed in TEMP_COMMERCIAL_SCHEDULE */

    /* Some Variables are declared here */

    BEGIN

    strstring := 'create table temp_commercial_schedule(XPF Varchar2(1),SNo Number(10),VSL varchar2(50),Voy Varchar2(20),ET Varchar2(3)';
    PCOUNT:=0;

    FOR val1 IN CUR_CONTAINER LOOP
    PCOUNT:=PCOUNT+1;
    strstring := strstring ||','|| val1.Port ||' VARCHAR2(20)';
    END LOOP;
    strstring := strstring ||')';

    strstring2 := 'Drop table temp_commercial_schedule';

    execute immediate strstring2;
    execute immediate strstring;

    .
    .
    .
    .

    For NPC IN CUR_NPC_PORT LOOP
    strstring := 'Alter table temp_commercial_schedule Add(' || NPC.PORT || '_NPC Varchar2(20))';
    Fld_Time := NPC.Port;
    END LOOP;

    --DBMS_OUTPUT.PUT_LINE(STRSTRING);

    EXECUTE IMMEDIATE strstring; /*Here I am Altering the design of table*/

    For NPC_UP IN CUR_NPC_SCHEDULE LOOP /* ERROR COMES AT THIS LINE*/

    .....
    .....
    END LOOP;

    ....
    ....


    EXCEPTION

    ....

    END;



    Regards
    Kushal

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can't perform static SQL (i.e. your cursor) against a table definition that is defined dynamically. The column list for the cursor is determined at compile time, and does not "update" to reflect any alterations made dynamically after that. So you will need to use dynamic SQL and a REF CURSOR.

Posting Permissions

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