Calling Oracle 11g Stored Procedure Using VB6

Question

I have a simple Oracle procedure as below. I am trying to call the procedure using VB6 and extract the output from the procedure.

CREATE OR REPLACE PROCEDURE EXTRACTTXN (reportdate IN DATE, p_recordset OUT SYS_REFCURSOR) AS
BEGIN
 OPEN p_recordset FOR
SELECT 
    TXN_ID,
    TXN_ACTION,
    TXN_STATUS,
    TXN_DATE,
    TXN_AMOUNT
FROM TRANSACTIONS
WHERE
    TRUNC(TXN_DATE) = TRUNC(reportdate)
END EXTRACTTXN;

The VB Code goes like this;

Sub FetchTransactions(ByVal ReportDate As Date, cnnMine as ADODB.Connection)
  On Error GoTo TrapErr
  Dim cmdMine As ADODB.Command, rsMine As ADODB.Recordset

  cmdMine.ActiveConnection = cnnMine
  cmdMine.CommandTimeout = 300
  cmdMine.CommandType = adCmdStoredProc
  cmdMine.CommandText = "EXTRACTTXN"

  cmdMine.Parameters.Append cmdMine.CreateParameter("reportdate", adDate, adParamInput, , Format(ReportDate, "DD-MMM-YYYY"))
  cmdMine.Parameters.Append cmdMine.CreateParameter("p_recordset", adVariant, adParamOutput)
  Set rsMine = cmdMine.Execute

  Do While rsMine.EOF
      Debug.Print rsMine!TXN_ID, rsMine!TXN_ACTION, rsMine!TXN_STATUS, rsMine!TXN_DATE, rsMine!TXN_AMOUNT
      rsMine.MoveNext
  Loop
  rsMine.Close

  Exit Sub
TrapErr:
  MsgBox Err.Number & " - " & Err.Description, vbExclamation, App.ProductName
End Sub

While running the code, I get the following Error:

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'EXTRACTTXN' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Anything wrong in my code? Appreciate help. Niz

Answer 1

The problem is that the types of your arguments as specified in your VB code don't match the types of the arguments as specified in your PL/SQL code. The most likely reason for your problem is that the Format function in VB6 returns a Variant type, not a Date type, and that Variant type is set to be a String type. See this for more information on the Format function.

In case you don't know, the way that Variant variables are set up is that they reserve 8 bytes to tell the world what the actual variable type is. So, if you pass your ReportDate in after applying the Format function to it, it will be a Variant that's telling the world it's a string. It's possible that the ADO Parameter object is happy with that (SQL Server is happy to parse properly-formatted strings into Date fields, after all) and Oracle isn't. In my limited experience with Oracle, I've found that it's fussier about that sort of thing than SQL Server.

Try losing the Format function and see if you at least get a different error.

Answer 2

I have managed to get this sorted. It's mainly due to me being new to Oracle and its complexity.

Here are the changes I made;

1. Stored Procedure Changes. Note that I have changed TRUNC(reportdate, 'DD') on the Where clause.

   CREATE OR REPLACE PROCEDURE EXTRACTTXN (reportdate IN DATE, p_recordset OUT SYS_REFCURSOR) AS
   BEGIN
   OPEN p_recordset FOR
   SELECT 
      TXN_ID,
      TXN_ACTION,
      TXN_STATUS,
      TXN_DATE,
      TXN_AMOUNT
   FROM TRANSACTIONS
   WHERE
       TRUNC(TXN_DATE) = TRUNC(reportdate, 'DD')
   END EXTRACTTXN;
   

2. VB Code Changes (Note that I have change the CommandText within parenthesis with a Call, removed the parameter name, changed the date format to DD/MMM/YYYY and removed the output parameter)

   Sub FetchTransactions(ByVal ReportDate As Date, cnnMine as ADODB.Connection)
       On Error GoTo TrapErr
       Dim cmdMine As ADODB.Command, rsMine As ADODB.Recordset
   
       cmdMine.ActiveConnection = cnnMine
       cmdMine.CommandTimeout = 300
       cmdMine.CommandType = adCmdStoredProc
       cmdMine.CommandText = "{ call EXTRACTTXN}"
   
       cmdMine.Parameters.Append cmdMine.CreateParameter(, adDate, adParamInput, , Format(ReportDate, "DD/MMM/YYYY"))
       Set rsMine = cmdMine.Execute
   
       Do While rsMine.EOF
           Debug.Print rsMine!TXN_ID, rsMine!TXN_ACTION, rsMine!TXN_STATUS, rsMine!TXN_DATE, rsMine!TXN_AMOUNT
           rsMine.MoveNext
       Loop
       rsMine.Close
   
       Exit Sub
     TrapErr:
       MsgBox Err.Number & " - " & Err.Description, vbExclamation, App.ProductName
     End Sub
   

The above worked perfectly.

Regards, Niz