OraFAQ Forum: SQL & PL/SQL » Select inside a cursor-loop

Home » SQL & PL/SQL » SQL & PL/SQL » Select inside a cursor-loop

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select inside a cursor-loop [message #40829]

Thu, 07 November 2002 09:07

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.

Report message to a moderator

Re: Select inside a cursor-loop [message #40843 is a reply to message #40829]

Thu, 07 November 2002 09:52

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

This is pretty straightforward - there is no row found on your SELECT INTO. I would double-check the any_field value - there is a least one that does not exist in table1.

Report message to a moderator

Re: Select inside a cursor-loop [message #40845 is a reply to message #40843]

Thu, 07 November 2002 10:39

Fall
Messages: 4
Registered: November 2002

Junior Member

Thanks Todd,

I replaced the EXEPTION clause in the IF THEN statement with this:

DBMS_OUTPUT.PUT_LINE('ANY_FIELD: '||reg_paso.ANY_FIELD);

And I run the select manually with the values given by that code, and the querys gives me 1 row each. If you need the complete code, tables and info, them could be provided.

Thanks again,
FALL

Report message to a moderator

Re: Select inside a cursor-loop [message #40849 is a reply to message #40845]

Thu, 07 November 2002 12:09

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

I would recommend running a query something like this that will identify any rows in your cursor that do not have a match in table1:

select *
  from (your_cursor_query) c
 where field4 is not null
   and not exists (select null
                     from table1 t1
                    where t1.field3 = c.any_field);

Report message to a moderator

Re: Select inside a cursor-loop [message #40855 is a reply to message #40845]

Thu, 07 November 2002 23:38

F. Tollenaar
Messages: 64
Registered: November 2002

Member

Try this:

DBMS_OUTPUT.PUT_LINE('ANY_FIELD: #'||reg_paso.ANY_FIELD||'#');

to be sure you don't have leading or trailing spaces.

hth
Frank

Report message to a moderator

Re: Select inside a cursor-loop [message #40863 is a reply to message #40845]

Fri, 08 November 2002 07:19

Fall
Messages: 4
Registered: November 2002

Junior Member

Thanks a lot guys!

I found an inconsistent in the data. Now everything works smoothly.

Report message to a moderator

Previous Topic:	what is svrmgrl.exe
Next Topic:	Insert subquery syntax

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 29 09:28:00 CDT 2024