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.Or acle.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 lcConnectionStr ing, loConn
lcConnectionStr ing = "Provider=OraOL EDB.Oracle.1;Da ta Source={Datasou rce};Persist Security Info=True;Passw ord=[password};User ID={user}"

Set loConnection = Server.CreateOb ject("ADODB.Con nection")
loConnection.Op en lcConnectionStr ing
Dim loRS
Set loRS = Server.CreateOb ject("ADODB.Rec ordSet")

Set CMD = Server.CreateOb ject("ADODB.Com mand")
CMD.ActiveConne ction = 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.commant ext: 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.CreateParam eter("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~

[code=asp]
<%
Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Open Application("my ConnectionStrin g") 'connection string here
Set Cmd = Server.CreateOb ject("ADODB.Com mand")

With CMD
.ActiveConnecti on = conn 'You can also just specify a connection string here
.CommandText = "certificat es" 'Name of procedure
.CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag

'Add Input Parameters
.Parameters.App end .CreateParamete r("@MyValue", adInteger, adParamInput, ,LocalValue)

'Add Output Parameters
.Parameters.App end .CreateParamete r("@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, ,adExecuteNoRec ords

'Setting new local variables
MyNewValue = .Parameters("@P rocValue")
End With

Response.Write( "Here is the value that i got: " & MyNewValue )
%>

[/code]