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.