Defining an External Procedure

External Procedure

The CREATE PROCEDURE statement for an external procedure:

* Names the procedure
* Defines the parameters and their attributes
* Gives other information about the procedure which the system uses when it calls the procedure.

Consider the following example:

    CREATE PROCEDURE P1
     (INOUT PARM1 CHAR(10))
     EXTERNAL NAME MYLIB.PROC1
     LANGUAGE C
     GENERAL WITH NULLS

This CREATE PROCEDURE statement:

* Names the procedure P1
* Defines one parameter which is used both as an input parameter and an output parameter. The parameter is a character field of length ten. Parameters can be defined to be type IN, OUT, or INOUT. The parameter type determines when the values for the parameters get passed to and from the procedure.
* Defines the name of the program which corresponds to the procedure, which is PROC1 in MYLIB. MYLIB.PROC1 is the program which is called when the procedure is called on a CALL statement.
* Indicates that the procedure P1 (program MYLIB.PROC1) is written in C. The language is important since it impacts the types of parameters that can be passed. It also affects how the parameters are passed to the procedure (for example, for ILE C procedures, a NUL-terminator is passed on character, graphic, date, time, and timestamp parameters).
* Defines the CALL type to be GENERAL WITH NULLS. This indicates that the parameter for the procedure can possibly contain the NULL value, and therefore will like an additional argument passed to the procedure on the CALL statement. The additional argument is an array of N short integers, where N is the number of parameters that are declared in the CREATE PROCEDURE statement. In this example, the array contains only one element since there is only parameter.

It is important to note that it is not necessary to define a procedure in order to call it. However, if no procedure definition is found, either from a prior CREATE PROCEDURE or from a DECLARE PROCEDURE in this program, certain restrictions and assumptions are made when the procedure is called on the CALL statement. For example, the NULL indicator argument cannot be passed. See Using embedded CALL statement where no procedure definition exists for an example of a CALL statement without a corresponding procedure definition.

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.