Stored Procedures returning Oracle cusor

Hi,
I've been trying to call a stored procedure that returns an oracle cursor and have been unable to do so. I posted on the bugzilla bug 117166 but haven't heard anything back so I figured I would post here as well and see if anyone else ran into the same issue and knows how to fix it.

My test procedure is

declaration:
PROCEDURE TEST(
test_cursor OUT SYS_REFCURSOR
);

body:
PROCEDURE TEST(test_cursor OUT SYS_REFCURSOR) IS
begin
OPEN test_cursor FOR
select * from testTable;
END TEST;

In BIRT (the version I am using is version is 2.2.1.r22a_v20071101) - I have
set up my query as {call testPackage.TEST(?)}. When I try to preview the
results, I get an error stating "SQL Error #1: Missing IN or OUT parameter at
index:1".
Based on this error, I add an output parameter to the parameters list. The
name of the parameter is "param1" and Direction is "output" and the Data Type is "Any". When I click on Preview Results, I get an error message "PLS-00306: wrong number or types of arguments in call to 'TEST'".
It's not a problem with the schema or package because I am able to call other stored procedures that do not return cursors. It seems like this is a feature that should work in this version of BIRT based on some of the posts by people from Actuate. Is this a known bug or is there something I am doing wrong?

Find more posts tagged with

Comments

hho

Hi,

Check to see if your JDBC driver is full implement of JDBC interface?

Your SP returns data in cursor. The full impleent of JDBC interface can map the cursor to result set. I had the same problem before when I used IBM JBDC driver. I switch to Actuate Oracle JDBC driver. Everything works fine now.

Hope it helps

hho

I forgot to mention if you use the right JDBC driver. For the SP that you posted the parameter list is none, your query should be {call testPackage.TEST()}.

vemons2

Hi, 
 
Thanks for the response! 
 
While I was searching for resolutions to this problem, I came across this bug: 
<a class='bbc_url' href='https://bugs.eclipse.org/bugs/show_bug.cgi?id=117166'>https://bugs.eclipse.org/bugs/show_bug.cgi?id=117166</a> 
 
If you look at comment #13 (Max from Actuate), he describes how to set up a stored procedure that returns a cursor. He does have a "?" in the parameter list when calling the stored procedure, I'm not sure if the situation is different if there's only one parameter being defined in the stored procedure. However, I did try it like you mentioned and I got the same error as before - "PLS-00306: wrong number or types of arguments in call to 'TEST'". 
 
As far as the driver, I am using ojdbc14.jar, this is the jar that is attached on that same site I mentioned before and reading through the comments, it should work with that jar so I think the driver I am using is okay. I'm not sure where I would even download the Actuate Oracle JDBC Driver. If you look within the BIRT installation, go to plugins, and then org.eclipse.birt.report.data.oda.jdbc_*, you'll notice a oda-jdbc.jar. I *think* this is what's used to map ResultSets (and other data types) properly in BIRT. 
 
Any other thoughts? 
 
Thanks!

hho

Hi,

The ojdbc14.jar driver is not full implement of JDBC interface so you cannot use this driver. If you use this driver in java application, the calling statement would be {Begin storeprocedureName(parameterlist) end;}. After that you have to convert cursor to resultset.

Try to find a driver that has full implement of JDBC interface.
We use BIRT Design Report proffesional tool. We just use the Actuate driver provided by this tool. Everything works fine.

rmurphy

The drivers that are included as part of the Actuate BIRT Designer (<a class='bbc_url' href='http://www.birt-exchange.com/modules/wfdownloads/singlefile.php?cid=2&lid=238'>http://www.birt-exchange.com/modules/wfdownloads/singlefile.php?cid=2&lid=238</a>) function quite nicely. The Actuate Oracle driver takes the results of the Oracle Ref Cursor and returns it to the BIRT Engine as a ResultSet. Therefore you still get the benefits of your Oracle Stored Procedure without the Engine needing to process Oracle's proprietary Ref Cursors. 
 
Rob

vemons2

Thanks for the response.

Unfortunately, I don't think we want to worry about any kind of licensing when we go into production. Currently, we're just trying out different reporting frameworks.
From what I've read online, it seems like mapping cursors to resultsets should be possible without using a driver provided by Actuate. The bugzilla entry I posted about earlier shows comments by people using the ojdbc14.jar who say they are able to get cursors to work in BIRT. So I think I should be able to as well - not sure why I can't.

zsarah

I used IBM's driver and got the following error message:
java.sql.SQLException: Missing IN or OUT parameter at index:: 1
After I switched to ojdbc14.jar driver I got another error message:
java.sql.SQLException: ORA-01008: not all variables bound

Can someone point out the correct oracle driver - where do I find it in BIRT. I use Oracle 10 and Birt 2.1.2.

Thanks,

Sarah

deanhiller

I have ojdbc14.jar working just fine with one cursor being returned. I however cannot get two cursors returned for some reason...not sure if it is my proc or BIRT though or driver. My query in BIRT is just...

{ call procOnDemandCallStatus(?, ?, ?, ?, ?) }

where the 5th ? is the output cursor. The latest BIRT discovers it and lists it as an output parameter for me. Make sure your database permissions are all setup correctly.

mfresa

Has anyone got this to work with Maximo datasource drivers? If so, please post the open and fetch routine. I can't get this to work.
Thanks
Mark

mfresa

Found the issue with the Maximo template. Evidently the driver does not allow the return of ref cursors from stored procedures. I could only get it to work using a standard JDBC driver. Maximo strikes again......

Hans_vd

Hi vemons2

Try adding the Oracle owner in which you created the procedure.
So do {call your_owner.testPackage.TEST()}

Hope this helps
Hans