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;
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.