Home » SQL & PL/SQL » SQL & PL/SQL » Fetching Cursor problem
Fetching Cursor problem [message #337360] Wed, 30 July 2008 12:02 Go to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, i'd like to know if someone could tell me where am I making a mistake. The following code fetches 2 cursors at the same time comparing values wich determine some actions to do. One of the cursor can end before than the other one, so I continue fetching when the loop exits. The problem is that when it start's the "second" fetch an exception raises(I put ***** in the line that causes it). Thanks in advance.
PROCEDURE updmixers (p_emixer IN CLOB)
IS
   c_enterprises   TYPES.cursors;
   c_temp_enterprises     TYPES.cursors;
   v_temp_enterprise      enterprises.ent%TYPE;
   v_enterprise    enterprises.ent%TYPE;
   v_code     enterprises.code%TYPE;
   v_nles           enterprises.nles%TYPE      := get_nles (p_emixer);
   v_new_code    enterprises.code%TYPE;
   v_temp_notfound     BOOLEAN                  := FALSE;
   v_notfound   BOOLEAN                  := FALSE;
BEGIN

   set_table (p_emixer);

   OPEN c_temp_enterprises FOR
      SELECT DISTINCT (enterprise) enterprises
                 FROM enterprises_temp
             ORDER BY enterprise;

   OPEN c_enterprises FOR
      SELECT DISTINCT (enterprise) enterprise, code
                 FROM  enterprises
                WHERE nles = v_nles
             ORDER BY enterprise;

   FETCH c_temp_enterprises
    INTO v_temp_enterprise;

   FETCH c_enterprises
    INTO v_enterprise, v_code;

   v_temp_notfound := c_temp_enterprises%NOTFOUND;
   v_mtw101_notfound := c_enterprises%NOTFOUND;

   WHILE (NOT (v_temp_notfound) AND NOT (v_notfound))
   LOOP
      IF v_enterprise < v_temp_enterprise
      THEN
         del_enterprise (v_enterprise, v_nles);

         FETCH c_enterprises
          INTO v_enterprise, v_code;

         v_notfound := c_enterprises%NOTFOUND;
      ELSE
         IF v_enterprise > v_temp_enterprise
         THEN
            add_enterprise (v_temp_enterprise, p_emixer);
            FETCH c_temp_enterprises
             INTO v_temp_enterprise;

            v_temp_notfound := c_temp_enterprises%NOTFOUND;
         ELSE
            upd_enterprise (v_temp_enterprise, p_emixer);

            FETCH c_temp_enterprises
             INTO v_temp_enterprise;

            FETCH c_enterprise
             INTO v_enterprise, v_code;

            v_temp_notfound := c_temp_enterprises%NOTFOUND;
            v_notfound := c_enterprises%NOTFOUND;
         END IF;
      END IF;
   END LOOP;

   IF (NOT (v_temp_notfound))
   THEN
      LOOP
         add_enterprise (v_temp_empresa, p_emixer);

         FETCH c_temp_enterprises
          INTO v_temp_enterprise;*****

         EXIT WHEN c_temp_enterprises%NOTFOUND;
      END LOOP;
   END IF;

   IF (NOT (v_notfound))
   THEN
      LOOP
         del_enterprise (v_enterprise, v_nles);

         FETCH c_enterprises
          INTO v_enterprise;*****

         EXIT WHEN c_enterprises%NOTFOUND;
      END LOOP;
   END IF;

   CLOSE c_temp_enterprises;

   CLOSE c_mtw101_enterprises;

   COMMIT;
END;

[EDITED by LF: the code was already formatted, so I have just added [code] tags to preserve formatting]

[Updated on: Wed, 30 July 2008 12:29] by Moderator

Report message to a moderator

Re: Fetching Cursor problem [message #337363 is a reply to message #337360] Wed, 30 July 2008 12:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It might help us if you'd tell us which exception is thrown..
Re: Fetching Cursor problem [message #337366 is a reply to message #337360] Wed, 30 July 2008 12:13 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Fetching Cursor problem [message #337378 is a reply to message #337360] Wed, 30 July 2008 12:50 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, thanks for replying so fast and pardon me for not posting in the correct form. The exception that's throwing is:
-6504 - ORA-06504: PL/SQL: Return types of Result Set variables or query do not match.

It really confuses me, since the fetch is the same that Im using in the previous loop.
Re: Fetching Cursor problem [message #337398 is a reply to message #337378] Wed, 30 July 2008 15:05 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
It really confuses me, since the fetch is the same that Im using in the previous loop.

Maybe; these are cursors:
c_temp_enterprises: SELECT enterprise       FROM enterprises_temp
c_enterprises     : SELECT enterprise, code FROM enterprises
and these are variables:
FETCH c_temp_enterprises INTO v_temp_enterprise
                         ->   v_temp_enterprise      enterprises.ent%TYPE

FETCH c_enterprises      INTO v_enterprise
                         ->   v_enterprise           enterprises.ent%TYPE

So, how about declaring
v_temp_enterprise      enterprises_temp.ent%TYPE
instead of
v_temp_enterprise      enterprises.ent%TYPE
Re: Fetching Cursor problem [message #337443 is a reply to message #337360] Wed, 30 July 2008 22:23 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Here are your fetches:

   FETCH c_temp_enterprises
    INTO v_temp_enterprise;

   FETCH c_enterprises
    INTO v_enterprise, v_code;

         FETCH c_enterprises
          INTO v_enterprise, v_code;

            FETCH c_temp_enterprises
             INTO v_temp_enterprise;

            FETCH c_temp_enterprises
             INTO v_temp_enterprise;

            FETCH c_enterprise
             INTO v_enterprise, v_code;

         FETCH c_temp_enterprises
          INTO v_temp_enterprise;                *****

         FETCH c_enterprises
          INTO v_enterprise;                     *****


Notice anything unusual about them?

Maybe if we reorder them and edit them a little to make it easier to see:

 FETCH c_temp_enterprises INTO v_temp_enterprise;
 FETCH c_temp_enterprises INTO v_temp_enterprise;
 FETCH c_temp_enterprises INTO v_temp_enterprise;
 FETCH c_temp_enterprises INTO v_temp_enterprise;


 FETCH c_enterprises INTO v_enterprise, v_code;
 FETCH c_enterprises INTO v_enterprise, v_code;
 FETCH c_enterprise INTO v_enterprise, v_code;
 FETCH c_enterprises INTO v_enterprise;


You should be seeing two errors.

Good luck, Kevin
Re: Fetching Cursor problem [message #337461 is a reply to message #337443] Thu, 31 July 2008 00:10 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Huh, now THAT's analysis!
Re: Fetching Cursor problem [message #337649 is a reply to message #337461] Thu, 31 July 2008 08:47 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
I saw them!!!!. Thank you!, I can't belive that was the error. I was so into the problem that I couldn't see such a simple mistake!.
Re: Fetching Cursor problem [message #337671 is a reply to message #337360] Thu, 31 July 2008 10:19 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
don't sweat it. We like'm easy.
Previous Topic: Performance Issue
Next Topic: UTL_FILE overwrites data
Goto Forum:
  


Current Time: Mon Dec 05 14:58:23 CST 2016

Total time taken to generate the page: 0.12125 seconds