Portlet Factory, Version 6.1.2

---

 

Displaying result sets from Oracle stored procedures

Oracle stored procedures return a REF_CURSOR object from which the results of the stored procedure may be made available to IBM® WebSphere Portlet Factory builders. To use the REF_CURSOR object, the results from the stored procedure must be explicitly cast in a Java method. You can use either a Method builder call, or a method in a linked Java object.

To cast the output parameter of an Oracle stored procedure, follow these steps:

1. Use the SQL Call builder to call the stored procedure. You can use the Database Explorer functionality in the builder call editor to select the stored procedure you want to call and to generate the SQL statement.

2. Create a method to cast the output parameter for the REF_CURSOR object.

3. In the Method builder call, the Arguments input should be:

   buildername

   String

   statement

   Object

   position

   int

   action

   String

   Return Type

   void

   body of the method

   {     try
       {
         // Code is specific to a CallableStatement.  Cast will throw an exception
        // if the caller tries to pass anyother type of object into the method.
         final CallableStatement cs = (CallableStatement) statement;
         if( cs != null && "cast".equals( action ) ) 
        {
           // Cast and register the output parameter at position "position" to be an
          // Oracle CURSOR type.  If the Oracle JDBC driver ZIP / JAR file is not
          // in the method compiler's classpath, then this will cause an error.
           cs.registerOutParameter( position, OracleTypes.CURSOR );
         }
         else if( cs != null && "set".equals( action ) )
         {
           // The "set" action is not supported for result sets so throw an
          // exception to alert the caller to the problem.
           throw new Exception( "Cannot set the value of a ResultSet" );
         }
       }
       catch( Throwable t )
       {
           // If anything goes wrong, then log an error to the Bowstreet Event log
          // and rethrow the exception for handling by the model.
          LogEvent.writeLogEntry( Severity.ERROR, Component.MODEL, t );
          throw new WebAppRuntimeException( t );
       }
     }

   Import List

   java.sql.CallableStatement oracle.jdbc.driver.OracleTypes com.bowstreet.appserver.logmanager.* com.bowstreet.webapp.engine.WebAppRuntimeException

4. In the SQL Call builder call, enter the name of the method directly as the value for the parameter's Value or Set/Cast Method. In this example, "castRefCursor".

   Note: Do not use the reference chooser to select the method; you just want to provide the method name for the Value or Set/Cast Method input.

5. Add the ojdbc14.jar you are using for your datasource and add it to the project's WEB-INF/work/lib directory.

6. In the WEB-INF/config/override.properties file, copy the bowstreet.methods.classpath property and its value in the WEB-INF/config/cluster.properties file and paste it into the override.properties file.

7. Add the ojdbc.jar to the method compiler classpath value. For example:

   # This will be used as the method compiler class path, if not specified then the System property "java.class.path" will be used.
   # Note - You can specify multiple items by using a comma "," separator. There is no need to change the separator based on target OS. bowstreet.methods.classPath=${bowstreet.rootDirectory}/work/lib/ojdbc14.jar,
   ${bowstreet.rootDirectory}/clientLibs/j2ee.jar,${bowstreet.rootDirectory}/classes,
   ${bowstreet.rootDirectory}/clientLibs/portlet.jar

You will now be able to work with the results from the Oracle stored procedure.

Parent topic: Working with a database

---

Library | Support |