Q
Problem solve Get help with specific problems with your technologies, process and projects.

Why isn't my cursor returning any rows?

I have tried the following with SQL*Plus:

select distinct t1.t$trun
from ttfgld106140 t1
where t1.t$fyer = 2002
and t1.t$fprd = 9
and t1.t$leac = '84001'
and it bring me 33 records just like: ... 2961 2963 3087 Later, I tried this:
create procedure Run140 (cta IN varchar2, eje IN number, per IN number)
as
	vtrun ttfgld106140.t$trun%TYPE;

	cursor c1(c_cta IN varchar2, c_eje IN number, c_per IN number) is
	select distinct t1.t$trun
	  from ttfgld106140 t1
	 where t1.t$fyer = c_eje
	   and t1.t$fprd = c_per
	   and t1.t$leac = c_cta;
BEGIN
	open c1(cta, eje, per);
	DBMS_OUTPUT.PUT_LINE('Prueba - Run140 cta:' || cta || ' - eje:' || eje || '
	- per:' || per);
	if c1%ISOPEN then
		DBMS_OUTPUT.PUT_LINE('Abierto');
	else
		DBMS_OUTPUT.PUT_LINE('Cerrado');
	end if;
	loop
		DBMS_OUTPUT.PUT_LINE('Entro en el Lazo ' || c1%ROWCOUNT);
		fetch c1 into vtrun;
		DBMS_OUTPUT.PUT_LINE('Luego del FETCH ' || c1%ROWCOUNT);
		EXIT when c1%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(vtrun);
	end loop;
	close c1;
END;
/
and executed it with the following parameters:
execute Run140 ( '84001', 2002, 9)
but it always said:
Prueba - Run140 cta:84001 - eje:2002 - per:9
Abierto
Entro en el Lazo 0
Luego del FETCH 0

PL/SQL procedure successfully completed.
Why does the cursor always return 0 rows? Why does it never get anything from the SELECT?

I duplicated your code and could not get the problem to repeat. When I run my duplicate of your procedure, the cursor returns rows properly. Without more info, I'm afraid I can't tell you why your cursor is not returning anything.

I'd suggest that you try simply doing the statement as a SELECT INTO in the body of the code and see what happens. If the single select gives you a NO_DATA_FOUND condition, then you can at least rule out that the problem is with the cursor definition. Sorry I can't be of more help, but without being able to re-create the problem here, I'm afraid I'm unable to provide much other than suggesting to try a slightly different approach and work your way through successful attempts until you get to the error.

For More Information


Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close