Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    4

    Unanswered: Cursor inside a cursor inside another cursor...

    Hello! Sorry for the bad english... I'm from Brazil...
    I'm migrating a SQL Server stored procedure to an oracle stored procedure, but I'm getting the following error from the compiler:

    "Error(42,17): PLS-00103: Encountered the symbol "OPEN" when expecting one of the following: . ( * @ % & - + / at mod remainder rem select update with <um expoente (**)> delete insert || execute multiset save merge"

    The SP:
    Code:
    create or replace
    PROCEDURE USP_TESTE2
    authid current_user as pragma AUTONOMOUS_TRANSACTION; 
    Begin
      Declare
        TYPE r_cursor IS REF CURSOR;
        TYPE r_cursor2 IS REF CURSOR;
        TYPE r_cursor3 IS REF CURSOR;
        CUR r_cursor;
        CUR2 r_cursor2;
        CUR3 r_cursor3;
        
        TYPE Material IS TABLE OF temp_CLASSE.MATERIAL%TYPE; 
        TYPE Familia IS TABLE OF CARACTERISTICA_FAMILIA.OBJEK%TYPE; 
        TYPE ATINN IS TABLE OF AUSP_MATERIAL_TMP.ATINN%TYPE; 
        TYPE ATWRT_FAMILIA IS TABLE OF temp_CARACTERISTICA_FAMILIA.ATWRT%TYPE; 
        TYPE ATWRT_MATERIAL IS TABLE OF temp_CARACTERISTICA_MATERIAL.ATWRT%TYPE; 
        TYPE CLINT IS TABLE OF temp_CARACTERISTICA_COMUM.CLINT%TYPE; 
        TYPE IMEK IS TABLE OF temp_CARACTERISTICA_COMUM.CLINT%TYPE; 
        
        t01 Material;
        t02 Familia;
        t03 ATINN;
        t04 ATWRT_FAMILIA;
        t05 ATWRT_MATERIAL;
        t06 CLINT;
        t07 IMEK;
        
      Begin
            OPEN CUR FOR 
                SELECT OBJEK, ATINN, ATWRT
                FROM CARACTERISTICA_FAMILIA
                WHERE OBJEK IN (
                    SELECT OBJEK 
                    FROM temp_PROVAVEL_FAMILIA
                );
            LOOP
                FETCH CUR  BULK COLLECT INTO t02, t03, t04
                LIMIT 1000;
                EXIT WHEN  t02.COUNT = 0; 
                FORALL x IN t02.FIRST .. t02.LAST
                    OPEN CUR2 FOR 
                        Select CLINT, IMERK, Material 
                        From temp_CARACTERISTICA_COMUM 
                        Where IMERK = t03(x);
                    LOOP
                        Fetch CUR2 BULK COLLECT Into t06, t07, t01
                        LIMIT 1000;
                        EXIT WHEN  t06.COUNT = 0; 
                        FORALL y IN t06.FIRST .. t06.LAST
                            IF (%ROWCOUNT = 0) THEN
                                OPEN CUR3 FOR
                                    Select OBJEK, ATWRT
                                    From temp_CARACTERISTICA_MATERIAL
                                    Where OBJEK = t01(y) 
                                        And ATINN = t03(x);
                                LOOP
                                    Fetch CUR3 BULK COLLECT Into t01, t05
                                    LIMIT 1000;
                                    EXIT WHEN  t01.COUNT = 0; 
                                    FORALL z IN t01.FIRST .. t01.LAST
                                        IF (t05(z) <> t04(x)) THEN
                                                Delete
                                                From temp_PROVAVEL_FAMILIA
                                                Where OBJEK = t02(x)
                                                    And MATERIAL = t01(z)
                                                COMMIT;
                                        END IF;
                                END LOOP;
                                CLOSE CUR3;
                                COMMIT;
                            END IF;
                     END LOOP;
                     CLOSE CUR2;
                     COMMIT;
                  End;
            END LOOP;
            CLOSE CUR;
            COMMIT;
        Exception
        When OTHERS then
          RAISE_APPLICATION_ERROR (-20000 , 'ERRO - ' || SQLERRM);
      end;
    end USP_TESTE2;
    I don't know if I'm using the cursor correctly... I hope you guys can help me!

    Thank you very much!
    Last edited by marcel.fernandes; 08-12-09 at 14:38.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >create or replace
    >PROCEDURE USP_TESTE2
    >authid current_user as pragma AUTONOMOUS_TRANSACTION;
    >Begin
    >Declare
    >TYPE r_cursor IS REF CURSOR;
    >TYPE r_cursor2 IS REF CURSOR;
    >TYPE r_cursor3 IS REF CURSOR;

    create or replace
    PROCEDURE USP_TESTE2
    as
    authid current_user as pragma AUTONOMOUS_TRANSACTION;
    TYPE r_cursor IS REF CURSOR;
    TYPE r_cursor2 IS REF CURSOR;
    TYPE r_cursor3 IS REF CURSOR;

    Changes above may get you closer; then againit may not

    http://www.dbforums.com/oracle/10316...s-posters.html
    Please Read & HEED section about VB code tags.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2009
    Posts
    4
    Thanks anacedent! But I'm still getting the same error...

    I'm trying to declare the cursors in the "Declare" area, but I don't know how to do it... I'm trying to do like this:

    Code:
    CURSOR CUR IS 
                SELECT OBJEK, ATINN, ATWRT
                FROM CARACTERISTICA_FAMILIA
                WHERE OBJEK IN (
                    SELECT OBJEK 
                    FROM temp_PROVAVEL_FAMILIA
                );
    But I don't know how to do it with the cursor CUR2 and CUR3...

  4. #4
    Join Date
    Aug 2009
    Posts
    4
    Hello!

    The SP compiled, but instead of use a Where clause I use IF (...) THEN...

    Code:
    create or replace
    PROCEDURE USP_TESTE 
    authid current_user as pragma AUTONOMOUS_TRANSACTION; 
    Begin
        Declare
           
            CURSOR CUR IS 
                SELECT OBJEK, ATINN, ATWRT
                FROM CARACTERISTICA_FAMILIA
                WHERE OBJEK IN (
                    SELECT OBJEK 
                    FROM temp_PROVAVEL_FAMILIA
                );
            
            CURSOR CUR2 IS 
                SELECT CLINT, IMERK, Material 
                FROM temp_CARACTERISTICA_COMUM; 
                --WHERE IMERK = curATINN;
                
            CURSOR CUR3 IS 
                Select OBJEK, ATWRT, ATINN
                From temp_CARACTERISTICA_MATERIAL;
                --Where OBJEK = curMaterial
                --    And ATINN = curATINN;
            
        BEGIN
            FOR REG1 IN CUR LOOP
                FOR REG2 IN CUR2 LOOP
                    IF (REG2.IMERK = REG1.ATINN) THEN
                        FOR REG3 IN CUR3 LOOP
                            IF (REG3.OBJEK = REG2.Material AND REG3.ATINN = REG1.ATINN) THEN
                                Delete
                                From temp_PROVAVEL_FAMILIA
                                Where OBJEK = REG1.OBJEK
                                    And MATERIAL = REG2.Material;
                                COMMIT;
                            END IF;
                        END LOOP;
                    END IF;
                END LOOP;
            END LOOP;
            
            Exception
            When OTHERS then
              RAISE_APPLICATION_ERROR (-20000 , 'ERRO - ' || SQLERRM);
      end;
    end USP_TESTE;
    But this is not the efficiently way to do it... any suggestions?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    DELETE FROM temp_provavel_familia tpf
    WHERE       tpf.objek IN (SELECT reg1.objek
                              FROM   caracteristica_familia reg1,
                                     temp_caracteristica_comum reg2
                              WHERE  reg2.imerk = reg1.atinn
                                     AND reg1.objek = tpf.objek
                                     AND tpf.material = reg2.material
                                     AND reg2.material IN (SELECT reg3.objek
                                                           FROM   temp_caracteristica_material reg3
                                                           WHERE  reg3.atinn = reg1.atinn))
    without DDL for tables & DML for test data, I can't be sure if it is correct or not.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Aug 2009
    Posts
    4
    anacedent, thank you very very much!

    I will test it!

    Thanks again!!!

Posting Permissions

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