Get Ref Cursor from procedure in ASP

[andeke]

Hi!
I'm developing a classic ASP application in which I'm supposed to retrieve a recordset from an Oracle procedure.

The Oracleprocedure only has one parameter, an out parameter of type REF CURSOR. The provider we are using is "ASPOraOLEDB.Oracle.1".

I can retrieve strings and other types from the procedures in the same Oracle database but I cannot get the ref cursor to work from ASP.

This is the code in ASP:
Dim lcConnectionString, loConn
lcConnectionString = "Provider=OraOLEDB.Oracle.1;Data Source={Datasource};Persist Security Info=True;Password=[password};User ID={user}"

Set loConnection = Server.CreateObject("ADODB.Connection")
loConnection.Open lcConnectionString
Dim loRS
Set loRS = Server.CreateObject("ADODB.RecordSet")

Set CMD = Server.CreateObject("ADODB.Command")
CMD.ActiveConnection = loConnection
cmd.CommandType = adCmdStoredProc
CMD.CommandText = "{call package.utdata()}"

Set loRS = CMD.Execute()

I am receiving the following error:
OraOLEDB error '80040e14'

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'UTDATA' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

If I use the following syntax for the command.commantext: CMD.CommandText = "{call package.utdata(?)}" I only get an empty resultset which is closed.

I also tried to add an out Parameter to the Command object such as:
CMD.Parameters.Append CMD.CreateParameter("varde", adVarChar, adParamOutput)
where "varde" is the name of the out parameter in the Oracle procedure. But got the same error.

I know that we have succeded to get data from the same procedure in VBA/Excel but not from ASP.

I will be really thankful if anyone has a solution to this.

[CroCrew]

Hello andeke,

Here is what I use when banging away at a Microsoft SQL server Stored Proc. I think hitting an Oracle Stored Proc would be very similar. Take a look and see if it helps you out. If not then we can look at your problem in more detail.

Hope this helps~

<%            
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open Application("myConnectionString") 'connection string here
    Set Cmd = Server.CreateObject("ADODB.Command")
 
    With CMD
        .ActiveConnection = conn    'You can also just specify a connection string here
        .CommandText = "certificates" 'Name of procedure
        .CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag
 
        'Add Input Parameters
        .Parameters.Append .CreateParameter("@MyValue", adInteger, adParamInput, ,LocalValue)
 
        'Add Output Parameters
        .Parameters.Append .CreateParameter("@ProcValue", adInteger, adParamOutput, ,0)    
 
        'Note: 
        '    Make sure you are setting the correct type in both Input and Output Parameters.
        '    I am passing in and receiving an integer.
 
        'Execute the function
        .Execute, ,adExecuteNoRecords
 
        'Setting new local variables 
        MyNewValue = .Parameters("@ProcValue")
    End With
 
    Response.Write("Here is the value that i got: " & MyNewValue )
%>