This discussion is archived
3 Replies Latest reply: Nov 14, 2012 5:52 AM by 893566 RSS

ORA-01001: invalid cursor

893566 Newbie
Currently Being Moderated
I have the below code I am trying to execute and get the error ORA-01001: invalid cursor
create or replace PROCEDURE TESTUPDATE(pNAME VARCHAR2,pID NUMBER) IS
  CN NUMBER;

   
   CURSOR C1 IS 
   SELECT CODE,SOPTION,SFILE
          STREET1,
          STREET2

   FROM TABLE1
   WHERE CODE = '10';
   
   CURSOR C2 IS
   SELECT CODE,
          STREET1,
          STREET2

   FROM TABLE2 
    WHERE CODE = '10;
 
    C1_REC C1%rowtype;
    C2_REC C2%rowtype;

BEGIN
    
   OPEN C1;
   FETCH C1 INTO C1_REC;

   SELECT COUNT(*) INTO CN FROM TABLE2
   WHERE  code =  '10'

   
      IF ( C1_REC.SOPTION = 'C' )  THEN  --- SOPTION is 'C"
         IF (CN = 0) THEN  
            IF (C1_REC.SFILE = 'FILE') THEN
          

               INSERT INTO TABLE2(ASSIGNED) VALUES('Y');
                ELSE     
               INSERT INTO TABLE2(ASSIGNED) VALUES('N');               
 
            END IF;    
     
         ELSE    /* CN > 0 */
           
           OPEN C2;
           FETCH C2 INTO C2_REC;
           IF (C1_REC.SFILE = 'FILE') THEN
             IF ( C1_REC.STREET1 IS NOT NULL OR
                 C1_REC.STREET2 IS NOT NULL ) AND
               ( C1_REC.STREET1 !=  C2_REC.STREET1 OR
                 C1_REC.STREET2 !=  C2_REC.STREET2) THEN
    

              UPDATE TABLE2
              SET    ASSIGNED = 'I';





             END IF;
          
       ELSE  /*IS NOT EQUAL TO 'FILE'*/
                    
             IF ( C1_REC.STREET1 IS NOT NULL OR
                 C1_REC.STREET2 IS NOT NULL ) AND
               ( C1_REC.STREET1 !=  C2_REC.STREET2 OR
                 C1_REC.STREET2 !=  C2_REC.STREET2) THEN
     
              UPDATE TABLE2
              SET    TABLE2_status_ind = 'N';


           END IF;
         END IF;
       END IF;

      ELSIF ( C1_REC.SOPTION = 'CR' ) THEN   /*SOPTION is 'CR"*/

         IF (CN = 0) THEN
            IF (C1_REC.SFILE = 'FILE') THEN


               INSERT INTO TABLE2(ASSIGNED) VALUES('Y');
                ELSE     
               INSERT INTO TABLE2(ASSIGNED) VALUES('N');  

              
            END IF;

     ELSE    /* CN > 0*/
       
           OPEN C2;
           FETCH C2 INTO C2_REC;  
           IF (C1_REC.SFILE = 'FILE') THEN
             IF ( C1_REC.STREET1 IS NOT NULL OR
                 C1_REC.STREET2 IS NOT NULL ) AND
               ( C1_REC.STREET1 !=  C2_REC.STREET2 OR
                 C1_REC.STREET2 !=  C2_REC.STREET2) THEN
    

              UPDATE TABLE2
              SET    TABLE2_status_ind = 'I';
              END IF;              

        ELSE  /* IS NOT EQUAL TO 'FILE'*/
             IF ( C1_REC.STREET1 IS NOT NULL OR
                 C1_REC.STREET2 IS NOT NULL ) AND
               ( C1_REC.STREET1 !=  C2_REC.STREET2 OR
                 C1_REC.STREET2 !=  C2_REC.STREET2) THEN
                 
                              
              UPDATE TABLE2
              SET    TABLE2_status_ind = 'I';
              END IF;  
              

       END IF;
     END IF;
 END IF;

CLOSE C1;
CLOSE C2;      

END IF;


EXCEPTION WHEN NO_DATA_FOUND THEN
                   dbms_output.put_line('e1');
  CLOSE C1;


 WHEN OTHERS THEN 
        CLOSE C1;
        dbms_output.put_line('e2');

END;
when I have the soption as CR and cn=0, I get the error invalid cursor. Please help me with changes to the code to overcome this error

thanks

Edited by: 890563 on 14 Nov, 2012 5:37 AM

Edited by: BluShadow on 14-Nov-2012 13:39
added {noformat}
{noformat} tags to make code readable.  After 48 questions (of which you still have 40 unanswered for some reason?) and a year on the forums, we would expect you to have learnt how to ask questions... but just in case you missed it... read here: {message:id=9360002}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 1. Re: ORA-01001: invalid cursor
    padders Pro
    Currently Being Moderated
    Hard to tell from the mess you have posted but I suspect you are trying to close cursor C2 when you never opened it.
  • 2. Re: ORA-01001: invalid cursor
    myOra_help Journeyer
    Currently Being Moderated
    It seems First cursor c1 missing semicolon at end.

    CURSOR C1 IS
    SELECT CODE,SOPTION,SFILE
    STREET1,
    STREET2

    FROM TABLE1
    WHERE CODE = '10' -----------missing ;


    SELECT COUNT(*) INTO CN FROM TABLE2
    WHERE code = '10' -----------missing here also ;

    check for syntax error first

    Edited by: myOra_help on Nov 14, 2012 7:08 PM
  • 3. Re: ORA-01001: invalid cursor
    893566 Newbie
    Currently Being Moderated
    I am sorry for posting such confusing code. I changed the last part of code code to
    END IF;
    END IF;
    CLOSE C1;
    CLOSE C2;
    END IF;



    END IF;


    EXCEPTION WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('e1');
    CLOSE C1;


    WHEN OTHERS THEN
    CLOSE C1;
    dbms_output.put_line('e2');

    END;

    and it worked with out errors

    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points