Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Location
    ha noi
    Posts
    24

    Question Unanswered: ORA-1001 Invalid Cursor!

    Hi all,
    Im using ORA 9i Database on Window Advance 2000, and I don't know how to solve the problem, my package has some procedures and fucntions, that and the error is made from the code below:
    ....
    Cursor1( ip1 varchar2, ip2 varchar2)
    Is select *
    From my_table A
    Where
    A.Col1 = ip1 AND
    A.Col2 = ip2;

    Cursor2( ip1 varchar2, ip2 varchar2, ip3 Varchar2)
    Is select *
    From my_table A
    Where
    A.Col1 = ip1 AND
    A.Col2 = ip2 AND
    A.Col3 = ip3;
    v_rec1 Cursor1%Rowtype;
    v_rec2 Cursor2%Rowtype;

    v1, v2, v3 Varchar2(20);
    Begin
    Open Cursor1(v1, v2 );
    Loop
    Fetch Cursor1 into v_rec1;
    Exit when Cursor1%notfound;
    Open Cursor2(v1, v2, v3);
    Loop
    Fetch Cursor1 into v_rec2;
    Exit when Cursor2%notfound;
    -- some code here...
    End loop;
    Close Cursor1;
    -- Some code here...
    End loop;
    Close Cursor2;
    --Some code here...
    ..........
    End;

    Please tell me where I made mistakes and how to correct them!
    Thanks a lot!

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    First off a couple mistakes:

    You cannot declare multiple variables in the same line, even if they have the same datatype.

    v1,v2,v3 varchar2(20) is NOT a legal call, so you would throw an error there.
    It needs to be :
    Code:
    v1 varchar2(20);
    v2 varchar2(20);
    v3 varchar2(20);
    Secondly, I would suggest not selecting * in your cursors. BAD habit, and you will never see this done in production.

    Third, you have cursor variables of ip1,ip2,ip3, but when you open the cursors, you instead use v1,v2,v3. NO. And you dont initialize v1,v2,v3 to anything. Dont do that.

    Third, consider using a FOR loop in your processessing to simplify things.
    EXAMPLE: (since I dont know your column names I must use *)
    Code:
    DECLARE
    v1 varchar2(20);
    v2 varchar2(20);
    v3 varchar2(30);
    
    Cursor1( ip1 varchar2, ip2 varchar2) 
    Is select * 
    From my_table A
    Where 
    A.Col1 = ip1 AND
    A.Col2 = ip2;
    
    Cursor2( ip1 varchar2, ip2 varchar2, ip3 Varchar2) 
    Is select * 
    From my_table A
    Where 
    A.Col1 = ip1 AND
    A.Col2 = ip2 AND
    A.Col3 = ip3;
    
    BEGIN
    for r_cursor1 in cursor1 (ip1, ip2)
    loop
    ...process
      for r_cursor2 in cursor2 (ip1, ip2, ip3)
      loop
      ....process
      end loop;
    end loop;
    
    END;
    Just a skeleton, but check the documentation on FOR loops, and the correct syntax! If you need, check out tahiti.oracle.com for the PL/SQL Developers Guide
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

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