I have a function that goes like:
create or replace function TestRecordset
return types.cursorType
AS
l_cursor types.cursorType;
begin
open l_cursor for select * from table1 where rownum <= 5;
return l_cursor;
end;

The type is created earlier in this manner:
create or replace package types
as
type cursorType is ref cursor;
end;

I am trying to use ADO To return the recordset but I get the

[Oracle][ODBC][Ora]ORA-24334: no descriptor for this position

error.

the way the code is executed on the asp side is like
Get the connection(objConn) and now set the Command.

Set cmdStoredProc = Server.CreateObject ("ADODB.Command")
Set cmdStoredProc.ActiveConnection = objConn
cmdStoredProc.CommandText = "TESTRECORDSET"
cmdStoredProc.CommandType = adCmdStoredProc

Set rs1 = cmdStoredProc.Execute

I get the error on cmdStoredProc.Execute
Can you explain why??

I've been trying, for far too long, to get ref cursors back from Oracle and into a VBA client. You can do it using the Oracle InProc server, but that does't support asynchronous fetching. You can try doing it using ADO, but I suspect that most developers give up. Here's why:

The ADODB .CreateParameter method only allows a very limited set of return types, making it almost impossible to get a cursor back into the client application.

.CreateParameter("myCursor", adVarChar, adParamOutput)

Try it - even if the parameter creation method succeeds, you'll get an 'invalid SQL' error when the ADO Fetch is completed.

The last piece in the puzzle was the 'adParamReturnValue' constant in this example.

.CreateParameter("myCursor", adNumeric, adParamReturnValue)

Every other code sample I've seen uses adParamOutput and I have yet to see a working example. You'll still have to play with the type - adVarChar doesn't work everywhere - but at least it compiles, runs, and returns data on an 8.06 client.

Here's a sample (not the full listing, which is full of statistical stuff) which gives a simple example:

/*PL SQL FRAGMENT **************/

CREATE OR REPLACE PACKAGE HISTVOLSTATS AS

TYPE Recordset IS REF CURSOR;

PROCEDURE GetRecordset
(
StartDate IN NUMBER,
EndDate IN NUMBER,
lngStockID IN NUMBER,
myRecordset IN OUT Recordset
);

END HistVolStats;




CREATE OR REPLACE PACKAGE BODY HISTVOLSTATS AS

PROCEDURE GetRecordset
(
StartDate IN NUMBER,
EndDate IN NUMBER,
lngStockID IN NUMBER,
myRecordset IN OUT Recordset
)
IS
BEGIN

OPEN myRecordset FOR
SELECT
tblPrice.*
FROM
tblPrice
WHERE
tblPrice.TradingDay > StartDate
AND tblPrice.TradingDay <= EndDate
AND tblPrice.StockID = lngStockID

ORDER BY
tblPrice.TradingDay DESC;

END;

END HistVolStats;






'VBA Fragment *******************

lngStart = CLng(StartDate)
lngEnd = CLng(EndDate)
lngStockID = LookupStockID(Ticker)

Set rst = New ADODB.Recordset
Set cmd = New ADODB.Command

With cmd

.ActiveConnection = connLocal

.CommandText = "HistVolStats.GetRecordset"
.Prepared = False

.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter( _
"StartDate", _
adInteger, _
adParamInput _
, , lngStart)

.Parameters.Append .CreateParameter( _
"EndDate", _
adInteger, _
adParamInput _
, , lngEnd)

.Parameters.Append .CreateParameter( _
"lngStockID", _
adInteger, _
adParamInput _
, , lngStockID)

.Parameters.Append .CreateParameter( _
"myRecordset", _
adNumeric, _
adParamReturnValue)

Set rst = .Execute(, , adAsyncFetchNonBlocking)



End With 'cmd

Set cmd = Nothing

