Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

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;

0
sfuk
Asked:
sfuk
  • 6
  • 4
  • 2
1 Solution
 
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
 
shru_0409Commented:
for this query group by is not require... remove the group by and try it...
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now