Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    bangalore
    Posts
    3

    Exclamation Unanswered: ORA-01007: variable not in select list

    Hi Guys,
    Am new to your group and expect some help. Here's my stored proc :

    PROCEDURE TestRank3(cur in out report_package.report_cursor,
    projectId_param IN VARCHAR2)

    AS
    str3 varchar2(1000):='';

    BEGIN

    str3:=str3 || ' select SC.SCORECARD_ID AS "Scorecard_id",';
    str3:=str3 || ' PROJECT_ID AS "ProjectId"';
    str3:=str3 || ' FROM SCORECARD SC where ';
    str3:=str3 || 'project_id in ('||projectId_param || ')';

    -- str3:='select * from scorecard where project_id in('||projectId_param || ' )';

    dbms_output.put_line('Str3 : '|| str3);
    OPEN cur FOR
    str3;

    EXCEPTION
    WHEN OTHERS THEN
    RAISE;
    END;

    -------------------------------------------------------------------------
    And here is the cursor i am using to execute and fetch results :
    declare
    myCur report_package.report_cursor;
    myvar scorecard%rowtype;
    begin
    TestRank3(myCur,'2251,2254');
    loop
    fetch myCur into myvar;
    exit when myCur%notfound;
    dbms_output.put_line('Sc no :' || myvar.scorecard_id||
    ' project id : ' || myvar.project_id);
    end loop;
    end;
    ----------------------------------------------------

    Can Anybody help on this fast? Email me on abhilash09@hotmail.com... URGENT!!!!
    i KEEP GETTING THE FOLLOWING ERROR :
    ERROR at line 1:
    ORA-01007: variable not in select list
    ORA-06512: at line 7


    THANKS!
    ABHILASH

  2. #2
    Join Date
    Mar 2004
    Location
    India
    Posts
    4
    I think you are missing

    "OPEN myCur" before loop

    Try

    Rohan

  3. #3
    Join Date
    Mar 2004
    Posts
    23

    Re: ORA-01007: variable not in select list

    myvar scorecard%rowtype;

    This record definition better contain only 2 columns because
    your select statement only asked for 2 columns. If you're
    going to fetch into a record type then the select list has to match.

    Is there some reason you don't combine all the set-up, open, fetch,
    display, close in one procedure? Why separate the open cursor from
    the actual fetching? If all combined, I would just declare a temp
    record type with the 2 columns you want and fetch/display that.

  4. #4
    Join Date
    Mar 2004
    Location
    bangalore
    Posts
    3

    defining two columns only

    well, can u show me how i will define only two columns in the cursor? i still dont get u when you say define only two columns in the temp variable

  5. #5
    Join Date
    Mar 2004
    Posts
    23

    Re: defining two columns only

    One simple solution could be to just use SELECT *. You had this
    commented out for some reason.

    If the scorecard table has only 6 columns and you just want the first 2,
    could also use:

    SELECT sc.scorecard_id, sc.project_id, null,null,null,null

    The above would satisfy the cursor and myvar scorecard%rowtype
    but I probably would not recommend doing it this way.

    You could also define your own record type, a subset of columns that
    you want. I use this all the time because a table might have 30
    columns and I only want 6 of them.

    TYPE myvar_type IS RECORD (
    scorecard_id scorecard.scorecard_id%TYPE,
    project_id scorecard.project_id%TYPE);

    my_var myvar_type;

    If you wanted a PL/SQL table:

    TYPE myvar_table IS TABLE OF myvar_type
    INDEX BY BINARY_INTEGER;

    myvar_recs myvar_table;

    Hope this helps . . . .

  6. #6
    Join Date
    Mar 2004
    Location
    bangalore
    Posts
    3

    Thanks

    thanks your suggestion solved the problems....

Posting Permissions

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