41.3 Create a REF CURSOR Query

After creating package specs that define the REF CURSORs, you are ready to define the queries, as described in this section.

To create a REF CURSOR query:

  1. In the Object Navigator, double-click the view icon next to the Data Model node to display the Data Model view.

  2. In the Data Model view, click the Ref Cursor Query tool in the tool palette.

  3. Click in an open area of the Data Model view to display the PL/SQL Editor for the new REF CURSOR query.

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

    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;
    

    Note:

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

  5. Click Compile.

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

  7. Click Close.

  8. In the Data Model view, double-click the new REF CURSOR query object (QR_1) to display the Property Inspector.

    • Under General Information, set the Name property to q_portdesc.

      Tip:

      It is usually a good idea to give objects meaningful names, particularly when building a report with many objects. Later when building the layout, it is helpful to have queries and groups with meaningful names.

  9. Repeat the steps above to create two more queries:

    • Query name: q_container

      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;
      
    • Query name: q_conclass

      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,
              email,
              phone_number,
              hire_date,
              job_id,
              salary,
              department_id
         from employees;
        return temp_concl;
      end;
      

      Note:

      You can open the provided file refcursor_code.txt to copy and paste the code into Reports Builder.

    The Data Model should look similar to the following:

    Figure 41-3 Data model with three queries

    Description of Figure 41-3 follows
    Description of "Figure 41-3 Data model with three queries"

  10. Save the report as ref_62_your_initials.rdf.

    Note:

    You can open the provided file ref_emp62.rdf and display the Data Model to compare your results.