Returning a JDBC result set from an Oracle stored procedure

Returning a JDBC result set from an Oracle stored procedure

Returning a JDBC result set from an Oracle stored procedure

Bruce P. Blackshaw

Introduction

You would think that returning a JDBC result set from an Oracle PL/SQL stored procedure would be quite straightforward. Unfortunately, it's not as easy as it sounds. But reading this article should tell you all you need to know. Any contributions or corrections welcome - please email me.

There are three basic steps. First, you have to write the stored procedure - which in Oracle is a bit tricky if you're not familiar with PL/SQL. Testing it using the Oracle command-line tool sqlplus is also quirky. And calling it via JDBC isn't exactly standard either.

Writing the stored procedure

If you are used to Sybase or SQLServer, returning a result set from a stored procedure is easy. Just finish the procedure with a "select x,y,z from my_table", selecting whatever columns you wish to return.

Not so for Oracle. You need to use cursors.

A cursor is a work area for a SQL statement that returns one or more rows. It allows you to fetch rows from the result set one by one. Cursors aren't particularly difficult to use, but to return a result set from a PL/SQL stored procedure, you must use a cursor variable. Cursor variables are basically pointers to cursors, and you use them to pass around references to cursors, such as a parameter to a stored procedure. The PL/SQL type of a cursor variable is REF CURSOR.

To use cursor variables, you must define a REF CURSOR type. This type can be strong or weak. Strong types are tied to a specific set of a table's columns. This means a strong REF CURSOR type can only be used with queries (i.e. cursors) returning those columns. Weak types can refer to any cursor, and so are much more flexible. They do, however, sacrifice type safety.

The easiest way forward is to define a weakly typed REF CURSOR in a PL/SQL package. Packages are used in PL/SQL for partitioning functionality. Below, a weak REF CURSOR type called REF_CURSOR is defined in a PL/SQL package called types.

CREATE OR REPLACE PACKAGE types
AS
    TYPE ref_cursor IS REF CURSOR;
END;

This definition can now be used in all stored procedures to declare a variable of type REF CURSOR.

We can now write a stored procedure that returns a REF CURSOR, that in JDBC we can process as a result set.

Assume we start from a table defined as below.

CREATE TABLE STOCK_PRICES(
    RIC VARCHAR(6) PRIMARY KEY,
    PRICE NUMBER(7,2),
    UPDATED DATE )

Here we have a table of stock prices, with the RIC (Reuters Instrument Code) as the primary key.  We define a PL/SQL function that simply declares a cursor that returns all columns for stocks below a certain price.

CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER)
    RETURN types.ref_cursor
AS
    stock_cursor types.ref_cursor;
BEGIN
    OPEN stock_cursor FOR
        SELECT ric,price,updated FROM stock_prices
        WHERE price < v_price;

    RETURN stock_cursor;
END;

These can all be created via the sqlplus command-line tool. Download the SQL script, and start up sqlplus.

C:\>sqlplus username/password

Once in sqlplus, run the script. If you start up sqlplus in the directory the SQL script is in, you can run it using the @ command:

SQL>@stock_prices

You should get an encouraging list of messages such as "1 row created", "Package created", "Function created". If instead you get error messages such as "insufficient privileges", your user must be given additional database privileges. The predefined Oracle roles CONNECT and RESOURCE should be what you need. Login as the system user and run the below:

SQL>GRANT CONNECT, RESOURCE TO username;

Testing the stored procedure

To test the stored procedure, we can either execute it from within an environment that allows you to execute stored procedures and supply their parameters, such as SQL Navigator or Toad, or we can run it from within sqlplus.

Executing a stored procedure from within sqlplus is straightforward once you know how.

Firstly, start up sqlplus and declare a sqlplus variable of type refcursor. This declares a sqlplus variable of type refcursor (a sqlplus type), called "results":

SQL> var results refcursor

Next, execute the procedure, supplying a number as a parameter, and assigning the result into our variable, "results". Note the unusual syntax.

SQL> exec :results := sp_get_stocks(20.0)

PL/SQL procedure successfully completed.

Finally, use the sqlplus print statement to print out the result set

SQL> print results

RIC PRICE UPDATED
------ --------- ---------
AMZN 15.5 21-OCT-01
SUNW 16.25 21-OCT-01
ORCL 14.5 21-OCT-01

If this works successfully, similar to the above, the stored procedure (well, function) is working correctly.

Calling the stored procedure from Java

There are some Oracle-specific tricks to calling the above stored procedure from Java.

The query string can be in the Oracle format or the standard JDBC callable statement syntax. If you are using the Oracle syntax, the query string is:

begin ? := sp_get_stocks(?); end;

If you are using the JDBC syntax, the query string is:

{ call ? := sp_get_stocks(?) }

In each case the spacing is important - the query will not work otherwise.

Secondly, the out parameter must be registered to be of type OracleTypes.CURSOR.

And finally, use getObject() to retrieve the result set.

The code (without error handling) is illustrated below. A more complete code sample can be downloaded from here.

String query = "begin ? := sp_get_stocks(?); end;";

CallableStatement stmt = conn.prepareCall(query);

// register the type of the out param - an Oracle specific type
stmt.registerOutParameter(1, OracleTypes.CURSOR);

// set the in param
stmt.setFloat(2, price);

// execute and retrieve the result set
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);

// print the results
while (rs.next()) {
    System.out.println(rs.getString(1) + "\t" +
        rs.getFloat(2) + "\t" +
        rs.getDate(3).toString());
}

Hopefully, this code will be all you need to get started on calling your own Oracle stored procedures via JDBC.

References

1. Oracle JDBC FAQ
2. Oracle JDBC How-To Documents

참조 :
http://www.idevelopment.info/data/Programming/java/jdbc/PLSQL_and_JDBC/RefCursorExample.java