Oracle8i SQLJ Developer's Guide and Reference Release 3 (8.1.7) Part Number A83723-01 |
Library | Service | Contents | Index

## Stored Procedure and Function Calls

SQLJ provides convenient syntax for calling stored procedures and stored functions in the database, as described immediately below. These procedures and functions could be written in Java, PL/SQL (in an Oracle database), or any other language supported by the database.

A stored function requires a result expression in your SQLJ executable statement to accept the return value and can optionally take input, output, or input-output parameters as well.

A stored procedure does not have a return value but can optionally take input, output, or input-output parameters. A stored procedure can return output through any output or input-output parameter.

Note: Remember that instead of using the following procedure-call and function-call syntax, you can optionally use JPublisher to create Java wrappers for PL/SQL stored procedures and functions, then call the Java wrappers as you would any other Java methods. JPublisher is discussed in "JPublisher and the Creation of Custom Java Classes". For additional information, see the Oracle8i JPublisher User's Guide.

### Calling Stored Procedures

Stored procedures do not have a return value but can take a list with input, output, and input-output parameters. Stored procedure calls use the `CALL` token, as shown below. The word "CALL" is followed by white space and then the procedure name. There must be a space after the `CALL` token to differentiate it from the procedure name. There cannot be a set of outer parentheses around the procedure call (this differs from the syntax for function calls, as explained in "Calling Stored Functions").

    #sql { CALL PROC(<PARAM_LIST>) };

`PROC` is the name of the stored procedure, which can optionally take a list of input, output, and input-output parameters. `PROC` can include a schema or package name as well, such as `SCOTT.MYPROC()`.

Presume that you have defined the following PL/SQL stored procedure:

    CREATE OR REPLACE PROCEDURE MAX_DEADLINE (deadline OUT DATE) IS
       BEGIN
          SELECT MAX(start_date + duration) INTO deadline FROM projects;
       END;

This reads the table `PROJECTS`, looks at the `START_DATE` and `DURATION` columns, calculates `start_date + duration` in each row, then takes the maximum `START_DATE + DURATION` total and selects it into `DEADLINE`, which is an output parameter of type `DATE`.

In SQLJ, you can call this `MAX_DEADLINE` procedure as follows:

    java.sql.Date maxDeadline;
    ...
    #sql { CALL MAX_DEADLINE(:out maxDeadline) };

For any parameters, you must use the host expression tokens `IN` (optional/default), `OUT`, and `INOUT` appropriately to match the input, output, and input-output designations of the stored procedure. Additionally, the types of the host variables you use in the parameter list must be compatible with the parameter types of the stored procedure.

Note: If you want your application to be compatible with Oracle7, do not include empty parentheses for the parameter list if the procedure takes no parameters. For example: `#sql { CALL MAX_DEADLINE };` not: `#sql { CALL MAX_DEADLINE() };`

### Calling Stored Functions

Stored functions have a return value and can also take a list of input, output, and input-output parameters. Stored function calls use the `VALUES` token, as shown below. This syntax consists of the word "VALUES" followed by the function call. In standard SQLJ, the function call must be enclosed in a set of outer parentheses, as shown. In Oracle SQLJ, the outer parentheses are optional. When using the outer parentheses, it does not matter if there is white space between the `VALUES` token and the begin-parenthesis. (A `VALUES` token can also be used in `INSERT INTO` `table` `VALUES` syntax supported by Oracle SQL, but these situations are unrelated semantically and syntactically.)

    #sql result = { VALUES(FUNC(<PARAM_LIST>)) };

Where `result` is the result expression, which takes the function return value. `FUNC` is the name of the stored function, which can optionally take a list of input, output, and input-output parameters. `FUNC` can include a schema or package name, such as `SCOTT.MYFUNC()`.

Referring back to the example in "Calling Stored Procedures", consider defining the stored procedure as a stored function instead, as follows:

    CREATE OR REPLACE FUNCTION GET_MAX_DEADLINE() RETURN DATE IS
       DECLARE
          DATE deadline;
       BEGIN
          SELECT MAX(start_date + duration) INTO deadline FROM projects;
          RETURN deadline;
       END;

In SQLJ, you can call this `GET_MAX_DEADLINE` function as follows:

    java.sql.Date maxDeadline;
    ...
    #sql maxDeadline = { VALUES(GET_MAX_DEADLINE) };

The result expression must have a type compatible with the return type of the function.

In Oracle SQLJ, the following syntax (outer parentheses omitted) is also allowed:

    #sql maxDeadline = { VALUES GET_MAX_DEADLINE };

For stored function calls, as with stored procedures, you must use the host expression tokens `IN` (optional--default), `OUT`, and `INOUT` appropriately to match the input, output, and input-output parameters of the stored function. Additionally, the types of the host variables you use in the parameter list must be compatible with the parameter types of the stored function.

Notes: If you want your stored function to be portable to non-Oracle environments, then you should use only input parameters in the calling sequence, not output or input-output parameters. If you want your application to be compatible with Oracle7, then do not include empty parentheses for the parameter list if the function takes no parameters. For example: `#sql maxDeadline = { VALUES(GET_MAX_DEADLINE) };` not: `#sql maxDeadline = { VALUES(GET_MAX_DEADLINE()) };`

### Using Iterators and Result Sets as Stored Function Returns

SQLJ supports assigning the return value of a stored function to an iterator or result set variable, provided that the function returns a `REF CURSOR` type.

The following example uses an iterator to take a stored function return. Using a result set is similar.

##### Example: Iterator as Stored Function Return

This example uses an iterator as a return type for a stored function, using a `REF CURSOR` type in the process. (`REF CURSOR` types are described in "Support for Oracle REF CURSOR Types".)

Presume the following function definition:

    CREATE OR REPLACE PACKAGE sqlj_refcursor AS
       TYPE EMP_CURTYPE IS REF CURSOR;
       FUNCTION job_listing (j varchar2) RETURN EMP_CURTYPE;
    END sqlj_refcursor;

    CREATE OR REPLACE PACKAGE BODY sqlj_refcursor AS
       FUNCTION job_listing (j varchar) RETURN EMP_CURTYPE IS
       DECLARE
          rc EMP_CURTYPE;
       BEGIN
          OPEN rc FOR SELECT ename, empno FROM emp WHERE job = j;
          RETURN rc;
       END;
    END sqlj_refcursor;

Use this function as follows.

Declaration:

    #sql public <static> iterator EmpIter (String ename, int empno);

(The `public` modifier is required, and `static` may be advisable if your declaration is at class level or nested-class level.)

Executable code:

    EmpIter iter;
    ...
    #sql iter = { VALUES(sqlj_refcursor.job_listing('SALES')) };

    while (iter.next())
    {
       String empname = iter.ename();
       int empnum = iter.empno();

       ... process empname and empnum ...
    }
    iter.close();
    ...

This example calls the `job_listing()` function to return an iterator that contains the name and employee number of each employee whose job title is "SALES". It then retrieves this data from the iterator.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index