Oracle8i Java Stored Procedures Developer's Guide Release 2 (8.1.6) Part Number A81358-01 |
Library | Service | Contents | Index

## Calling PL/SQL from Java

JDBC and SQLJ allow you to call PL/SQL stored functions and procedures. For example, suppose you want to call the following stored function, which returns the balance of a specified bank account:

    FUNCTION balance (acct_id NUMBER) RETURN NUMBER IS
      acct_bal NUMBER;
    BEGIN
      SELECT bal INTO acct_bal FROM accts
        WHERE acct_no = acct_id;
      RETURN acct_bal;
    END;

From a JDBC program, your call to the function `balance` might look like this:

    CallableStatement cstmt = conn.prepareCall("{? = CALL balance(?)}");
    cstmt.registerOutParameter(1, Types.FLOAT);
    cstmt.setInt(2, acctNo);
    cstmt.executeUpdate();
    float acctBal = cstmt.getFloat(1);

From a SQLJ program, the call might look like this:

    #sql acctBal = {VALUES(balance(:IN acctNo))};

To learn more about JDBC, see the Oracle8i JDBC Developer's Guide and Reference. To learn more about SQLJ, see the Oracle8i SQLJ Developer's Guide and Reference.

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