3 Replies Latest reply: Nov 14, 2012 7:52 AM by 893566 RSS

    ORA-01001: invalid cursor

    893566
      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
          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
            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
              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