Oracle Procedure dont work with Ref Cursor OUT

[amarildolacerda]

I have some procedure into oracle packaege that work fine.
This days I receive from DBA one procedure that OUTPUT a Ref CURSOR.

Pkg_test.procedure( listParams IN , refCursor OUT );

http://asktom.oracle.com/pls/asktom/ASK ... 3078805685
http://www.oracle-base.com/articles/mis ... rdsets.php

Some one known how I can retrieve this Oracle - Ref Cursor ?

[amarildolacerda]

I propose add stCursor at ZDbcIntfs.TZSQLType

TZSQLType = (stUnknown, stBoolean, stByte, stShort, stInteger, stLong, stFloat,
stDouble, stBigDecimal, stString, stUnicodeString, stBytes, stDate, stTime,
stTimestamp, stAsciiStream, stUnicodeStream, stBinaryStream,stCursor);


It will be possible to suport Oracle RefCursor with procedure OUTPUT or Result Functions

[EgonHugeist]

amarildolacerda,

i wish i could say YES, lets do it. But here i'm waiting for MDeams. He's oracle developer. In my mind we still need an upgrade here concernung stCurrency, stBCD, ..., stCursor.

Which type is the Cursor from? types.cursorType

Michael

[mdaems]

As far as I understand there's not a real generic way you can develop to handle the data returned. You could do so when you're sure the procedure only returns one variable, which is the refcursor variable. This isn't really something you easily do with a 'one resultset at a time' architecture as zeoslib's.

I think I would write a wrapper 'Oracle Pipelined Table Function' for this procedure that takes the same input parameters as your procedure.
That way you can select from te wrapper function that in turn calls the procedure and then reads the refcursor content line by line. That way you have a select * from table(<wrapper function call>) that can be handled easily by TZQuery.

Mark

[amarildolacerda]

First of all, Its so complex for me.
I try to change some piece of code to see if its possible CAST/Convert ref cursor (POCIStmt) parameters to IZStatement and/or IZDataset like TNestedDataset;

Ill attach my code... (no complete yet) (ZDbcOracleStatement line 819)

....
case CurrentVar.TypeCode of
SQLT_RSET : DefVarManager.SetAsPointer( outParamValues, CurrentVar.Data );
....


About Table Functions, its sound good.
Nowadays, I have package to isolate Oracle Code and execute some before retrieve dataset

[mdaems]

amarildolacerda,
You're ambitous! Certainly more than I am.
If you get TNestedDatasets working, that would be extremely nice. But even a utility function to get a dbc resultset instance from a refcursor would be nice!
I'm hopefully waiting for the result of your experiments!

Mark