Unable to fetch the resultset schema in a JDBC call procedure activity.
Article ID: KB0086166
Updated On:
| Products | Versions |
|---|---|
| TIBCO ActiveMatrix BusinessWorks | - |
| Not Applicable | - |
Description
Resolution:
============
The stored procedure has an output parameter of Ref Cursor. The Resultsets Using Schema option is checked. Unable to obtain the resultset schema using an Oracle driver but can get the schema using a DataDirect driver.
Environment:
===========
All
Resolution:
============
This is a limitation of the Oracle driver. At design time, BW executes a prepared statement with default values to create the resultset schema. BW relies on the metadata provided by the driver to determine if the procedure has resultset. The Oracle driver informs that the callablestatement for this procedure does not have a resultset but the datadirect driver states that the procedure returns a resultset. This is the why you cannot use an Oracle driver to fetch thw resultset schema.
The workaround follows:
1). Configure the Call Procedure activity.
2). Add a Write File activity.
3). Configure a Write File activity to write a text file. Provide a local filename with an .xml extension.
4). Map the unresolved result sets from the call activity's output to the textContent input field in the Write File activity.
5). Execute the process definition. Upon successful execution, all records are written to the specified XML file in a predefined format.
6). Open the XML file in XML Authority as a schema. XML Authority generates a schema based on the contents of the XML file (or create the schema manually using the structure provided in the XML file.)
7). Create a schema definition resource using the newly generated schema.
8). Add a Parse XML activity. In the Configuration tab, select the newly created schema definition and type Resultsets in the Element field.
9). Map the unresolved result sets from the Call Process activity's output to the Parse XML activity(s).
xmlString input field.
Result sets can now be utilized at design time from the output of the Parse XML activity.
Note: Only stored procedures that always generate a fixed schema can be utilized.
Issue/Introduction
Unable to fetch the resultset schema in a JDBC call procedure activity.
Feedback
Yes
No
Powered by
