Oracle® Application Development Framework Developer's Guide For Forms/4GL Developers
10g Release 3 (10.1.3.0) B25947-01 |
|
Previous |
Next |
You can write code in the custom Java classes for your business components to invoke database stored procedures and functions. Here you'll consider some simple examples based on procedures and functions in a PL/SQL package; however, using the same techniques, you also can invoke procedures and functions that are not part of a package.
Consider the following PL/SQL package:
create or replace package devguidepkg as procedure proc_with_no_args; procedure proc_with_three_args(n number, d date, v varchar2); function func_with_three_args(n number, d date, v varchar2) return varchar2; procedure proc_with_out_args(n number, d out date, v in out varchar2); end devguidepkg;
The following sections explain how to invoke each of the example procedures and functions in this package.
Note: The examples in this section refer to theStoredProcedureInvocation project in the AdvancedExamples workspace. See the note at the beginning of this chapter for download instructions.
|
If you need to invoke a stored procedure that takes no arguments, you can use the executeCommand()
method on the DBTransaction
interface (in the oracle.jbo.server
package as shown in Example 25-6.
Invoking stored procedures that accept only IN
-mode arguments — which is the default PL/SQL parameter mode if not specified — requires using a JDBC PreparedStatement
object. The DBTransaction
interface provides a createPreparedStatement()
method to create this object for you in the context of the current database connection. You could use a helper method like the one shown in Example 25-7 to simplify the job of invoking a stored procedure of this kind using a PreparedStatement
. Importantly, by using a helper method, you can encapsulate the code that closes the JDBC PreparedStatement
after executing it. The code performs the following basic tasks:
Creates a JDBC PreparedStatement
for the statement passed in, wrapping it in a PL/SQL begin
...end
block.
Loops over values for the bind variables passed in, if any.
Sets the value of each bind variable in the statement.
Notice that since JDBC bind variable API's use one-based numbering, the code adds one to the zero-based for loop index variable to account for this.
Executes the statement.
Closes the statement.
Example 25-7 Helper Method to Simplify Invoking Stored Procedures with Only IN Arguments
protected void callStoredProcedure(String stmt, Object[] bindVars) { PreparedStatement st = null; try { // 1. Create a JDBC PreparedStatement for st = getDBTransaction().createPreparedStatement("begin "+stmt+";end;",0); if (bindVars != null) { // 2. Loop over values for the bind variables passed in, if any for (int z = 0; z < bindVars.length; z++) { // 3. Set the value of each bind variable in the statement st.setObject(z + 1, bindVars[z]); } } // 4. Execute the statement st.executeUpdate(); } catch (SQLException e) { throw new JboException(e); } finally { if (st != null) { try { // 5. Close the statement st.close(); } catch (SQLException e) {} } } }
With a helper method like this in place, calling the proc_with_three_args
procedure above would look like this:
// In StoredProcTestModuleImpl.java public void callProcWithThreeArgs(Number n, Date d, String v) { callStoredProcedure("devguidepkg.proc_with_three_args(?,?,?)", new Object[]{n,d,v}); }
Notice the question marks used as JDBC bind variable placeholders for the arguments passed to the function. JDBC also supports using named bind variables, but using these simpler positional bind variables is also fine since the helper method is just setting the bind variable values positionally.
Invoking stored functions that accept only IN
-mode arguments requires using a JDBC CallableStatement
object in order to access the value of the function result after executing the statement. The DBTransaction
interface provides a createCallableStatement()
method to create this object for you in the context of the current database connection. You could use a helper method like the one shown in Example 25-8 to simplify the job of invoking a stored function of this kind using a CallableStatement
. As above, the helper method encapsulates both the creation and clean up of the JDBC statement being used.
The code performs the following basic tasks:
Creates a JDBC CallableStatement
for the statement passed in, wrapping it in a PL/SQL begin
...end
block.
Registers the first bind variable for the function return value.
Loops over values for the bind variables passed in, if any.
Sets the value of each bind user-supplied bind variable in the statement.
Notice that since JDBC bind variable API's use one-based numbering, and since the function return value is already the first bind variable in the statement, the code adds two to the zero-based for loop index variable to account for these.
Executes the statement.
Returns the value of the first bind variable.
Closes the statement.
Example 25-8 Helper Method to Simplify Invoking Stored Functions with Only IN Arguments
// Some constants public static int NUMBER = Types.NUMERIC; public static int DATE = Types.DATE; public static int VARCHAR2 = Types.VARCHAR; protected Object callStoredFunction(int sqlReturnType, String stmt, Object[] bindVars) { CallableStatement st = null; try { // 1. Create a JDBC CallabledStatement st = getDBTransaction().createCallableStatement( "begin ? := "+stmt+";end;",0); // 2. Register the first bind variable for the return value st.registerOutParameter(1, sqlReturnType); if (bindVars != null) { // 3. Loop over values for the bind variables passed in, if any for (int z = 0; z < bindVars.length; z++) { // 4. Set the value of user-supplied bind vars in the stmt st.setObject(z + 2, bindVars[z]); } } // 5. Set the value of user-supplied bind vars in the stmt st.executeUpdate(); // 6. Return the value of the first bind variable return st.getObject(1); } catch (SQLException e) { throw new JboException(e); } finally { if (st != null) { try { // 7. Close the statement st.close(); } catch (SQLException e) {} } } }
With a helper method like this in place, calling the func_with_three_args
procedure above would look like this:
// In StoredProcTestModuleImpl.java public String callFuncWithThreeArgs(Number n, Date d, String v) { return (String)callStoredFunction(VARCHAR2, "devguidepkg.func_with_three_args(?,?,?)", new Object[]{n,d,v}); }
Notice the question marks as above that are used as JDBC bind variable placeholders for the arguments passed to the function. JDBC also supports using named bind variables, but using these simpler positional bind variables is also fine since the helper method is just setting the bind variable values positionally.
Calling a stored procedure or function like devguidepkg.proc_with_out_args
that includes arguments of OUT
or IN OUT
mode requires using a CallableStatement
as in the previous section, but is a little more challenging to generalize into a helper method. Example 25-9 illustrates the JDBC code necessary to invoke the devguidepkg.proc_with_out_args
procedure.
The code performs the following basic tasks:
Defines a PL/SQL block for the statement to invoke.
Creates the CallableStatement
for the PL/SQL block.
Registers the positions and types of the OUT
parameters.
Sets the bind values of the IN
parameters.
Executes the statement.
Creates a JavaBean to hold the multiple return values
The DateAndStringBean
class contains bean properties named dateVal
and stringVal
.
Sets the value of its dateVal
property using the first OUT
param.
Sets value of its stringVal
property using second OUT
param.
Returns the result.
Closes the JDBC CallableStatement
.
Example 25-9 Calling a Stored Procedure with Multiple OUT Arguments
public Date callProcWithOutArgs(Number n, String v) { CallableStatement st = null; try { // 1. Define the PL/SQL block for the statement to invoke String stmt = "begin devguidepkg.proc_with_out_args(?,?,?); end;"; // 2. Create the CallableStatement for the PL/SQL block st = getDBTransaction().createCallableStatement(stmt,0); // 3. Register the positions and types of the OUT parameters st.registerOutParameter(2,Types.DATE); st.registerOutParameter(3,Types.VARCHAR); // 4. Set the bind values of the IN parameters st.setObject(1,n); st.setObject(3,v); // 5. Execute the statement st.executeUpdate(); // 6. Create a bean to hold the multiple return values DateAndStringBean result = new DateAndStringBean(); // 7. Set value of dateValue property using first OUT param result.setDateVal(new Date(st.getDate(2))); // 8. Set value of stringValue property using 2nd OUT param result.setStringVal(st.getString(3)); // 9. Return the result return result; } catch (SQLException e) { throw new JboException(e); } finally { if (st != null) { try { // 10. Close the JDBC CallableStatement st.close(); } catch (SQLException e) {} } } }
The DateAndString
bean used in Example 25-9 is a simple JavaBean with two bean properties like this:
package devguide.advanced.storedproc; import java.io.Serializable; import oracle.jbo.domain.Date; public class DateAndStringBean implements Serializable { Date dateVal; String stringVal; public void setDateVal(Date dateVal) {this.dateVal=dateVal;} public Date getDateVal() {return dateVal;} public void setStringVal(String stringVal) {this.stringVal=stringVal;} public String getStringVal() {return stringVal;} }
Note: In order to allow the custom method to be a legal candidate for inclusion in an application module's custom service interface (if desired), the bean needs to implement thejava.io.Serializable . interface. Since this is a "marker" interface, this involves simply adding the implements Serializable keywords without needing to code the implementation of any interface methods.
|