Fetching Cursor problem [message #337360] |
Wed, 30 July 2008 12:02  |
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 #337378 is a reply to message #337360] |
Wed, 30 July 2008 12:50   |
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.
|
|
|
|
|
|
|
|