- WebObjects 5.1
- WebObjects 5.2
The Oracle Adaptor in WebObjects 4.5 passed stored procedure arguments by name. However, WebObjects 5 uses the JDBC API, which passes stored procedure arguments by order. The JDBC Adaptor uses the alphabetical order of the "columnName" of the arguments in the EOStoredProcedure to define the order of the arguments for the stored procedure call.
The "column" name is relevant here, not the name shown in the "Name" field of EOModeler. Use the EOModeler inspector to look at the "column" name of each parameter, or use the "Add Column" command at the bottom of EOModeler to add the "column" to the display.
The EOModeler tool can be used to reverse engineer your stored procedures. By default, EOModeler uses strings of numbers like 001 and 002 for the column names to emphasize the order. If you have an existing model file, you can use the EOModeler "New Updated Model" command to regenerate the model file.
Example 1: The stored procedure has only IN and OUT parameters.
The stored procedure expects three arguments in the following order: username(IN), password (IN), and authId (OUT).
After reverse-engineering, EOModeler automatically assigns a column name like "001" for username, "002" for password, and "003" for authId to show the order of execution for the arguments.
EOModeler displays the following:
Example 2: Your stored procedure expects a result set.
An Oracle stored procedure does not produce a result set in the usual way. Instead, it uses an Oracle-specific REF CURSOR parameter to provide access to results. The JDBC adaptor offers limited support for the Oracle REF CURSOR only as the return value for an Oracle stored function, not as an OUT parameter for an Oracle stored procedure.
To work around this issue, rewrite the Oracle stored procedure as a stored function. In Oracle, the function is created with the SQL command "CREATE FUNCTION" instead of "CREATE PROCEDURE", which is used for procedures.
In EOModeler, the Oracle stored function is modeled in the same way as any stored procedure. The external type of the returnValue parameter should be set to "REF CURSOR", even though this is not in the pop-up list of supported types -- you need to type in "REF CURSOR". Its column name should be "000" (a string of three zeroes) to indicate that it is a return value from the stored function.
Known Issues
- 1. EOModeler cannot reverse stored functions or stored procedures defined inside a package.
Workaround: Do not use packages. Or if you have to use packages, first define the stored procedure in your model file manually (not-reverse-engineered) using the EOModeler tool. Then, be careful to use the correct name for the stored procedure. For example, if the package name is "P" and the stored procedure name is "proc" in the database, then the external name of the stored procedure in your model file should be named "P.proc". To verify that the external name is properly:
1. Ensure EOModeler tool is set to the 'Table' mode.
2. Select 'Stored Procedures' to view all stored procedures with their internal and external names.
2. When reverse-engineering a stored function with a REF CURSOR, EOModeler sometimes adds an extra parameter with the external type PL/SQL RECORD, which causes an exception at run-time (Java.lang.NullPointerException).
Workaround:
1. Manually remove all extra OUT parameters, in particular the one of type PL/SQL RECORD.
2. Use the EOModeler Inspector to view the column named 'returnValue'.
3. Using the Inspector, add a false factory method, for example, name it 'factoryMethod') and a false conversion method (for example, name it 'convMethod'. Select 'Bytes' as the value for 'Init Arguments' using the pop-up menu.
4. Save the model file.
Example of the same stored function after modifications:
EOModeler's Inspector showing the column 'returnValue' after modifications:
