| NO_DATA_FOUND in Cursor with a Select into [message #4081] |
Thu, 07 November 2002 06:54  |
Fall
Messages: 4
Registered: November 2002
|
Junior Member |
|
|
Hi List!,
I have this code:
------------------------
CREATE OR REPLACE PROCEDURE TEST IS
CURSOR C_datos is
<... some stuff in here ...>
Ln_tipo_id table1.cod_type_id%TYPE;
Ln_num_id table1.id_num%TYPE;
reg_paso C_datos%ROWTYPE;
SQL_MSG VARCHAR2 (100);
BEGIN
FOR reg_paso in C_datos LOOP
IF(reg_paso.FIELD4 is not null) THEN
Begin
SELECT field1, field2
INTO Ln_tipo_id, Ln_num_id
FROM table1
WHERE field3 = reg_paso.ANY_FIELD;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Ln_tipo_id := reg_paso.FIELD1;
Ln_num_id := reg_paso.FIELD2;
END;
ELSE
Ln_tipo_id := reg_paso.FIELD1;
Ln_num_id := reg_paso.FIELD2;
END IF;
<... Some lines goes in here ...>
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('RollBack');
SQL_MSG := SUBSTR(SQLERRM,1,100);
dbms_output.put_line(SQL_MSG);
ROLLBACK;
END;
-------------------------
Now, the problem is that if I perform the select that is inside the If clause the result is 1 line, so, there wouldn't be any problem for that SELECT INTO. But, if I run the procedure TEST it always goes through the EXCEPTION NO_DATA_FOUND. The tables has been already granted to PUBLIC but I still have the same behavior.
Maybe is something too obvious, but I really need help on this.
Thanks in advance.
|
|
|
|
| Re: NO_DATA_FOUND in Cursor with a Select into [message #4084 is a reply to message #4081] |
Thu, 07 November 2002 11:52  |
DLund
Messages: 6
Registered: October 2002
|
Junior Member |
|
|
Your use of a field named ANY_FIELD is a bit
scary, but I assume it IS a specific field from
the cursor. Your exception is probably telling
the truth. Do the CURSOR select in SQL first by
itself and then again adding the fields from
"table1" joining the cursor table with table1
via field 3 and ANY_FIELD). If the second does
not produce any results and the first does
then you have a problem with your comparison of ANY_FIELD.
i.e.
SELECT <... some stuff in here ...>
field1,
field2,
FROM cursor_table reg_paso,
table1
WHERE -
AND regpaso.ANY_FIELD = table1.field3.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
