Hi guys - has anyone invoked a stored procedure that returns a reference cursor? I’m trying to invoke an Oracle stored procedure from IS 4.6 using the WmDB ‘pub.db:call’ service. However, I keep getting an error saying:
“java.sql.SQLException: Invalid column type”
Is this because of lack of support for database reference cursors in IS 4.6? (I don’t see any cursor-specific services in WmDB.)
Otherwise does anyone know of a way to call stored procedures that return a cursor? Here’s the stored procedure’s signature:
TYPE GenericCurTyp IS REF CURSOR;
PROCEDURE uom_mappings (
in_translated_uom_standard IN uom_standard.uom_standard%TYPE,
out_fallback OUT erp_uom.uom%TYPE,
out_s2t IN OUT GenericCurTyp,
out_t2s IN OUT GenericCurTyp);
END uom_proc;
Does IS 6.x handle this situation any differently?
You will have to use the JDBC adapter for Ref Cursor support. WmDB does not support it. Since you are using 4.6 you will have to resort to using the Enterprise JDBC adapter (if that is an option in your environment).
The WmDB pub.db:connect service returns a com.wm.app.b2b.server.JDBCConnection object in the $connection field. With that, you can access the java.sql.Connection object with a call to getNativeConnection() against the webMethods object and casting the result. With that you can call your stored procedure and get your cursor.
Thanks Rupinder and Eric - I was able to execute the SP and process the cursors with a Java service. Sadly, going to IS 6 or using a new adapter isn’t an option.
However, I got an “Io exception: Broken pipe” error this morning when trying to use any of the pub.db:* services. Strangely, the Java service that processes the cursors still worked. I had to do a pub.db:closeAll before my pub.db:* services started working. Has anyone seen this problem? Is there any advantage of using the pub.db:connect and pub.db:close services instead of handling the stored procedure database connection setup in Java?
Also, is it possible for pub.db:call to execute a stored procedure that returns multiple row in a result set? This is so I don’t have to use a Java service. I tried writing an SP to return a PL/SQL temp table instead of a cursor, but calling the SP with pub.db:call results in an “unknown column type” error. (I also asked this same question in this thread)
You get the Broken Pipe exception when writing to a disconnected socket. This can happen with the pub.db services when a connection that it uses is closed or corrupted. Because pub.db services use connection pooling I have noticed that sometimes it tries to use a connection that is no longer available.
With respect to your last reply you have mentioned that by using an java service you were able to execute the SP and process the cursors. Can you please tell me how to go about writing that service as i need to handle a curser datatype returned by the stored procedure since i’m also facing invalid column datatype error.
In fact my sp is returning another output parameter with the datatype PL/SQL RECORD ad i think that this also could be the reason for the error.
Since i’m new to this datatype stuff i could use all the valuable info that u ppl can provide. Thank you very much 4 ur time.Really appreciate it.