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.

---