Call Oracle Stored Procedure with Parameter from Excel

Question

I need to call a stored procedure from Excel. The parameter is set in cell A1 (Date - 06.04.2022).

Below code is not working error message - "type mismatch"

Dim cn As New ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
 
 
Set cn = New ADODB.Connection
 
cn.ConnectionString = "Provider=" & _
                    "xxx" & _
                    "xxx" & _
                    "xxx" & _
                    "xxx;"
cn.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "begin proc_name ('" + Range("A1") + "') ;"
cmd.CommandType = adCmdText
cmd.CommandTimeout = 20
cmd.Execute
cn.Close
Set cn = Nothing
Set cmd = Nothing

Stored procedure:

create or replace procedure proc_name (prm_dt date)

Help solve the problem. Thanks.

Answer 1

Problem is the Date value and wrong syntax.

Try this one:

cmd.CommandText = "begin proc_name (TO_DATE('" & Format(Range("A1").Value, "yyyy-mm-dd") & "','YYYY-MM-DD')) ; END; "

Or even better try prepared statements with bind parameter, like this:

cmd.CommandText = "begin proc_name(?); END;"
cmd.Parameters.Append cmd.CreateParameter("prm_dt", adDate, adParamInput, , Range("A1").Value)

Or try this syntax: cmd.CommandText = "{CALL proc_name(?)}"