Home » Developer & Programmer » Reports & Discoverer » Building Reports in Dev. Report 10g with Ref Cursor Query
Building Reports in Dev. Report 10g with Ref Cursor Query [message #537182] Tue, 27 December 2011 03:23 Go to next message
oralover2006
Messages: 144
Registered: January 2010
Location: India
Senior Member
hi all,

i was searching for the example of subject mentioned and visited the following website
http://docs.oracle.com/html/B10602_01/orbr_refcur.htm


here is a tutorial to create such report, but i am confused as here in Report's Data Model they created 3 Ref Cursor Queries and did not Close the opened cursor, is there no need of this or any mistake or if it necessary, where have to Close the cursor?

First, they have created following Packages
PACKAGE concl_cv IS
    type conclass_rec is RECORD 
       (EMPLOYEE_ID NUMBER(6),
     FIRST_NAME VARCHAR2(20),
     LAST_NAME VARCHAR2(25),
     e-mail VARCHAR2(25),
     PHONE_NUMBER VARCHAR2(20),
     HIRE_DATE DATE,
     JOB_ID VARCHAR2(10),
     SALARY NUMBER(8,2),
     DEPARTMENT_ID NUMBER(4));
    type conclass_refcur is REF CURSOR return conclass_rec;
END;

PACKAGE cont_cv IS
    type container_rec is RECORD 
       (EMPLOYEE_ID NUMBER(6),
        START_DATE DATE,
        END_DATE DATE,
        JOB_ID VARCHAR2(10),
        DEPARTMENT_ID NUMBER(4));
    type container_refcur is REF CURSOR return container_rec;
END;

PACKAGE port_cv IS
  type portdesc_rec is RECORD 
       (DEPARTMENT_ID NUMBER(4),
        DEPARTMENT_NAME VARCHAR2(30));
  type portdesc_refcur is REF CURSOR return portdesc_rec;
END;


then, created Functions which created in Ref Cursor Query in Data Model

function q_portdescRefCurDS return port_cv.portdesc_refcur is
   temp_portdesc port_cv.portdesc_refcur;
begin 
   open temp_portdesc for select department_id, department_name from departments; 
   return temp_portdesc;
end;

function q_containerRefCurDS return cont_cv.container_refcur is
temp_container cont_cv.container_refcur;
begin
    open temp_container for 
     select employee_id, 
         start_date,
         end_date,
         job_id,
         department_id
      from job_history;
    return temp_container;
end;

function q_conclassRefCurDS return concl_cv.conclass_refcur is
temp_concl concl_cv.conclass_refcur;
begin
  open temp_concl for
   select employee_id,
        first_name,
        last_name,
        e-mail,
        phone_number,
        hire_date,
        job_id,
        salary,
        department_id
   from employees;
  return temp_concl;
end;


nothing here any Close cursor statement. please help.
i am trying to explore this area of the development.

thanks in advance.
Re: Building Reports in Dev. Report 10g with Ref Cursor Query [message #537248 is a reply to message #537182] Tue, 27 December 2011 06:49 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Read this Ask Tom and this OTN Forums discussion; I hope you'll get the idea. Feel free to Google for more.
Re: Building Reports in Dev. Report 10g with Ref Cursor Query [message #537262 is a reply to message #537248] Tue, 27 December 2011 07:33 Go to previous message
oralover2006
Messages: 144
Registered: January 2010
Location: India
Senior Member
Thanks much Littlefoot for reply and help.
Previous Topic: Reports gets closed
Next Topic: Report Server closes automatically
Goto Forum:
  


Current Time: Fri Mar 29 05:06:22 CDT 2024