Call Store Procedure and return Cursor type

Hello, I'm BIRT newbie.
I got a problem when use BIRT to call Store Procedure.
This Store Procedure input one parameter integer type,
and output a Cursor type data
such as
CREATE OR REPLACE PROCEDURE latest(
num_entries_in IN NUMBER,
entries_cursor_out OUT SYS_REFCURSOR
) AS

but when I add this data set use
{call latest(4, entries)}
BIRT will not response any data, and when I close data set window BIRT will close with it.

Have anyone can help me?
or give me some suggest. Thx

Find more posts tagged with

Comments

Virgil Dodson

Hi loume,

What version of BIRT are you using? With BIRT 2.3, you can now select from multiple resultsets and select from stored procedures that return a cursor.

loume

Hi vdodson
I use BIRT version is 2.3
Is Oracle JDBC version problem?
I use Oracle JDBC with ojdbc14.jar
There have some article told ojdbc14.jar can't support this action?
Thx

Virgil Dodson

Hi loume,

Try putting this:
{call latest(?, ?)}
instead of:
{call latest(4, entries)}

...and then select the Parameters tab. You will see two parameters detected. One is already selected as input... enter a default value of 4... and the other one should be selected as ouput.

Now try preview, that should give the results you want. I am using ojdbc14.jar with Oracle stored procedures returning cursors with no problems.

loume

Hi vdodson,

I had try {call latest(?, ?)}
but in parameter tab I can't see any parameters,
so, I add to parameters by myself
P_IN type is Integer as input
P_OUT type is Any as output
when I click finish, I got error message
and when I tyr preview, I can't see any result

BIRT 2.3 download with BIRT Home All-In-One....
Is there something wrong with me?

loume

I had try twice today.

but I got the same error message

Error :
Cannot execute the statement.
odajdbc.CannotGetParameterMetadata

Reason:
A BIRT exception occurred.

Detail :
Plug-in Provider : eclipse.org
Plug-in Name : BIRT Data Engine
Plug-in ID : org.eclipse.birt.data
Version : 2.3.0.v20080606
Error Code : odaconsumer.CannotExecuteStatement
Error Message : Cannot execute the statement. odajdbc.CannotGetParameterMetadata.

It's there any problem??
Or there has any setting I need to do??
Thx

Virgil Dodson

Hi loume, 
 
It is interesting that you cannot see the parameters automatically. Can you try following the same steps I followed in the Word document at the link below? You should be able to see the Input and Output parameters while you are selecting your Stored Procedure. 
 
<a class='bbc_url' href='http://www.birt-exchange.com/devshare/designing-birt-reports/447-using-stored-procedures-with-birt/'>http://www.birt-exchange.com/devshare/designing-birt-reports/447-using-stored-procedures-with-birt/</a>

sjalla

Has anybody found the reason for the birt exception

Error Code:odaconsumer.CannotExecuteStatement
Error Message:Cannot execute the statement.
odajdbc.CannotGetParameterMetadata

I am getting the same problem, tried all possible things, but nothing worked.

I followed all the steps in the link that shows how to create a data set using stored procedure, I am still getting that error.

jalla

sjalla

Hi loume, 
 
Check the bug report for the work around, don't know whether that will fix your problem. 
 
Logged a bug at bugzilla 
 
<a class='bbc_url' href='https://bugs.eclipse.org/bugs/show_bug.cgi?id=244966'>https://bugs.eclipse.org/bugs/show_bug.cgi?id=244966</a>

Corinth

I'm not having this problem with the designer, but when I take a working report out of the designer and deploy it into my own home-grown implementation of the RE API (which works fine for non-stored proc based reports), I get the stack trace below.

Version: 2.3.0
OS: Windows

Again, this report works in the designer tool (and with the built in report viewer). I'm not doing anything terribly fancy in my own app, just executing a run task. My .rptdocument is created but without any data (just headers and an error message).

22:14:16,830 INFO [STDOUT] Aug 28, 2008 10:14:16 PM org.eclipse.birt.data.engine.odaconsumer.PreparedStatement$SequentialRes
ultSetHandler getMoreResults
SEVERE: Cannot get more result sets from the statement.
odajdbc.CannotGetParameterMetadata
org.eclipse.datatools.connectivity.oda.OdaException: odajdbc.CannotGetParameterMetadata
at org.eclipse.birt.report.data.oda.jdbc.CallStatement.getParameterMetaData(CallStatement.java:1808)
at org.eclipse.birt.report.data.oda.jdbc.CallStatement.getParameterType(CallStatement.java:498)
at org.eclipse.birt.report.data.oda.jdbc.CallStatement.registerOutputParameter(CallStatement.java:479)
at org.eclipse.birt.report.data.oda.jdbc.CallStatement.execute(CallStatement.java:562)
at org.eclipse.birt.report.data.oda.jdbc.CallStatement.getMoreResults(CallStatement.java:1697)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaAdvancedQuery.getMoreResults(OdaAdvancedQuery.java:206)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement$SequentialResultSetHandler.getMoreResults(PreparedState
ment.java:4885)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getMoreResults(PreparedStatement.java:749)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.flushResultSets(PreparedStatement.java:936)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.close(PreparedStatement.java:907)
at org.eclipse.birt.data.engine.executor.DataSource.close(DataSource.java:354)
at org.eclipse.birt.data.engine.impl.DataSourceRuntime.closeOdiDataSource(DataSourceRuntime.java:238)
at org.eclipse.birt.data.engine.impl.DataEngineImpl.closeDataSource(DataEngineImpl.java:481)
at org.eclipse.birt.data.engine.impl.DataEngineImpl.shutdown(DataEngineImpl.java:568)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.shutdown(DataRequestSessionImpl.java:366)
at org.eclipse.birt.report.engine.data.dte.DataGenerationEngine.shutdown(DataGenerationEngine.java:147)
at org.eclipse.birt.report.engine.executor.ExecutionContext.closeDataEngine(ExecutionContext.java:786)
at org.eclipse.birt.report.engine.api.impl.RunTask.doRun(RunTask.java:231)
at org.eclipse.birt.report.engine.api.impl.RunTask.run(RunTask.java:89)

Corinth

I think I've been able to isolate the problem to incorrectly set report parameters - they must send the stored proc call into fits and cause downstream problems such as this one!

deanhiller

yes, we ran into BIRT not discovering problems. The main problem is BIRT somehow runs the proc to discover the output, and then would error out and if your stored proc didn't have exception handling, you would just get nothing or in our case, it discovered only one column coming out, the error column. Also, make sure your database permissions are correct. to use BIRT, I really discovered I needed to become a dba type more and get into oracle and fool around and try my own procs and what not. Got to learn alot about PL/SQL though!!!

jain

Hi, how to handle multiple cursor results in a single dataset, I have a stored procedure which is returning 3 cursors, but in dataset at a time only one cursor is returning. How to have 3 cursors in single dataset?