Problem with returning records

Here is the problem with Postgresql that I am facing for last few days:

Using select usage_rep_sp.get_result('2009-01-01','2009-12-01')full_name from dual; The package below supposes to return a number of records((at least 5 different names) However it returns just one.

From iReports it gives me the error message: Caused by: org.postgresql.util.PSQLException: ERROR: cursor "()" does not exist ;

Could you help me with these problems?

CREATE OR REPLACE PACKAGE usage_rep_sp
IS

type usage_type is record (
full_name     varchar2(50));
-- 
 type srr_rec is ref cursor return usage_type;
 type mycursor is ref cursor;

function get_usage_rep(p_start_date  timestamp without time zone,
  p_end_date  timestamp without time zone)
  return srr_rec;

function get_result(p_start_date timestamp without time zone, p_end_date timestamp without time zone) return mycursor;

END usage_rep_sp;

CREATE OR REPLACE PACKAGE BODY usage_rep_sp
IS
function get_usage_rep
 (p_start_date  timestamp without time zone, p_end_date  timestamp without time zone)

  return      srr_rec
  is
  v_report            srr_rec;
  v_temp              varchar2(50):=' ';
  v_aff_level         varchar2(30);
  commapos            number ;        
  outstring           varchar2(50) := upper(v_temp) ;      
  vquery              varchar2(3200);
  whereclause         varchar2(3200);

begin
if v_temp =' ' or v_temp is null then
whereclause  := 'and  u.affiliate_id in  (select aff_id from ultra_affiliate)';      
else
for index_var in 1..50        
loop              
commapos   := instr(outstring,',',1,index_var) ;              
exit when commapos=0 ;          
outstring  := substr(outstring,1,(commapos-1))||''','''|| substr(outstring,(commapos+1));          
end loop ;                  
--outstring    := '('''||outstring||''')' ;  
v_temp      := outstring ;  


if v_aff_level= 'COUNCIL' then        
whereclause     := 'and  u.affiliate_id in  (select aff_id from ultra_affiliate where  council_id = '''|| v_temp ||''')';
elsif v_aff_level = 'DISTRICT' then
whereclause     := 'and  u.affiliate_id in  (select aff_id from ultra_affiliate where district = '''|| v_temp ||''')';
elsif v_aff_level= 'LOCAL' then
    whereclause     := 'and  u.affiliate_id in (select aff_id from ultra_affiliate where aff_id = '''|| v_temp ||''')';
end if;
end if;


open v_report for
  'select distinct initcap( u.first_name) || initcap( u.last_name )full_name '
     || chr (10)
     ||' from ubcsecurity.user_session s,cod_security_vw u, ultra_affiliate ua '
     || chr (10)
     ||' where s.user_name      = u.user_name '
     || chr (10)    
     ||' and   ua.aff_id = u.affiliate_id '
     || chr (10)    
     ||' and   s.login >= '''|| p_start_date|| ''' and   s.login <= '''|| p_end_date|| ''' '
     || chr (10)
     || whereclause
     || chr (10)
     || ' group by initcap( u.first_name) || initcap( u.last_name ) '
     ||开发者_如何学C chr(10)
     ||' order by initcap( u.first_name) || initcap( u.last_name ) ';

   return v_report;
end get_usage_rep;

function get_result(p_start_date  timestamp without time zone, p_end_date  timestamp without time zone) return mycursor
is
        mycursor usage_rep_sp.srr_rec;
        myrec    usage_rep_sp.usage_type;

        begin

       select usage_rep_sp.get_usage_rep(p_start_date, p_end_date)
        into mycursor from dual;

       if mycursor%isopen then
                loop
                fetch mycursor into myrec;
                exit when mycursor%notfound;
                end loop;
                close mycursor;
        end if;
return myrec;
end  get_result;
END usage_rep_sp;

I have no idea what you use, but this is not PostgreSQL. PostgreSQL doesn't have "dual" (it's Oracle thing). It doesn't have PACKAGES. there is no %isopen operator. there is virtually no reason to use cursors in PostgreSQL in functions. there is no varchar2 datatype in PostgreSQL.

It appears that you are trying to combine the declare and the open on the cursor. I don't have EnterpriseDB, but I have never seen this on Oracle or PostgreSQL. You may need to declare the cursor, then open it.