Home » Infrastructure » Windows » Oracle Objects for OLE - Paranmert Query using OraParamArray (Windows XP/Access 2003/Oracle 11)
| Oracle Objects for OLE - Paranmert Query using OraParamArray [message #542191] |
Sun, 05 February 2012 14:14 |
 |
leightonstew
Messages: 1
Registered: February 2012
Location: Seattle
|
Junior Member |
|
|
I am trying to use OO40 to retrieve Oracla db records into an Access database; complex queries on a large database. I want to pass an array of parameters to a SELECT query, hopefully minimizing network traffic and optimizing Oracle response time. I have the following VBA code, which does work to retrieve the first record (PREM_CODE="327376") but only the first record.
So, my question is, if I may: How do I view subsequent records if not rst.MoveNext? Do OraParamArray objects only work with PL/SQL cursors or, when using SELECT queries as below, do I need to refresh the dynaset in some apparently undocumented way?
========================================
Note: in the version below I use the parameter placeholder in the clause...
"WHERE s.PREM_CODE in (:PREMCODES)"
... I did try the simpler form
"WHERE s.PREM_CODE = :PREMCODES
but it also returns only one record.
=========================================
Private Sub OraParamArrQuery()
On Error GoTo ParamQuery_ERR
ErrMsg = ""
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraCollection As OraCollection
Dim OraObject As OraObject
Dim PremCodeArray As OraParamArray
Dim SvcNumArray As OraParamArray
Dim strSQL As String
Dim rst As OraDynaset
Dim NoOfRows As Integer: NoOfRows = 20
' Use default Dynaset option (0)
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("*****", "***", 0&)
'Define parameter arrays, one for each column
OraDatabase.Parameters.AddTable "PREMCODES", ORAPARM_INPUT, ORATYPE_VARCHAR2, 13, 7
Set PremCodeArray = OraDatabase.Parameters("PREMCODES")
'Initialize the newly created input parameter table PREMCODES
With PremCodeArray
.AutoBindEnable
.put_Value "327376", 0
.put_Value "327380", 1
.put_Value "327377", 2
.put_Value "327304", 3
.put_Value "327305", 4
.put_Value "327306", 5
.put_Value "327307", 6
.put_Value "327308", 7
.put_Value "327309", 8
.put_Value "327310", 9
.put_Value "325372", 10
.put_Value "325373", 11
.put_Value "327175", 12
End With
' Create a sqlstmt to insert array values into table
strSQL = "SELECT s.PREM_CODE, s.SERVICE_NUM, " & _
" FROM CMART.SERVICE s WHERE " & _
" s.PREM_CODE in (:PREMCODES)"
' Now call the SQL only once
Dim i As Integer
'Create the OraDatabase Object by opening a FWD ONLY connection to Oracle.
Set rst = OraDatabase.dbCreateDynaset(strSQL, &H8&)
'Create the OraDynaset Object using sliceSize as 256,perblock size as 16, no. of
'blocks as 20, fetchLimit as 20,FetchSize as 4096
' Set rst = OraDatabase.CreateCustomDynaset(strSQL, 0&, _
256, 16, 20, 20, 4096)
Debug.Print rst.RecordCount
rst.MoveFirst
While Not rst.EOF
Debug.Print rst.RecordCount, rst!PREM_CODE
rst.MoveNext
Wend
' Next i
ParamQuery_EXIT:
On Error Resume Next
rst.Close
Set rst = Nothing
OraDatabase.Parameters.Remove "PREM_CODE_ARR"
OraDatabase.Close
Set OraDatabase = Nothing
OraSession.DestroyDatabasePool
Set OraSession = Nothing
Exit Sub
ParamQuery_ERR:
If ErrMsg <> "" Then
MsgBox ErrMsg
Else
MsgBox Error$
End If
Resume ParamQuery_EXIT
End Sub
|
|
|
|
Goto Forum:
Current Time: Wed Dec 04 02:38:43 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
