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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.