Clarity

I have an existing Business Objects report which has an Oracle RDBMS data source. That data source is an Oracle STORED PROCEDURE, and returns a REF CURSOR.

I have seen little information concerning the integration of Jaspersoft with Oracle REF CURSOR data sets and queries. According to the JDBC description, REF CURSOR is an accepted data type for JDBC, which drives the data connections to Jaspersoft.

My efforts to use the REF CURSOR as a data source in the Jaspersoft Dataset and Query Dialog have resulted in ORA errors including:

net.sf.jasperreports.engine.JRException: Unable to get next record from result set.

Cannot perform fetch on PLSQL statement: next

net.sf.jasperreports.engine.JRException: Error executing SQL statement for: data.

Has anyone successfully connected to an Oracle STORED PROCEDURE returning a REF CURSOR within the Jaspersoft Dataset and Query Dialog?

I have tried using both the standard Oracle format, as well as the exec format described on the Jaspersoft Community Forum. Both return errors.

Hi Phelan,

We need to see this problem via case as we might need to see the report and might well need to enable traces at Jaspersoft to see what its breaking. Are you able to run the report using Jaspersoft studio?

Regards

Suman Pramanik

Here are further details.

Within the Oracle database schema, there is a STORED PROCEDURE, I will call it SP1. SP1 has six parameters. I will call them P1, P2, P3, P4, P5 and P6. The first five parameters are IN parameters of type VARCHAR2. The final parameter is an IN OUT parameter of type SYS_REFCURSOR.

In Jaspersoft, the first five parameters are all of type java.lang.String. The final parameter is of type java.sql.ResultSet. The ResultSet parameter has to be manually configured as there is no drop-down type for the Jaspersoft parameters that accommodates the results of a SYS_REFCURSOR.

I need to call SP1 from within a Jaspersoft report, using a JDBC connection to the Oracle database schema. I have successfully created the JDBC connection.

I attempt to call SP1 within the Jaspersoft Dataset and Query Dialogue. Here is the invocation:

{call clarity.mbt_iprm_generate_costitnow($P{Parameter1}, $P{Parameter2}, $P{Parameter3}, $P{Parameter4}, $P{Parameter5}, $P{Parameter6})}

This results in the following error:

java.sql.SQLException: ORA-01403: No data found

Assigning the final parameter to the type, java.lang.String, results in this alternative error:

Error executing SQL statement for: data.

PLS-00306: Wrong number of type of arguments in call

What is the proper way to invoke a STORED PROCEDURE with a parameter of type SYS_REFCURSOR?

I was able to resolve this problem. Here are the settings that worked for me.

parameter name, class, default value expression

Parameter1, java.lang.String, "<string that matches data in db>"

Parameter2, java.lang.String, "<string that matches data in db>"

Parameter3, java.lang.String, "<string that matches data in db>"

Parameter4, java.lang.String, "<string that matches data in db>"

Parameter5, java.lang.String, "<string that matches data in db>"

Parameter6, java.sql.ResultSet, "cursor"

Query in Dataset and Query Dialog:

language: PLSQL

query:

{call SCHEMA.SP1($P{Parameter1}, $P{Parameter2}, $P{Parameter3}, $P{Parameter4}, $P{Parameter5}, $P{Parameter6})}

Hi Mphelan,

Kindly advise on how to set the below parameters in the CA Dev Test.

Parameter1, java.lang.String, "<string that matches data in db>"

Parameter2, java.lang.String, "<string that matches data in db>"

Parameter3, java.lang.String, "<string that matches data in db>"

Parameter4, java.lang.String, "<string that matches data in db>"

Parameter5, java.lang.String, "<string that matches data in db>"

Parameter6, java.sql.ResultSet, "cursor".

Thanks,

Swapna

Hi Swapna,

I'm using TIBCO Jaspersoft Studio. Here are the steps I took.

1. Within studio, I open a new report.

2. There is a pane called Outline. Within that pane, you should see a parent node with the name of the report you just created as its label. Expand that node, and find the node, Parameters.

3. Context-click the Parameters node (click with the right button on the mouse) and choose the item, Create Parameter. The Parameters node will expand and you will see a new parameter at the bottom of the list of sub-nodes. It will be called Parameter1.

4. Context-click on Parameter1 and choose Show Properties. A new pane will open called Properties.

5. Within the Properties pane, choose a name for the parameter, and choose a Java class to define the class of the parameter.

You need to repeat steps 2-5 for each parameter, being careful to define the last parameter as a java.sql.ResultSet. Note that you need to manually enter that type, as it is not made available in the drop-down list of values for the Class field.

Please feel free to ask any clarifying questions.

Hi Mphelan,

Can you please help on to define the Cursor output parameters in JDBC Step .

Ex:Parameter6, java.sql.ResultSet, "cursor".

Thank you so much.

Thanks,

Swapna

Sure, here are the precise settings I used for Parameter 6.

In the Properties pane for my CURSOR parameter, Object tab:

Name: <YOUR_CHOSEN_NAME>

Class: java.sql.ResultSet

Descrption: <NONE>

Is For Prompting: <NOT SELECTED/CHECKED>

Default Value Expression: <NONE>

Evauation Time: <NULL>

In the Properties pane for my CURSOR parameter, Advanced tab:

Class: java.sql.ResultSet

Default Value Expression: <NONE>

Description: <NONE>

Evaluation Time: <NULL>

Is For Prompting: false

Name: <YOUR_CHOSEN_NAME>

Nested Type Name: <NONE>

Properties: [Properties: 0]