6 Using PL/SQL Stored Procedures and REF CURSORs

This chapter contains:

Introduction to PL/SQL Stored Procedures

A stored procedure is a named set of PL/SQL statements designed to perform an action. Stored procedures are stored inside the database. They define a programming interface for the database rather than allowing the client application to interact with database objects directly. Stored procedures are typically used for data validation or to encapsulate large, complex processing instructions that combine several SQL queries.

Stored functions have a single return value parameter. Unlike functions, procedures may or may not return values.

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 defines what is contained in the package; it is analogous to a header file in a language such as C++. The specification defines all public items. The specification is the published interface to a package.

  • The package body contains the code for the procedures and functions defined in the specification, and the code for private procedures and functions that are not declared in the specification. This private code is only visible within the package body.

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 REF CURSORs

Using REF CURSORs is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application.

A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a REF CURSOR is a pointer or a handle to a result set on the database. REF CURSORs are represented through the OracleRefCursor ODP.NET class.

REF CURSORs have the following characteristics:

  • A REF CURSOR refers to a memory address on the database. Therefore, the client must be connected to the database during the lifetime of the REF CURSOR in order to access it.

  • A REF CURSOR involves an additional database round-trip. While the REF CURSOR is returned to the client, the actual data is not returned until the client opens the REF CURSOR and requests the data. Note that data is not be retrieved until the user attempts to read it.

  • 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.

  • 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.

  • A REF CURSOR is a PL/SQL data type. You create and return a REF CURSOR inside a PL/SQL code block.

Creating a PL/SQL Stored Procedure that Uses REF CURSORs

This section demonstrates how to create a PL/SQL stored procedure.

To create a stored procedure:

  1. Open Server Explorer and double-click HR to open the connection to the HR schema created in "Connecting to the Oracle Database".

    Description of connect_se.gif follows
    Description of the illustration connect_se.gif

    If you have not previously saved the password, the Oracle Server Login opens and you can enter the password now. If you have saved the password, then the connection expands immediately.

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

    Description of procedure1.gif follows
    Description of the illustration procedure1.gif

    The New Package window appears.

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

  4. Under the Methods area, click Add.

    Description of procedure2.gif follows
    Description of the illustration procedure2.gif

    The Add Method window appears.

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

  6. Under Parameters, click Add.

    This starts the process of adding parameters.

    Under the Parameter Details group on the right, enter these three parameters. Click Add before each parameter that you need to add.

    • Name: DEP_ID, Direction: select IN , Data Type: select NUMBER.

    • Name: EMPLOYEES_C, Direction: select OUT, Data Type: select SYS_REFCURSOR.

    • Name: DEPENDENTS_C , Direction: OUT, Data Type: select SYS_REFCURSOR.

    Description of procedure3.gif follows
    Description of the illustration procedure3.gif

  7. Click OK when you finish adding parameters.

    The New Package window reappears.

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

    A Preview SQL window appears, containing code similar to the following. Note that this code has been abbreviated by removing most of the comments.

    CREATE PACKAGE "HR"."HR_DATA" IS  -- Declare types, variables, constants, exceptions, cursors, 
      -- and subprograms that can be referenced from outside the package.
        "DEP_ID" IN NUMBER, 
    END "HR_DATA";
      -- Implement subprograms, initialize variables declared in package
      -- specification.
      -- Make private declarations of types and items, that are not accessible 
      -- outside the package
        "DEP_ID" IN NUMBER, 
      -- Declare constants and variables in this section.
         BEGIN -- executable part starts here
      -- EXCEPTION -- exception-handling part starts here
    END "HR_DATA"; 
  9. Click OK to close the Preview SQL window.

  10. In the New Package window, click OK to save the new package.

    The new package, HR_DATA, now appears in the Server Explorer.

  11. In the Server Explorer, right-click the package HR_DATA, and select Edit Package Body.

    Description of procedure4.gif follows
    Description of the illustration procedure4.gif

    The code for the package appears.

  12. Scroll down to the body of the GETCURSORS procedure, and after BEGIN, replace the line NULL; with the following code:

  13. Save the changes to the package.

  14. To run the stored procedure, in Server Explorer, expand the HR_DATA package.

    Right-click the GETCURSORS method, and select Run.

    Description of procedure5.gif follows
    Description of the illustration procedure5.gif

    The Run Procedure window appears.

  15. In the Run Procedure window, enter a Value of 60 for dep_id.

    Description of procedure6.gif follows
    Description of the illustration procedure6.gif

  16. Click OK.

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

    In the result window, the following message appears:

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

    Under this message, note two output parameters (together with DEP_ID): EMPLOYEES_C and DEPENDENTS_C.

  17. Select the Value column entry for EMPLOYEES_C.

    The Parameter Details area appears, showing the employees in department 60. The value for DEP_ID is 60.

    Description of procedure7.gif follows
    Description of the illustration procedure7.gif

  18. Select the Value column entry for DEPENDENTS_C.

    The Parameter Details area appears, showing the value of the DEPENDENTS_C.

    Description of procedure8.gif follows
    Description of the illustration procedure8.gif

Modifying an ODP.NET Application to Run Stored Procedures

This section demonstrates how to modify your Oracle Data Provider for .NET application to run a PL/SQL stored procedure, using the GETCURSORS stored procedure as a sample.

To modify your application to run a stored procedure:

  1. Open the application HR_Connect_CS or HR_Connect_VB.

  2. Make a copy of Form3.xx, which you finished at the end of Chapter 4 and name it Form4.xx, following the instructions in Appendix B, "Copying a Form".

  3. With Form1 selected, switch to code view.

  4. In the try block of the connect_Click() method, replace the two command assignment lines, starting with cmd = New OracleCommand... with the code indicated.

    Visual C#:

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

    Visual Basic:

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

    Visual C#:

    OracleParameter dep_id = new OracleParameter();
    dep_id.OracleDbType = OracleDbType.Decimal;
    dep_id.Direction = ParameterDirection.Input;
    dep_id.Value = 60;
    OracleParameter employees_c = new OracleParameter();
    employees_c.OracleDbType = OracleDbType.RefCursor;
    employees_c.Direction = ParameterDirection.Output;
    OracleParameter dependents_c = new OracleParameter();
    dependents_c.OracleDbType = OracleDbType.RefCursor;
    dependents_c.Direction = ParameterDirection.Output;

    Visual Basic:

    Dim dep_id As OracleParameter = New OracleParameter
    dep_id.OracleDbType = OracleDbType.Decimal
    dep_id.Direction = ParameterDirection.Input
    dep_id.Value = 60
    Dim employees_c As OracleParameter = New OracleParameter
    employees_c.OracleDbType = OracleDbType.RefCursor
    employees_c.Direction = ParameterDirection.Output
    Dim dependents_c As OracleParameter = New OracleParameter
    dependents_c.OracleDbType = OracleDbType.RefCursor
    dependents_c.Direction = ParameterDirection.Output
  6. Build the application.

Running a PL/SQL Stored Procedure Using an ODP.NET Application

This section demonstrates how to run a PL/SQL stored procedure, such as the GETCURSORS stored procedure, from your ODP application.

To run a stored procedure:

  1. Run the application.

    A Form1 window appears.

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

  3. In the DataGrid object, scroll horizontally to verify that the values in the last column, DEPARTMENT_ID are only 60.

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

    Description of procedure9.gif follows
    Description of the illustration procedure9.gif

  4. Close the application.