Call Oracle stored procedure with VBA using a refcursor

Question

I have stored procedure in Oracle:

create or replace procedure testproc (articlenr in number, storenr in number, cweek in varchar, prc out sys_refcursor)
is begin
open prc for 
select * from weekly_revenues
where
  article = articlenr
  and period = cweek
  and store = storenr
  ;
end;

I can call that function perfectly with the following SQL code:

variable rc refcursor;
exec testproc(123,345,'201705',:rc);
print rc;

That code gives me all the sales data for article 123, store 345 in week 5 of 2017.

Now, I want to call that stored procedure from VBA and I've tried the following code:

Sub ConnectToOracle()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
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

cmd.CommandText = "testproc"
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
Set param1 = cmd.CreateParameter("articlenr", adInteger, adParamInput, , 47)
Set param2 = cmd.CreateParameter("storenr", adInteger, adParamInput, , 281)
Set param3 = cmd.CreateParameter("cweek", adVarChar, adParamInput, 10, "201705")
Set param4 = cmd.CreateParameter("prc", adVariant, adParamOutput, , Null)
cmd.Parameters.Append param1
cmd.Parameters.Append param2
cmd.Parameters.Append param3
cmd.Parameters.Append param4

Set rs = cmd.Execute

arr = rs.GetRows
'Work with array...

End Sub

However, I get an arrow for the

Set rs = cmd.Execute

line ("ORA-0136: illegal variable name/number"). Does anyone know how I can get my code to run? I'm not sure whether the variable for the refcursor needs to be declared in a different way (as in the second code snippet), but I'm not sure whether that is the mistake or not.

Thanks!

Why do you use a procedure when you have only one output parameter? Use a function for that. — Wernfried Domscheit
– Wernfried Domscheit, Commented Mar 2, 2017 at 13:24

Berbatov · Accepted Answer · 2017-03-02 14:03:28Z

2

I found a solution by accident. If I don't define the ref_cursor parameter, it works.

cmd.CommandText = "testproc"
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
Set param1 = cmd.CreateParameter("articlenr", adInteger, adParamInput, , 47)
Set param2 = cmd.CreateParameter("storenr", adInteger, adParamInput, , 281)
Set param3 = cmd.CreateParameter("cweek", adVarChar, adParamInput, 10, "201705")

'Next line not needed
'Set param4 = cmd.CreateParameter("prc", adVariant, adParamOutput, , Null)

cmd.Parameters.Append param1
cmd.Parameters.Append param2
cmd.Parameters.Append param3

'Next line not needed
'cmd.Parameters.Append param4

Set rs = cmd.Execute

This works, apparently the ref_cursor specification is not needed and happens by default :)

answered Mar 2, 2017 at 14:03

Berbatov

1,0832 gold badges13 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wernfried Domscheit · Accepted Answer · 2017-03-02 13:22:31Z

0

Try this one:

Cmd.Properties ("PLSQLRSet") = TRUE  
Set rs = cmd.Execute

arr = rs.GetRows
'Work with array...

answered Mar 2, 2017 at 13:22

Wernfried Domscheit

60.5k10 gold badges93 silver badges132 bronze badges

2 Comments

Berbatov Over a year ago

Thanks for the suggestion. I get the following error on the new line "Item cannot be found in the collection corresponding to the requested name or ordinal in access". Solved it by leaving out the definition of the ref_cursor parameter

Wernfried Domscheit Over a year ago

Yes that's right, I forgot that. See Provider for OLE DB Developer's Guide: OraOLEDB returns a rowset for the REF CURSOR bind variable. Because there is no predefined data type for REF CURSOR in the OLE DB specification, the consumer must not bind this parameter.

Collectives™ on Stack Overflow

Call Oracle stored procedure with VBA using a refcursor

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related