ya its helped me a lil bit ..my problem is a similar one..but here in my proc which is using a ref cursor..is working finely in database and when calling from vb its giving error in special way(typical microsoft way vb6.exe
the instruction at "0x286a87cf" referenced memory at "0x00000047" the meory could not be read..the same vb code is working for original database and its giviong errors for replica databse only that too only when calling procs with ref cursors...can u help me

VB CODEAR SIR

I HAVE VALUE 17.32
I WANT TO CHANGE FRACTIONAL PART .32 IN .33

COMPUTER CALCULATE 17.32 BUT CALCULATOR SHOWING 17.33

CAN U HELP ME IN DE

Hi,
In VB code, user name and passoword to connect to Oracle are provided in the application.
How can we avoid giving the user name and password right in the code?

Thanks
Hima

Nigel's post above shows code for a pl/sql procedure returning a ref cursor as an in/out parameter -- and the accompanying VB. I think it is unnecessary to explicity define the cursor as a parameter to ADO as Tom shows here: </code> http://asktom.oracle.com/~tkyte/ResultSets/index.html <code>

I have a similar working example.
In package specification:
TYPE metric_rt IS RECORD
(m_id metric.id%TYPE,
m_owningOrgId metric.owningOrgId%TYPE,
m_uomId metric.uomId%TYPE,
m_frequencyId metric.frequencyId%TYPE,
m_calendarIndicator metric.calendarIndicator%TYPE,
m_name metric.name%TYPE,
m_abbreviation metric.abbreviation%TYPE,
m_owningOrgName organization.name%TYPE);

TYPE metric_rct IS REF CURSOR RETURN metric_rt;

function getMetric(p_metricId_in IN number)
return metric_rct;


procedure getMetricProc(p_metricId_in IN number,
p_cursor_out IN OUT metric_rct);

in package body:
function getMetric(p_metricId_in IN number)
return metric_rct
is
metric_ref_cursor metric_rct;
begin

OPEN metric_ref_cursor FOR
SELECT m.id,
m.owningOrgId,
m.uomId,
m.frequencyId,
m.calendarIndicator,
m.name,
m.abbreviation,
o.name as owningOrgName
FROM metrics.metric m, metrics.organization o
WHERE m.id = p_metricId_in
AND o.id = m.owningOrgId;

RETURN metric_ref_cursor;

end getMetric;


procedure getMetricProc(p_metricId_in IN number,
p_cursor_out IN OUT metric_rct)
is
begin

OPEN p_cursor_out FOR
SELECT m.id,
m.owningOrgId,
m.uomId,
m.frequencyId,
m.calendarIndicator,
m.name,
m.abbreviation,
o.name as owningOrgName
FROM metrics.metric m, metrics.organization o
WHERE m.id = p_metricId_in
AND o.id = m.owningOrgId;

end getMetricProc;


Note that I have a function returning a ref cursor, and a proc with an in/out ref cursor, both of which do the same sql.
I am a Java developer who has working VB/ADO code calling the proc, but can't figure out how to call the function.
Every example I found on the internet is of a proc with an in/out parameter.

This works for the proc:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=MSDAORA.1;Password=blabla;User ID=blabla;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=somehost)(PORT=1522))(CONNECT_DATA=(SID = mysid)))"

conn.Open

Dim proc As ADODB.Command
Set proc = New ADODB.Command
Set proc.ActiveConnection = conn
proc.CommandType = adCmdStoredProc
proc.CommandText = "metrics_pkg.getMetricProc"
proc.Prepared = False

proc.Parameters.Append proc.CreateParameter("p_metricId_in", adInteger, adParamInput, , 3)

Dim rs As New ADODB.Recordset
Set rs = proc.Execute

While Not rs.EOF
Debug.Print rs(0)
Debug.Print rs(1)
Debug.Print rs(2)
Debug.Print rs(3)
Debug.Print rs(4)
Debug.Print rs(5)
Debug.Print rs(6)
Debug.Print rs(7)

rs.MoveNext
Wend

rs.Close
conn.Close
Set proc = Nothing
Set rs = Nothing
Set conn = Nothing

If I change the name of the proc to be "metrics_pkg.getMetric", the above code fails with
"Wrong number or types of arguments in call to getMetric" ORA-06550.
Anyone know how to call a function which returns a ref cursor from VB/ADO?