CREATE TABLE temp1
   (	SERVERNAME_IPADDRESS VARCHAR2(30 BYTE), 
	USERNAME VARCHAR2(128 BYTE), 
	PASSWORD VARCHAR2(40 BYTE), 
	LONGUSERNAME VARCHAR2(128 BYTE), 
	DIRECTORY VARCHAR2(120 BYTE)
   )
/

insert into temp1 (SERVERNAME_IPADDRESS,USERNAME,PASSWORD,LONGUSERNAME,DIRECTORY)
values ('1','A','B','C','D')
/

create or replace PACKAGE pkg_temp IS
TYPE RFC_Online IS REF CURSOR;
procedure prc_temp;
end pkg_temp;
/

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;
begin
sql_ftp := 'select SERVERNAME_IPADDRESS,USERNAME,PASSWORD,DIRECTORY from temp1 where LONGUSERNAME = ''C''';
OPEN c_ret_lnoff_ftp FOR sql_ftp;
DBMS_OUTPUT.PUT_LINE('c_ret_lnoff_ftp%rowcount = ' || c_ret_lnoff_ftp%rowcount);
IF c_ret_lnoff_ftp%rowcount <> 0 THEN
     LOOP
	FETCH c_ret_lnoff_ftp INTO FTP_SERVER,FTP_USER,FTP_PASS,FTP_DEST_LOCATION;
	EXIT WHEN c_ret_lnoff_ftp%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE ('In the loop..!!');
     END LOOP;
else DBMS_OUTPUT.PUT_LINE ('No Records Fetched..!!');
end if;
end prc_temp;
end pkg_temp;
/