|
Using View Objects to Select Stored Function Results
Using BC4J, you can always drop down to write JDBC when needed to invoke stored procedures by using the following methods on the oracle.jbo.server.DBTransaction interface:
Statement createStatement(...)
PreparedStatement createPreparedStatement(...)
CallableStatement createCallableStatement(...)
However, if what you are doing involves simply evaluating the results of a stored function or stored procedure, then there is an easier way to avoid having to write JDBC code. Rather than thinking about invoking the function or stored procedure, turn the problem into a query and use a view object.
For example, suppose you have a stored function which takes an argument like this:
FUNCTION employeeSalary(p_id NUMBER) RETURN NUMBER IS
lv_ret NUMBER;
BEGIN
SELECT SAL
INTO lv_ret
FROM EMP
WHERE EMPNO = p_id;
RETURN lv_ret;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
You can create a read-only, expert-mode view object called test.FindEmployeeSalary that is not related to any entity object to execute the query:
SELECT employeeSalary(:0) AS EMP_SAL FROM DUAL
You do this by using the View Object Wizard and, after giving the new view object a name, pressing (Next>) until you get to the Query panel where you can just type in the query. Note that we've included the bind variables named :0 (colon-zero) to stand in for the parameter we'll need to pass in at query execution time. Since this query will always return one row, we should go to the view object's Tuning panel, and set it Max Fetch Size to 1 for best performance, as well. Tip:
I like to name my first bind variable :0 instead of :1 to remind me that when I set the bind variable values by position, it is zero-based numbering.
To encapsulate the bind variable setting and retrieving of the one row we're expecting, we can add a method like this to our FindEmployeeSalaryImpl.java java class. This method assumes we've generated the VO Row class as well to have typesafe accessors for the FindEmployeeSalaryRowImpl result row:
/** [In file: FindEmployeeSalaryImpl.java]
*
* Use the default rowset of this view object to return the employee salary
*/
public Number getEmployeeSalary(Number id) {
setWhereClauseParam(0,id);
executeQuery();
/*
* Since we're in a business-tier *Impl.java class, we can cast safely
* to the RowImpl class here to use the typesafe accessors
*/
return ((FindEmployeeSalaryRowImpl)first()).getEmpSal();
}
Assuming we have an application module named TestModule, we can use the application model editor to add an instance of the test.FindEmployeeSalary component to our application data model with an instance name of EmployeeLookup. This will automatically generate a getter method in the TestModuleImpl.java class named getEmployeeLookup() to allow us to conveniently access this view object instance. We can then add a custom method to our application module that looks like this:
/** [In file: TestModuleImpl.java
*
* Allow client to get employee salary, encapsulating the use
* of the view object in the business tier. This method is exposed
* as a client-accessible method on the AppModule service interface.
*/
public Number getEmployeeSalary(Number id) {
return getEmployeeLookup().getEmployeeSalary(id);
}
After publishing this as a custom application module method, then we can write client code to test the new custom application module interface method like this:
package test;
import oracle.jbo.client.Configuration;
import oracle.jbo.domain.Number;
import test.common.TestModule;
public class TestClient {
public static void main(String[] args) {
String _am = "test.TestModule", _cf = "TestModuleLocal";
/*
* Cast the application module to our custom TestModule interface that extends
* ApplicationModule and has our getEmployeeSalary() custom method on it.
*/
TestModule testModule = (TestModule)Configuration.createRootApplicationModule(_am,_cf);
System.out.println(testModule.getEmployeeSalary(new Number(7839)));
Configuration.releaseRootApplicationModule(testModule,true);
}
}
Running this should produce an output like:
5000
which confirms that all the mechanics are working correctly.
The next question that typically arises is, "What if I need to call a stored procedure with OUT arguments?" Again, you can always do things using a JDBC CallableStatement, but if you are the kind of person who likes to use as much of the built-in simplifying machinery as possible, then in some cases you can still use the technique above. Let's make the situation more interesting by having the stored procedure return two OUT arguments like this:
PROCEDURE employeeInfo( p_id NUMBER, p_name OUT VARCHAR2, p_sal OUT NUMBER) IS
BEGIN
SELECT ename, sal
INTO p_name, p_sal
FROM emp
WHERE empno = p_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
In order to access this from a SELECT statement, we'll need to write a wrapper function, but the problem is that the procedure returns two different OUT arguments and a function can only have a single return value. What to do?
Easy. We can use a database object type to create a structure that has members to hold both of the out values, and then have our wrapper function return an instance of this database "structure". So, to hold the name and salary OUT parameter information, we can create a type like this:
CREATE TYPE employeeInfo_type AS OBJECT ( name VARCHAR2(10), salary NUMBER);
Next we can write the wrapper function like this to accept the expected IN arguments to our procedure, and return the multiple OUT arguments wrapped in our new employeeInfo_type object.
FUNCTION employeeInfoWrapper( p_id NUMBER ) RETURN employeeInfo_type IS
lv_name VARCHAR2(10);
lv_sal NUMBER;
BEGIN
employeeInfo(p_id,lv_name,lv_sal);
RETURN employeeInfo_type(lv_name,lv_sal);
END;
With this in place, we can repeat the steps we did above to create an expert-mode view object named FindEmployeeInfo for the following query:
select employeeInfoWrapper(:0) as emp_info from dual
To work with this database-object-typed return value in BC4J, we create a domain based on it. This gives us a Java representation of the employeeInfo_type in our BC4J application. In the New Domain Wizard, check the Domain for an Oracle Object Type checkbox, and select EMPLOYEEINFO_TYPE from the list. You can rename the domain to something like EmployeeInfoType instead of taking the default name of EmployeeinfoType.
With the domain created, we edit the FindEmployeeInfo view object and change the type of its EmpInfo attribute from Object to our new EmployeeInfoType domain. Since I created the domain in the test package, the domain will be called test.common.EmployeeInfoType. With this done, we can repeat the steps we did above to add a helper method into the FindEmployeeInfoImpl.java class like this:
/** [In file: FindEmployeeInfoImpl.java]
*
* Use the default rowset of this view object to return the employee info
*/
public EmployeeInfoType getEmployeeInfo(Number id) {
setWhereClauseParam(0,id);
executeQuery();
return ((FindEmployeeInfoRowImpl)first()).getEmpInfo();
}
After adding an instance of the FindEmployeeInfo component to the TestModule application module's data model, with an instance name of EmployeeInfoLookup, we can add another method into our TestModuleImpl.java application module class like this to expose this as a business method on our application module interface:
/** [In file: TestModuleImpl.java]
* Allow client to get employee info type, encapsulating the use
* of the view object in the business tier. This method is exposed
* as a client-accessible method on the AppModule service interface.
*/
public EmployeeInfoType getEmployeeInfo(Number id) {
return getEmployeeInfoLookup().getEmployeeInfo(id);
}
Then, by including getEmployeeInfo in the list of the application module's client methods (using the Application Module editor) we can modify our test program to look like this, illustrating that both view objects work to return their results:
package test;
import oracle.jbo.client.Configuration;
import oracle.jbo.domain.Number;
import test.common.TestModule;
import test.common.*;
public class TestClient {
public static void main(String[] args) {
String _am = "test.TestModule", _cf = "TestModuleLocal";
TestModule testModule = (TestModule)Configuration.createRootApplicationModule(_am,_cf);
System.out.println(testModule.getEmployeeSalary(new Number(7839)));
EmployeeInfoType info = testModule.getEmployeeInfo(new Number(7876));
/*
* Access the name and salary structure info using the domain's getters
*/
System.out.println(info.getName());
System.out.println(info.getSalary());
Configuration.releaseRootApplicationModule(testModule,true);
}
}
which now produces the results:
5000
ADAMS
1100
which illustrates that both of our view objects selecting from stored functions work correctly.
|
