Skip Headers
Oracle® Database Express Edition 2 Day Plus .NET Developer Guide
10g Release 2 (10.2)

Part Number B25312-01
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
View PDF

6 Using PL/SQL Stored Procedures and REF Cursors

This chapter contains the following sections:

Introduction to PL/SQL Packages and Package Bodies

A PL/SQL package stores related items as a single logical entity. A package is composed of two distinct pieces:

The package specification and body are stored as separate objects in the data dictionary and can be seen in the user_source view. The specification is stored as the PACKAGE type, and the body is stored as the PACKAGE BODY type.

While it is possible to have a specification without a body, as when declaring a set of public constants, it is not possible to have a body with no specification.

Introduction to PL/SQL Stored Procedures

A stored procedure is a named set of PL/SQL statements designed to perform an action. Stored functions have a single return value parameter. Unlike functions, procedures may or may not return values.

Introduction to Ref Cursors

Ref cursors are one of the most powerful, flexible, and scalable methods for returning query results from an Oracle Database to a client application.

A ref cursor is a PL/SQL datatype whose value is the address of the memory location of a query work area on the database server. A query work area can be thought of as the result set, or a row set, on the server; it is the location where the results of a query are stored in server memory. In essence, a ref cursor is a handle to a result set on the server. A ref cursor is represented through the OracleRefCursor ODP.NET class.

Ref cursors have the following characteristics:

  1. A ref cursor refers to server memory. The memory address represented by a ref cursor "lives" on the database server, not on the client machine. Therefore, the client's connection to the database must be in place during the lifetime of the ref cursor. If the underlying connection to the database is closed, the ref cursor will become inaccessible to the client.

  2. A ref cursor involves an additional database round trip. Because a ref cursor is a pointer to memory on the server that is returned to the client, the actual data contained in the ref cursor is not initially returned to the client. The client must request the data contained in the ref cursor after it has opened the ref cursor. Note that data will not be retrieved until the user attempts to read it.

  3. A ref cursor is not updatable. The result set represented by the ref cursor is read-only. You cannot update the database by using a ref cursor.

  4. A ref cursor is not backward scrollable. The data represented by the ref cursor is accessed in a forward-only, serial manner. You cannot position a record pointer inside the ref cursor to point to random records in the result set.

  5. A ref cursor is a PL/SQL datatype. You create and return a ref cursor inside a PL/SQL code block.

