Function does not return any data

Hi Experts,
here is the function that I am having a problem with. It doesn't return any data. However, when I run the select statement from SQL*PLUS it brings data back. what is the problem?
Please help!
CREATE OR REPLACE FUNCTION get_usage_rep(p_start_date date, p_end_date date)
  RETURN sys_refcursor AS
  v_report            sys_refcursor;
  whereclause         varchar2(3200):='and  u.affiliate_id in  (select aff_id from ultra_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;

sfukAsked:
Who is Participating?
 
shru_0409Commented:
u r absulatly right u can not use directoly to your report either u have to create a procedure and insert the value in temporary table or call the function in another procedure and do the same as said above.

for more information pls check this link.

http://www.experts-exchange.com/Database/Oracle/Q_24937120.html
0
 
shru_0409Commented:
nothing is wrong in this function .. for more information pls check this link
http://www.orafaq.com/node/980
0
 
shru_0409Commented:
this link also give the more in detail....
http://www.enterprisedb.com/documentation/spl-ref-cursors.html
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
shru_0409Commented:
for this query group by is not require... remove the group by and try it...
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
try to format the dates

CREATE OR REPLACE FUNCTION get_usage_rep(p_start_date date, p_end_date date)
  RETURN sys_refcursor AS
  v_report            sys_refcursor;
  whereclause         varchar2(3200):='and  u.affiliate_id in  (select aff_id from ultra_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 >='||'to_date('||''''||to_char(p_start_date,'dd/mm/yyyy')||''','||''''||'dd/mm/yyyy'||''') '
     ||' and   s.login <='||'to_date('||''''||to_char(p_end,'dd/mm/yyyy')||''','||''''||'dd/mm/yyyy'||''') '
     || 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;
 
0
 
shru_0409Commented:
CREATE OR REPLACE FUNCTION get_usage_rep(p_start_date date, p_end_date date)
  RETURN sys_refcursor AS
  v_report            sys_refcursor;
  whereclause         varchar2(3200):='and  u.affiliate_id in  (select aff_id from ultra_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 >= '''|| to_char(p_start_date,'dd-mon-yy')|| ''' and   s.login < '''|| to_char(p_end_date+1,'dd-mon-yy')|| ''' '
     || 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;


try this
0
 
sfukAuthor Commented:
Thanks a lot for your help!
I am calling the function with
select get_usage_rep(to_date('01-JAN-2009','DD-MON-YYYY'),to_date('01-APR-2009','DD-MON-YYYY')) from dual
or
select get_usage_rep('01-JAN-2009','01-APR-2009') from dual
It still does not return anything back. But the data is there. Am I doing this right?
0
 
sfukAuthor Commented:
I see the problem: I was trying to run the select statement from TOAD, however it did not anything.
When I tried SQL*PLUS  I got the exact result that is described at http://www.orafaq.com/node/980

GET_USAGE_REP('01-JA
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

FULL_NAME
--------------------------
name1
name2
name2

Could you suggest how I can call the function and get a result without
GET_USAGE_REP('01-JA
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1
I need to run the function using the select statement at iReports.
Thanks!
0
 
shru_0409Commented:
http://www.psoug.org/reference/ref_cursors.html

check this link it will give better idea about ref cursor....
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
i think it is a property of sqlplus only, u can use  the function in reports
0
 
sfukAuthor Commented:
No, unfortunately I can not use the function as it is in iReports.
There is useful link  that I found
http://it.toolbox.com/blogs/jjflash-oracle-journal/ref-cursors-pipelined-table-functions-and-adf-bc-20740

0
 
sfukAuthor Commented:
Thanks for the help.
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.