Home » SQL & PL/SQL » SQL & PL/SQL » No records fetch in Cursor. (Oracle 10g)
No records fetch in Cursor. [message #310440] Tue, 01 April 2008 05:06 Go to next message
ramya.pathak
Messages: 15
Registered: August 2007
Junior Member
Hi,

I have a package in which I have used a cursor.
This cursor is not fetching any records even if database does have the matching record.
If I run the same query in SQL Plus or SQL developer it fetches the corresponding record.
Attached is the complete script that u can run. Just try to run the package. It says 'No Record Fetched..!!'

Any suggestions..??

Thanks.
Re: No records fetch in Cursor. [message #310467 is a reply to message #310440] Tue, 01 April 2008 06:42 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
You are referencing %ROWCOUNT before performing the first FETCH.

Look at: http://www.unix.org.ua/orelly/oracle/prog2/ch06_09.htm

It says:

Quote:
The %ROWCOUNT attribute returns the number of records fetched from a cursor at the time that the attribute is queried. When you first open a cursor, its %ROWCOUNT is set to zero. If you reference the %ROWCOUNT attribute of a cursor that is not open, you will raise the INVALID_CURSOR exception. After each record is fetched, %ROWCOUNT is increased by one.


FETCH before referencing %ROWCOUNT.

HTH.
Re: No records fetch in Cursor. [message #310483 is a reply to message #310440] Tue, 01 April 2008 07:09 Go to previous messageGo to next message
ramya.pathak
Messages: 15
Registered: August 2007
Junior Member
Thanks Michael,

I have got this error. I have changed my code to have one for query which does select count(*) into CNTR.

I am checking this CNTR variable for <> 0. This condition is necessary as if not followed, I have some statements in ELSE part.

Suggest me if there is any other BETTER way I can achieve the above.

Thanks again..
Re: No records fetch in Cursor. [message #310491 is a reply to message #310440] Tue, 01 April 2008 07:24 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try:

create or replace PACKAGE BODY pkg_temp IS
procedure prc_temp is
c_ret_lnoff_ftp RFC_Online;
FTP_SERVER VARCHAR2(50);
FTP_USER VARCHAR2(200);
FTP_PASS VARCHAR2(50);
FTP_DEST_LOCATION VARCHAR2(500);
sql_ftp VARCHAR2(1000) DEFAULT NULL;
i NUMBER := 0;
begin
sql_ftp := 'select SERVERNAME_IPADDRESS,USERNAME,PASSWORD,DIRECTORY from temp1 where LONGUSERNAME = ''C''';
OPEN c_ret_lnoff_ftp FOR sql_ftp;
  LOOP
    FETCH c_ret_lnoff_ftp
         INTO FTP_SERVER,FTP_USER,FTP_PASS,FTP_DEST_LOCATION;
    EXIT WHEN c_ret_lnoff_ftp%NOTFOUND;
    i := i + 1;
    DBMS_OUTPUT.PUT_LINE ('In the loop..!!');
  END LOOP;
CLOSE OPEN c_ret_lnoff_ftp;
IF ( i = 0 ) THEN
  DBMS_OUTPUT.PUT_LINE ('No Records Fetched..!!');
end if;
end prc_temp;
end pkg_temp;
Previous Topic: when a ROWNUM value is actually assigned ?
Next Topic: using field from main query as parameter in PL/SQL code of formula field
Goto Forum:
  


Current Time: Fri Dec 02 12:43:57 CST 2016

Total time taken to generate the page: 0.12413 seconds