Multiple Recordsets with Oracle DB 1

[InsaneProgrammer]

Is it possible to use multiple recordsets with an Oracle Database? If your connecting to SQL Server or mySQL you can do somthing like this:

private sub Example

Dim sSQL
Dim oRS
Dim oCON

set oCON=server.createobject("ADODB.Connection")
oCON.Open ("SOME_CONNECTION_INFO")

sSQL = "Select * from Table1"
sSQL = sSQL & ";Select * from Table2"
sSQL = sSQL & ";Select * from Table3"

set oRS=Server.CreateObject("ADODB.Recordset")
oRS.Open sSQL, oCON

if not oRS.eof then
  'Do Some Calculations on the Data from Table1
end if

set oRS = oRS.NextRecordset

if not oRS.eof then
  'Do Some Calculations on the Data from Table2
end if

set oRS = oRS.Nextrecordset

if not oRS.eof then
  'Do Some Calculations on the Data from Table3
end if

oRS.Close
oCON.Close
set oRS = Nothing
set oCON = Nothing

End Sub

This method reduces the number of recordsets that you create and the number of trips your program makes to the database. When I try to do this from VB or an ASP page to an Oracle 8i Database I get the following error:
ORA-00911: invalid character
Any help would be greatly appreciated.

 

[Joe46]

Yes, you can have multiple recordsets with Oracle, although I don't know what is causing your error.

Just wanted to let you know that it is possible to have more than one recordset.

 

[InsaneProgrammer]

Have you actually used it. I'd like an example if possible. Me error is caused by the ";" in the sql statement. When I take out the semicolon the error changes. It then tells me that me sql statement isn't terminated correctly. It seems like the semicolon isn't getting parsed correctly.

 

[Joe46]

Sorry, I haven't used it. I was looking through my book "Visual Basic Oracle 8", when I came across this statement: "Next Recordset: Clears the current Recordset object and returns the next Recordset by advancing to the next in a series of commands."

Like I said, I was just trying to let you know that it is possible so you would know that you could have multiple recordsets.