Postresql: Problem with returning records

Hi Experts,

Coming from Oracle I am new to iReport as well as to Postgresql.
Here is the problem that I am facing for last few days:

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

From iReports it gives me the error message:
Caused by: org.postgresql.util.PSQLException: ERROR: cursor "(<record>)" 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;


sfukAsked:
Who is Participating?
 
sfukAuthor Commented:
Thank you very much for your help!! They both work for me.
I really appreciate it.

Best wishes
0
 
earth man2Commented:
This looks more like Enterprise DB syntax rather than PostgreSQL, so I cannot test this out.

In get_result you are looping through the cursor, so you end up at the last record.

omit
  if mycursor%isopen then
                loop
                fetch mycursor into myrec;
                exit when mycursor%notfound;
                end loop;
                close mycursor;
        end if;

also you are returning a record when you are specifying a cursor

function get_result( p_start_date  timestamp without time zone, p_end_date  timestamp without time zone) return usage_rep_sp.srr_rec is
the_cursor usage_rep_sp.srr_rec;
begin
  select usage_rep_sp.get_usage_rep(p_start_date, p_end_date)  into the_cursor from dual;
  return the_cursor;
end  get_result;

but this is then just a simple wrapper for get_usage_rep, so I'm not really sure what your requirements are.
0
 
sfukAuthor Commented:
Thank you very much for your observations!  I completely agree with you.
I don't have any special requirements. My goal is to create ireport that will return set of records from the cursor. The only way that I know is to call function from select statement. But I got lost some where creating it.  I removed loop from the get_result function.
But don't know how to re-do the return part. Please advise... Running out of time.

 
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
sfukAuthor Commented:
I also was trying to use standalone function:

CREATE OR REPLACE FUNCTION get_usage_rep(p_start_date timestamp without time zone, p_end_date timestamp without time zone)
  RETURNS sys_refcursor AS
$BODY$
  v_report            sys_refcursor;
  whereclause         varchar2(3200):= ' and  u.affiliate_id in  (select aff_id from affiliate) ';
 
begin
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$BODY$
  LANGUAGE 'edbspl' VOLATILE SECURITY DEFINER;


However, using the select statement below, don't get any result either.

select * from get_usage_rep('2009-01-01','2009-12-01')

 
0
 
earth man2Commented:
CREATE OR REPLACE FUNCTION get_usage_rep(p_start_date timestamp without time zone, p_end_date timestamp without time zone)
 RETURNS sys_refcursor AS
$BODY$
 v_report      sys_refcursor;
 whereclause   varchar2(3200) := ' and u.affiliate_id in (select aff_id from affiliate) ';
begin
  open v_report for execute
   'select distinct initcap( u.first_name) || initcap( u.last_name ) full_name ' ||
   ' from ubcsecurity.user_session s,cod_security_vw u, ultra_affiliate ua ' ||
   ' where s.user_name = u.user_name ' ||
   ' and   ua.aff_id = u.affiliate_id ' ||
   ' and   s.login >= '''|| p_start_date || ''' and   s.login <= '''|| p_end_date || ''' ' ||
   whereclause ||
   ' order by initcap( u.last_name ) || initcap( u.first_name) ';
  return v_report;
end
$BODY$ LANGUAGE 'edbspl' VOLATILE SECURITY DEFINER;
0
 
earth man2Commented:
you have to do this in a transaction so note begin and commit  .  Something like the following might work.

BEGIN;
SELECT get_usage_rep('2009-01-01','2009-12-01');

 get_usage_rep
--------------------
 <unnamed cursor 1>
(1 row)

FETCH ALL IN "<unnamed cursor 1>";
COMMIT;
0
 
sfukAuthor Commented:
Thank you for your help! I see the changes.
Could you please give me some example how I can use the Begin block?
Is there any case that I can create collection(MyTableType) based on type(MyType) and then select from MyTableType?

0
 
earth man2Commented:
If you are using latest version of postgres then see the following docs.

http://www.postgresql.org/docs/8.4/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES
If you are using an older standard then you need to use "return setof ... return next rowvar .. return" syntax.
try.

CREATE FUNCTION get_usage_rep(p_start_date timestamp without time zone, p_end_date timestamp without time zone) RETURNS TABLE( full_name varchar2(50) ) AS $$
BEGIN
    RETURN QUERY select distinct initcap( u.first_name) || initcap( u.last_name ) full_name
   from ubcsecurity.user_session s, cod_security_vw u, ultra_affiliate ua
   where s.user_name = u.user_name
   and   ua.aff_id = u.affiliate_id
   and   s.login >= p_start_date and   s.login <= p_end_date
   and u.affiliate_id in (select aff_id from affiliate)
   order by initcap( u.last_name ) || initcap( u.first_name);
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

you might be able to create dynamic query using EXECUTE keyword.

CREATE FUNCTION get_usage_rep(p_start_date timestamp without time zone, p_end_date timestamp without time zone) RETURNS TABLE( full_name varchar2(50) ) AS $$
DECLARE
  whereclause  text := ' and u.affiliate_id in (select aff_id from affiliate) ';
BEGIN
    RETURN QUERY EXECUTE  'select distinct initcap( u.first_name) || initcap( u.last_name ) full_name ' ||
   ' from ubcsecurity.user_session s,cod_security_vw u, ultra_affiliate ua ' ||
   ' where s.user_name = u.user_name ' ||
   ' and   ua.aff_id = u.affiliate_id ' ||
   ' and   s.login >= '''|| p_start_date || ''' and   s.login <= '''|| p_end_date || ''' ' ||
   whereclause ||
   ' order by initcap( u.last_name ) || initcap( u.first_name) ';
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
0
 
earth man2Commented:
Hope you met your deadline....
0
 
sfukAuthor Commented:
Yes, I did. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.