Re: [sqr-users] How to deal with REF CURSOR???

[D Patel <dgpinfo@yahoo.com>]

Hi All,
   I got answeer from BRIO Support. Here is the one
example with SQL procedure. Hope this will help others
who wish to use this feature.

###################################################
!  Storeproc.sqr  3/20/86  List all customers in
database
! (_EOH_)
!*****************************************************
!
begin-setup
        declare-variable
         text $dept
        end-declare
end-setup
begin-report
let #dept=30
execute 
  on-error=process_err
  do=print_emp
  @#retstatus=TestPck.GetEmpData
  @indeptno=#dept, @EmpCursor=$cursor OUT
  INTO 
        &ename varchar2(40)
  do end_print_emp
end-report

begin-heading 3   ! Heading takes 3 lines, 3rd is
blank.
print 'DEPT 30 EMPLOYEE LIST' (1) center
end-heading

begin-footing 1
page-number (1,37)  'Page '
end-footing


begin-procedure print_emp
let #count = #count + 1
print &ename     (+1,30)
print #count       (0,+2,6) edit 099999
end-procedure

begin-procedure process_err
print 'Stored Procedure Processing Error' (+1,30)
print #retstatus                          (0,+2,6)
end-procedure

begin-procedure end_print_emp
print 'The procedure return status' (+2,30)
print #retstatus   (0,+2,6) edit 099999
end-procedure
###################################################
======================================================
create or replace package TestPck is
  -- Author  : DPATEL
  -- Created : 09-Sep-2003
  -- Purpose : 
  -- Public constant declarations
   TYPE m_refCur IS  REF CURSOR;
  -- Public function and procedure declarations
  Procedure sp_ListEmpInpOnly(name_in VARCHAR2, rs OUT
m_refCur);
  PROCEDURE GetEmpData(indeptno NUMBER, EmpCursor OUT
m_refCur); 
end TestPck; 
/

create or replace package body TestPck IS
 procedure sp_ListEmpInpOnly(name_in varchar2, rs OUT
m_refCur) is
   begin
     OPEN rs FOR SELECT Emp.Ename, Emp.Empno FROM Emp
       WHERE emp.Ename LIKE NVL(name_in, emp.Ename);
   end sp_ListEmpInpOnly;

  PROCEDURE GetEmpData(indeptno NUMBER, EmpCursor OUT
m_refCur) IS
  BEGIN
     OPEN EmpCursor FOR SELECT Ename FROM Emp
       WHERE emp.Deptno = NVL(indeptno, emp.Deptno);
  END GetEmpData;
end TestPck;
/
======================================================

--- D Patel <dgpinfo@yahoo.com> wrote:
> Hi ALL,
>    I am working on SQR Reports (V8.1.0) and we are
> fetching data from ORACLE9i as database. Can I use
> REF
> Cursor returned by ORACLE Procedure? If yes, how??
>   Thanks in advance.
> Thanks,
> D Patel
> 
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users