Stored Functions

This topic explains how to call and create stored functions using a database control file.

Calling Stored Functions

To call a stored function, place the function call in a @jc:sql statement annotation. When the Java method callMyFunction is called the SQL statement in the @jc:sql statement annotation is passed to the database. Any data returned by the SQL statement is passed back to, and returned by, the Java method.

    /**
     * @jc:sql statement="SELECT my_function FROM DUAL"
     */
    int callMyFunction() throws SQLException;

In most cases, WebLogic Workshop automatically converts between the appropriate database data types to the Java data types. For example, if the database function my_function returns the database type INTEGER, the Java method callMyFunction() will automatically convert it into the Java type int. Standard datatype mappings are described in detail in the help topic Mapping Database Field Types to Java Types in the Database Control.

You can substitute values dynamically into the database function call using curly braces. The following method passes the parameter int x to the function call.


Before the function call is passed to the database, WebLogic Workshop constructs a complete function call from the static elements and the substituted value. Assuming that the parameter x has the value 3, the following function call is constructed and passed to the database.

    SELECT my_function(3) FROM DUAL

Creating Stored Functions

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

    /**
     * A stored function that takes an integer, squares it, and returns the 
     * result through the database control method.
     * 
     * @jc:sql statement::
     CREATE OR REPLACE FUNCTION fn_squareInt 
        (field1 IN INTEGER)
        RETURN INTEGER IS field2 INTEGER; 
     BEGIN 
         field2 := field1 * field1;
         RETURN field2;
     END fn_squareInt; 
     ::
     */
    void create_fn_squareInt() throws SQLException;

Some XA database drivers contain restrictions on code that rolls back 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_fn_squareInt();
        } 
        finally 
        {

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

Related Topics

Parameter Substitution in @jc:sql Statements

Stored Procedures

@jc:sql Annotation