Returning PL SQL from an Oracle database

[CHUMPY]

Hi,

I am calling stored procedures and functions from an Oracle database using ADO, I can only return PL SQL tables conatining one column does anybody know if I can return tables containing multiple columns?

Thanks

Chumpy!

 

[mike777]

Hey Chumpy,
I am doing that exact thing (in MS Access, so the syntax might be a little diffferent?). Here's what's been working for me:

Sub TryThis()
Dim ADORst as ADODB.Recordset, strCn as string
DimCnLive as ADODB.Connection, cmdADO as ADODB.Command
Set ADORSt = New ADODB.Recordset
Set CnLive = New ADODB.Connection
strCn="DSN=MyDSN; UID=MyID; PWD=MyPwd; " & _
"DBQ=MyDb; DBQ=W; APA=T; PFC=1; TLO=0"
CnLive.ConnectionTimeout=0
Set cmdADO=New ADODB.Command
strSQL="select * from myTable_in_oracle;"
cmdADO.CommandText=strSQL
With ADORst
.CursorType=adOpenStatic
.LockType=adLockPessimistic
.ActiveConnection=CnLive
End With
ADORst.Open cmdADO.CommandText
End Sub

I'm new to ADO, and all this is kind of freakin' me out, but this seems to work with pretty good efficiency. The only thing I have problems with is in the next procedure, I pass the ADORst object, and do an ADORst.MoveFirst on it. The MoveFirst takes FOREVER; I don't know what the deal is, maybe I'll post a question on that in the MSAccess forum.
Hope this helps.
-Mike

 

[Pipe2Path]

You need to set up a return cursor in PL/SQL to return data.
Just a select statement will not do.

 

[mike777]

Maybe that's why I'm having a problem moving around the recordset (even though I do get data)...Pipe2Path, is it possible to give a brief example of what you mean?
Thanks.
-Mike

 

[CHUMPY]

Hi Pipe2Path,

Thanks for the help, I am returning data in a ref cursor fine. It is passing back data that has been processed/calculated using PL SQL funtctions/ptocedures and contained in PL SQL tables..... I can pass back a table with 1 column but get an unspecified error with two or more columns. I can break the table out into seperate tables each containg one column but this is a nightmare in terms of the number of IN OUT parameters.

Any other ideas would be greatly appreciated?

chumpy

 

[mike777]

This sounds very odd.
Maybe you could give a little code snippet of what works, and then one that doesn't.