Skip Headers
Oracle® Fusion Middleware Oracle Reports User's Guide to Building Reports
11g Release 1 (11.1.1)

Part Number B32122-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

41.2 Define a REF CURSOR Type

To create a REF CURSOR query, you first create a package spec that defines the REF CURSOR. Then you create a query that uses the REF CURSOR. The steps in this section will help you create package specs that define REF CURSORs.

To define a REF CURSOR type:

  1. Launch Reports Builder (or, if already open, choose File > New > Report).

  2. In the Welcome or New Report dialog box, select Build a new report manually, then click OK.

  3. In the Object Navigator, click the Program Units node under the new report node (MODULEx).

  4. Click the Create button in the toolbar to display the New Program Unit dialog box.

  5. In the New Program Unit dialog box, type concl_cv in the Name field.

  6. Select Package Spec, then click OK to display the PL/SQL Editor.

  7. In the PL/SQL Editor, use the template to enter the following PL/SQL code:

    PACKAGE concl_cv IS
        type conclass_rec is RECORD 
           (EMPLOYEE_ID NUMBER(6),
         FIRST_NAME VARCHAR2(20),
         LAST_NAME VARCHAR2(25),
         EMAIL 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;
    

    This package spec does two things:

    • defines a record (conclass_rec) that describes the data you want to select from the database.

    • defines a REF CURSOR that returns the data in the format described by the record.

      Note:

      You can open the provided file refcursor_code.txt to copy and paste the code into the PL/SQL Editor.

  8. Click Compile.

  9. If any compilation errors occur, check the code for syntax errors and recompile as needed.

  10. Click Close.

  11. Repeat steps 3 through 8 to create two more package specs:

    • Package Spec Name: cont_cv

      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 Spec Name: port_cv

      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;
      

      Note:

      You can open the provided file refcursor_code.txt to copy and paste the code into the PL/SQL Editor.

  12. Choose File > Save As. Save the report in the directory of your choice, and name the report ref61_your_initials.rdf.

    Note:

    It is good practice when you are designing your report to save it frequently under a different file name. If you generate an error or if you do not like some of the changes you made, you easily can go back to the previously saved file and make revisions from that point.