Looking for help on Classic ASP. I have a Oracle 8 Stored Procedure which returns Cursor output. I would like to call SP on in ASP page and display the data in a table format by looping all result set rows.
Here is the code which i tried:
Dim conn, cmd, rs
Set conn = Server.CreateObject("adodb.connection")
With conn
.ConnectionString=strConnect
.Open
End With
Set cmd = Server.CreateObject ("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "Ref_Cursor_Output_Procedure"
cmd.CommandType = 4 'adCmdStoredProc
Dim param1
Set param1 = cmd.CreateParameter ("pid", adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = 30
Set rs = cmd.Execute
Do Until rs.BOF Or rs.EOF
-- Do something
rs.MoveNext
Loop
rs.Close
Please have a look and let me know if anything wrong.
I got the solution:
Dim cn, rs, cmd, param
set cn = Server.CreateObject( "ADODB.Connection" )
cn.Open Cstr(strConnect) '"Provider=MSDAORA.1;Data Source=xxxx;User ID=xx;Password=xx"
set cmd = server.CreateObject ("ADODB.Command")
with cmd
set .ActiveConnection = cn
.CommandText = "{call ref_cursor_procedure({resultset 0, t_cursor})}"
.CommandType = 1
end with
set rs = server.CreateObject ( "ADODB.Recordset" )
set rs = cmd.execute
Set rs = cmd.Execute
Do Until rs.BOF Or rs.EOF
-- Do something
rs.MoveNext
Loop
rs.Close
It worked well.
thanks