# Stored Procedures

The following topic explains how to call and create stored procedures using a database control file.

## Calling Stored Procedures with IN Parameters

If the stored procedure contains only IN parameters, you can call the procedure by passing method parameters to the procedure.

Assume the following procedure sp_updateData has been created on the database.

         CREATE OR REPLACE PROCEDURE sp_updateData
           (pkID   IN SMALLINT,
           intVal  IN INT)
         AS
         BEGIN
           UPDATE CUSTOMER
           SET NAME = intVal
           WHERE CUSTID = pkID;
         END sp_updateData;

The following database control method calls the procedure sp_updateData and passes two method parameters to the procedure.

        /**
         * @jc:sql statement="call sp_updateData({keyVal}, {intVal})"
         */
        void call_sp_updateCust(short keyVal, int intVal);

Note that the method parameters are substituted into the procedure call using the curly brace substition syntax.

Before the procedure call is passed to the database, WebLogic Workshop constructs a complete statement from the static elements and the substituted values. Assuming that keyVal and intVal have the values 1 and 2 respectively, the complete statement sent to the database is

        call sp_updateData(1, 2)

Note: If you are calling this stored procedure against a Sybase database, you must include curly braces around the stored procedure call. For Sybase, the annotation value should look like this:

        /**
         * @jc:sql statement="{call sp_updateData({keyVal}, {intVal})}"
         */

## Calling Stored Procedures with OUT Parameters

To call a procedure that contains OUT parameters: (1) use a SQLParameter Array as the parameter of the Java method that calls the procedure and (2) use question marks as placeholders for the parameters within the procedure call.

For example, assume that the following procedure sp_squareInt exists on the database.

         CREATE OR REPLACE PROCEDURE sp_squareInt
            (field1 IN INTEGER, field2 OUT INTEGER) IS
         BEGIN
             field2 := field1 * field1;
         END sp_squareInt;

The following Java method will call the procedure sp_squareInt.

        /**
         * @jc:sql statement="{call sp_squareInt(?, ?)}"
         */
        void call_sp_squareInt(SQLParameter[] params) throws SQLException;

Note that the method parameter params is not explicitly substituted into the procedure call {call sp_squareInt(?, ?)}. The substitution syntax {call ...} has special meaning within the jc:sql statement annotation. When WebLogic Workshop encounters the substitution syntax {call myStoredProc(?,?,?...)}, it automatically distributes the elements of params into the procedure call.

The following shows how to construct an SQLParameter[] to call the procedure sp_squareInt.

            // Construct a SQLParameter[]
            // to hold two SQLParameter objects
            SQLParameter[] params = new SQLParameter[2];

            // Construct two objects corresponding to the initial values of the
            // stored procedure's two parameters.
            Object obj0 = new Integer(x);
            Object obj1 = new Integer(0);

            // The stored procedure sp_squareInt has two parameters:
            // an IN parameter of data type INTEGER
            // and an OUT parameter of data type INTEGER.
            // params[0] is build to correspond to the IN parameter,
            // params[1] is build to correspond to the OUT parameter.
            params[0] = new SQLParameter(obj0, Types.INTEGER, SQLParameter.IN);
            params[1] = new SQLParameter(obj1, Types.INTEGER, SQLParameter.OUT);

            // Call the stored procedure.
            // Note that the procedure does not return any value.
            // Instead the result of the procedure is loaded directly into the OUT parameter,
            // and, in turn, into params[1].
            myDBControlFile.call_sp_squareInt(params);

            // Get the result loaded directly into params[1].
            return Integer.parseInt(params[1].value.toString());

Note that database control method call_sp_squareInt does not return the result of the procedure call. Instead the result of the procedure is loaded directly into the procedure's OUT parameter, and this in turn is loaded directly into the corresponding SQLParameter object. To get the result of the procedure, examine the .value property of the of the SQLParameter object.

        params[1].value

## Wrapping Procedures in Functions

An alternative to calling stored procedures directly is to wrap them in stored functions, then call the wrapping function from your database control file.

For example the following database control method will create a function that wraps the procedure sp_squareInt.

        /**
         * Wraps a procedure in a function.
         *
         * @jc:sql statement::
            CREATE OR REPLACE FUNCTION wrapProc
              (p1 INTEGER)
              RETURN INTEGER IS p2 INTEGER;
              BEGIN
                  -- Call the stored procedure sp_squareInt. p2 corresponds to sp_squareInt's OUT parameter.
                  sp_squareInt(p1, p2);
                  RETURN p2;
              END;
         * ::
         */
        public void create_wrapProc();

Once the procedure has been wrapped, you can call the function, instead of calling the procedure directly.

        /**
         * @jc:sql statement::
         * SELECT wrapProc({x}) FROM DUAL
         * ::
         */
        public int callWrapProc(int x, int y);

## Creating Strored Procedures

You can also send any DDL statement to the database through a database control method.

        /**
         * A stored procedure that takes an integer, squares it, and loads
         * the result into an OUT parameter.
         *
         * @jc:sql statement::
         CREATE OR REPLACE PROCEDURE sp_squareInt
            (field1 IN INTEGER, field2 OUT INTEGER) IS
         BEGIN
             field2 := field1 * field1;
         END sp_squareInt;
         ::
         */
        void create_sp_squareInt() throws SQLException;

Some XA database drivers contain restrictions on code that rollsback or commits a transaction independently of the driver's transaction management. Since DDL statements are implicitly transactional (COMMIT is called whether it or not it explicitly appears in the DDL statement), you may have to suspend the transaction with these XA drivers. For example if you send a DDL statement using the Oracle XA thin client without suspending the transaction, the driver throws the following exception.

        ORA-02089: COMMIT is not allowed in a subordinate session

The following code suspends the transaction, executes the DDL statement, and then resumes the transaction.

            import javax.transaction.Transaction;
            import weblogic.transaction.TransactionManager;
            import weblogic.transaction.TxHelper;

    		TransactionManager tm = TxHelper.getTransactionManager();
            Transaction saveTx = null;
            try
            {

                // Suspend the transaction
                saveTx = tm.forceSuspend();

                // Execute the DDL statement
                myDBControlFile.create_sp_squareInt();
            }
            finally
            {

                // Resume the transaction
                tm.forceResume(saveTx);
            }

Related Topics

Parameter Substitution in @jc:sql Statements

Stored Functions

@jc:sql Annotation