How To Pass a Date Value to a Oracle Stored Procedure

MORE INFORMATION

1. On the new Form add a CommandButton.
2. Paste the following code in the General Declarations section of the Form:

      Option Explicit
      Dim Cn As New rdoConnection
      Dim En As rdoEnvironment
      Dim Qd As New rdoQuery
      Dim Rs As rdoResultset
      Dim Conn As String
      Dim tempcnt As Integer
   
      Private Sub Command1_Click()
   
          Dim strDate, strQd$, strQd1$
          strDate = InputBox("Enter the date you wish to retrieve:")
          Qd(0) = CDate(strDate)
          Qd.Execute
          strQd = Mid(Qd(1), 12)
          strQd1 = Mid(Qd(1), 1, 11)
          Debug.Print "For the date you entered: " & strQd1 & vbCrLf & strQd
          MsgBox "For the date you entered: " & strQd1 & vbCrLf & strQd
   
      End Sub
   
      Private Sub Form_Load()
   
          Command1.Caption = "Start"
          'Change each <> field to the appropriate value for your system
          Conn = "UID=<uid>;PWD=<password>;" _
          & "driver={Microsoft ODBC for Oracle};SERVER=<server>;"
   
          Set En = rdoEnvironments(0)
          En.CursorDriver = rdUseOdbc
          With Cn
              .Connect = Conn
              .CursorDriver = rdUseOdbc
              .EstablishConnection rdDriverNoPrompt, False
          End With
   
          Set Qd.ActiveConnection = Cn
   
          Qd.SQL = "{call testdate1(?,?)}"
          Qd(0).Direction = rdParamInput
          Qd(1).Direction = rdParamOutput
          Qd(0).Type = rdTypeDATE
   
      End Sub
   
      Private Sub Form_Unload(Cancel As Integer)
          Qd.Close
          Cn.Close
          En.Close
   
      End Sub
   
   					

3. Create an Oracle stored procedure using this script:

      CREATE OR REPLACE PROCEDURE TESTDATE1(
   
      /* This is called by a Visual Basic project that passes in */ 
      /* a Date parameter in any Visual Basic-acceptable format and */ 
      /* returns a Count value and the input date in */ 
      /* a different format */ 
   
         INDATE IN DATE,
         OUTSTR OUT VARCHAR2 )
   
      IS
   
         m_Count NUMBER;
   
      BEGIN
         SELECT
         COUNT(*) INTO m_Count
         FROM EMP WHERE HIREDATE < INDATE;
   
         OUTSTR:=TO_CHAR(INDATE,'DD-Mon-YYYY') ||
         'The Count is: '|| TO_CHAR(m_Count);
   
      END;
      / 
   
   					

1. This stored procedure uses the DEMO account EMP table supplied in a standard Oracle 7.3 installation.
2. This demonstrates that the Microsoft Oracle ODBC driver will handle Date conversion from an acceptable Visual Basic format to an acceptable Oracle format.