Hi guys,
I have an SQL statement that works fine in Oracle SQL Developer
select
*
from articles
where
artnr in (123, 234, 345)
I want to call this statement from VBA which works if I do it like this:
Sub ConnectToOracle
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim arr As Variant
connstr = "Provider=msdaora;Data Source=###;User Id=###;Password=###;"
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open connstr
rs.CursorType = adOpenForwardOnly
rs.Open ("select * from articles where artnr in (123, 234, 345)"), cn
arr = rs.GetRows
arr = transposeArray(arr)
Dim x As Long
Dim y As Long
x = UBound(arr, 1) - LBound(arr, 1) + 1
y = UBound(arr, 2) - LBound(arr, 2) + 1
Worksheets(1).Activate
ActiveSheet.Range(Cells(1, 1), Cells(x, y)) = arr
'Close connections
Set rs = Nothing
Set cn = Nothing
End Sub
However, my actual SQL-code is much longer and I want to use a stored procedure to store the select-statement on the server and then call it from VBA.
How would I do this? I can't find any online documentation on how to call regular SQL statements from VBA and get a record set. Most of them only feature single variables.
Thanks!