Ado MoveFirst method problem with ORAOLEDB v.9.2.01 provider

Posted on 2004-12-01
Last Modified: 2013-12-25
Hi all,
I am using an old ASP technology with ORAOLEDB.Oracle v.9.2.01 provider, ADO connection with CursorLocation = adUseServer and ADODB.Recordset. In the last release we updated ORACLE 8i to 9.2.03 and in one of asp pages I am having a problem with recordset.MoveFirst method. The page is a report that contains a couple passes of the same recordset. The related code is following:
<!-- #include file="..\scripts\connect.asp" -->
DIM oConn
DIM oResult
DIM oResult1
DIM oResult2
‘Connect to Oracle DB with CommandType= adCmdStoredProc
call exec_connect(oConn)  

‘Execute stored procedure with refcursor output -oResult.
call exec_procedure(oConn, oResult, sSPname, sParameters)  

‘Read SP output that contains 2 refcursors
Set oResult1 = oResult   ‘contains 107 records
Set oResult2 = oResult.NextRecordset()   ‘contains 2 records

‘1st Pass
Do While not oResult1.EOF  
       someVar = oResult1(“column_nme”)  

‘2nd pass
Do While not oResult1.EOF  
       someVar = oResult1(“other_column_nme”)  

‘3rd pass
Do While not oResult2.EOF  
      Do While not oResult1.EOF  
            someVar_1 = oResult1(“other_column_nme”)  

       someVar_2 = oResult1(“column_nme”)          

Only the first pass works properly in this sample. After oResult1.MoveFirst I am not able to read oResult1(“column_name”) value and all following logic is corrupted. This code works fine for smaller oResult1 that contains less then 100 records. Also it was in production with Oracle 8i without any visible problems.  
Does anyone know about this ORACLE 9i bug? Any suggestions about the code will be appreciate.
Question by:mikosha
    LVL 29

    Accepted Solution

    Lets see the code in exec_procedure.  Most likely you are using default value for CursorType or it is set to adOpenForwardOnly, 0 (Default).  You should use adOpenStatic, 3 instead.


    LVL 4

    Author Comment

    Thanks for your response and you are right I am using default rs.CursorType =adOpenForwardOnly that theoretically allows you to perform MoveNext, MoveFirst and MoveLast operations.
    Also, I tried to change it to adOpenDynamic, adOpenStatic. The result is confusing :  on the report page itself debug message tells me that it is still adOpenFirwardOnly. Here are the Connection.asp code. Am I doing something wrong or rs.execute just ignores my settings.

    sub exec_connect(cn)
          set cn = Server.CreateObject( "ADODB.Connection" )
          cn.Provider = "OraOLEDB.Oracle"
          cn.ConnectionString = Session("sApplicationConnectionDat")
    end sub
    sub exec_procedure(cn,rs,proc_nme,parameter)
          Dim cmd, param
          Dim parameters
            Dim paramcount, counter
          Dim paramval
          Dim sCommandText

          set cmd = server.CreateObject ("ADODB.Command")
          set cmd.ActiveConnection      = cn
                cmd.CommandText                  = proc_nme
                cmd.CommandType                  = adCmdStoredProc

          for counter = 0 to paramcount
                paramval = SPLIT(parameters(counter),"~")

                if paramval(1) = 2 then
                      set param = cmd.CreateParameter("param"&cStr(counter), paramval(2), paramval(1), 4000)
                       set param = cmd.CreateParameter("param"&cStr(counter), paramval(2), paramval(1), 4000, paramval(0))
                end if
                cmd.Parameters.Append param


          set rs = server.CreateObject ( "ADODB.Recordset" )
          rs.CursorType = adOpenStatic
          set rs = cmd.execute            

          set cmd = Nothing
          set param = Nothing
    end sub
    LVL 18

    Assisted Solution

    "The result is confusing :  on the report page itself debug message tells me that it is still adOpenFirwardOnly." --> This is because Oracle unlike SQL Server does not support Dynamic recordset and when you try using this type, Oracle provider will return ForwardOnly cursor type. Please check the Oracle documentation for the types supported by Oracle and then use that type.
    LVL 4

    Author Comment

    Look what I've found at

    "Setting ADO Rowset property CursorType to adOpenKeyset or adOpenDynamic is not supported by Oracle Provider for OLE DB. OraOLEDB does not support either of the two as Oracle supports Statement Level Read Consistency, which ensures that the data returned by a query contains only committed data as of the time the query was executed. CursorType values adOpenStatic and adOpenForwardOnly are supported by OraOLEDB."

    So, why I am not able to reset to  adOpenStatic as well?
    LVL 4

    Author Comment

    The problem solved by changing the connection location to adUseClient that sets default cursorType to adOpenStatic. Eventually now I have to keep two different connections (server and client) in application  wich is not a big deal.

    Thanks everybody for your time and help .

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now