Creating a PL/SQL Stored Procedure that Uses Ref Cursors

  1. Follow the instructions in Section "Copying a Project" to create a new copy of the HR_DataSet_ODP_CS project. Name the new project HR_StoredProcedure_CS. If using VB, name it HR_StoredProcedure_VB.

  2. In Oracle Explorer, right-click Packages and select New Package.

    Description of stored01.gif follows
    Description of the illustration stored01.gif

    The New Package window appears.

  3. In the New Package window, change the Package Name to HR_DATA.

  4. Click Add.

    Description of stored02.gif follows
    Description of the illustration stored02.gif

    The Add Method window appears.

  5. In the Add Method window, enter Method Name GETCURSORS, and change Method Type to Procedure.

    Description of stored03.gif follows
    Description of the illustration stored03.gif

  6. Under Parameters, click Add.

    In the Add Method window, under Parameter Details, enter DEPARTMENTID for Name, IN for Direction, and NUMBER for Data Type.

    Description of stored04.gif follows
    Description of the illustration stored04.gif

  7. Under Parameters, click Add.

    Enter a second parameter under Parameter Details, with EMPLOYEESCUR for Name, OUT for Direction, and SYS_REFCURSOR for Data Type.

  8. Under Parameters, click Add.

    Enter a third parameter under Parameter Details, with DEPENDENTSCUR for Name, OUT for Direction, and SYS_REFCURSOR for Data Type.

  9. Click OK.

    The New Package window appears.

  10. In the New Package window, click Preview SQL to see the SQL code created.

    A Preview SQL window appears, containing code in Example 6-1. Note that this example has been abbreviated by removing most of the comments.

    Example 6-1 PL/SQL Code for Package HR_DATA

    CREATE PACKAGE "HR"."HR_DATA" IS  -- Declare types, variables, constants, exceptions, cursors, 
      -- and subprograms that can be referenced from outside the package.
    
      PROCEDURE "GETCURSORS" (
        "DEPARTMENTID" IN NUMBER, 
        "EMPLOYEESCUR" OUT SYS_REFCURSOR, 
        "DEPENDENTSCUR" OUT SYS_REFCURSOR);
    
    END "HR_DATA";
    
    CREATE PACKAGE BODY "HR"."HR_DATA" IS
    
      -- Implement subprograms, initialize variables declared in package
      -- specification.
    
      -- Make private declarations of types and items, that are not accessible 
      -- outside the package
       PROCEDURE "GETCURSORS" (
        "DEPARTMENTID" IN NUMBER, 
        "EMPLOYEESCUR" OUT SYS_REFCURSOR, 
        "DEPENDENTSCUR" OUT SYS_REFCURSOR) IS
    
      -- Declare constants and variables in this section.
    
         BEGIN -- executable part starts here
    
         NULL;
    
      -- EXCEPTION -- exception-handling part starts here
     
        END "GETCURSORS";
    
    END "HR_DATA"; 
    
    

    Click OK to close the window.

  11. In the New Package window, click OK.

    Note that a new package, HR_DATA, now appears in Oracle Explorer.

  12. In Oracle Explorer, right-click package HR_DATA, and select Edit Package Body.

    Description of stored05.gif follows
    Description of the illustration stored05.gif

    The code for the package is displayed.

  13. Scroll down to the body of the GETCURSORS procedure, and replace NULL; with code in Example 6-2:

    Example 6-2 Assigning Reference Cursors

    OPEN EMPLOYEESCUR FOR SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=DEPARTMENTID;
    OPEN DEPENDENTSCUR FOR SELECT * FROM DEPENDENTS;
    
    
    Description of stored06.gif follows
    Description of the illustration stored06.gif

  14. Save the changes to the package; use the Ctrl+S keyboard shortcut.

  15. To run the stored procedure, in Oracle Explorer, expand package HR_DATA.

    Right-click the GETCURSORS method, and select Run.

    Description of stored07.gif follows
    Description of the illustration stored07.gif

    A Run Procedure window appears.

  16. In the Run Procedure window, enter a Value of 60 for DEPARTMENTID. Click OK.

    Description of stored08.gif follows
    Description of the illustration stored08.gif

  17. The Output window appears, showing that the run was successful. Close the Output Window.

  18. In the design view, the following message appears:

    Procedure <HR.HR_DATA.GETCURSORS@hr.database> was run successfully.
    
    

    Under this message, note two new parameters (together with DEPARTENTID): EMPLOYEESCUR and DEPENDENTSCUR.

    Description of stored09.gif follows
    Description of the illustration stored09.gif

  19. In the design view, select the Value column entry for EMPLOYEESCUR.

    The Parameter Details area appears, showing the result of the EMPLOYEESCUR for DEPARTMENTID 60.

    Description of Stored10.gif follows
    Description of the illustration Stored10.gif

  20. In the design view, select the Value column entry for DEPENDENTSCUR.

    The Parameter Details area appears, showing the result of the DEPENDENTSCUR.

    Description of Stored11.gif follows
    Description of the illustration Stored11.gif

Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET

  1. With Form1 selected, switch to code view using the F7 keyboard shortcut.

  2. In the try block of the connect_Click() method, replace the two command assignment lines, starting with cmd = New OracleCommand... with code in Example 6-3 or Example 6-4.

    Example 6-3 Changing OracleCommand to Use a Stored Procedure: C#

    cmd = new OracleCommand("HR_DATA.GETCURSORS", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    

    Example 6-4 Changing OracleCommand to Use a Stored Procedure: VB

    cmd = new OracleCommand("HR_DATA.GETCURSORS", conn)
    cmd.CommandType = CommandType.StoredProcedure
    
    
  3. Under the code added in Step 2, add definitions and bindings for the three parameters of the GETCURSORS stored procedure as OracleParameter objects, calling them dept_id, emp_cur and dnt_cur.

    Example 6-5 Defining and Binding OracleParameter Objects for Stored Procedure: C#

    OracleParameter dept_id = new OracleParameter();
    dept_id.OracleDbType = OracleDbType.Decimal;
    dept_id.Direction = ParameterDirection.Input;
    dept_id.Value = 60;
    cmd.Parameters.Add(dept_id);
    
    OracleParameter emp_cur = new OracleParameter();
    emp_cur.OracleDbType = OracleDbType.RefCursor;
    emp_cur.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(emp_cur);
    
    OracleParameter dnt_cur = new OracleParameter();
    dnt_cur.OracleDbType = OracleDbType.RefCursor;
    dnt_cur.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(dnt_cur);
    

    Example 6-6 Defining and Binding OracleParameter Objects for Stored Procedure: VB

    Dim dept_id As OracleParameter = New OracleParameter
    dept_id.OracleDbType = OracleDbType.Decimal
    dept_id.Direction = ParameterDirection.Input
    dept_id.Value = 60
    cmd.Parameters.Add(dept_id)
    
    Dim emp_cur As OracleParameter = New OracleParameter
    emp_cur.OracleDbType = OracleDbType.RefCursor
    emp_cur.Direction = ParameterDirection.Output
    cmd.Parameters.Add(emp_cur)
    
    Dim dnt_cur As OracleParameter = New OracleParameter
    dnt_cur.OracleDbType = OracleDbType.RefCursor
    dnt_cur.Direction = ParameterDirection.Output
    cmd.Parameters.Add(dnt_cur)
    
    
  4. Run the application using the F7 keyboard shortcut.

    A Form1 window appears.

  5. In the Form1 window, enter the connection information, and click Connect.

  6. In the DataGrid object, scroll horizontally to note that the last column, DEPARTMENT_ID, is equal to 60.

    Note that the DataGrid contains the first result set from the stored procedure, which matches the query of the EMPLOYEES table.

    Description of Stored13.gif follows
    Description of the illustration Stored13.gif

  7. Close the application.