Problem with returning records

Question

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 ) '
     || 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;

Answer 1

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.

Answer 2

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.