?
Solved

Postresql: Problem with returning records

Posted on 2009-12-18
10
Medium Priority
?
903 Views
Last Modified: 2012-05-08
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;


0
Comment
Question by:sfuk
  • 5
  • 5
10 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 26100941
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
 

Author Comment

by:sfuk
ID: 26101185
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
 

Author Comment

by:sfuk
ID: 26101295
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 22

Expert Comment

by:earth man2
ID: 26108216
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
 
LVL 22

Expert Comment

by:earth man2
ID: 26108253
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
 

Author Comment

by:sfuk
ID: 26108864
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
 
LVL 22

Assisted Solution

by:earth man2
earth man2 earned 2000 total points
ID: 26109282
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
 

Accepted Solution

by:
sfuk earned 0 total points
ID: 26113951
Thank you very much for your help!! They both work for me.
I really appreciate it.

Best wishes
0
 
LVL 22

Expert Comment

by:earth man2
ID: 26115021
Hope you met your deadline....
0
 

Author Comment

by:sfuk
ID: 26115160
Yes, I did. Thanks!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Integration Management Part 2
Suggested Courses
Course of the Month16 days, 21 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question