You are currently offline, waiting for your internet to reconnect

How To Pass a Date Value to a Oracle Stored Procedure

This article was previously published under Q177358
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
This article gives an example of passing a Date datatype value to an Oraclestored procedure and getting back a result from the stored procedure. Theexample below uses RDO 2.0, and the second version, 2.73.7253, of theMicrosoft Oracle ODBC driver. Note that the first version of the MicrosoftOracle ODBC driver, Ver 2.00.006325, does not support passing dates to astored procedure.

This example also demonstrates how the ODBC will take care of convertingthe Visual Basic Date to the acceptable Oracle Date format.
MORE INFORMATION
Create a new project in Visual Basic and reference the Microsoft RemoteData Object 2.0 under Project/References.
  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;   / 					
Run the project by pressing the F5 key and, when prompted, enter a date inany Visual Basic-acceptable date format that is greater than Jan 1, 1983 toget a count value of all records in the EMP table.

Additional Information:
  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.
The return should be a count of 14 and the date that was entered instandard military format of DD-MON-YYYY.
REFERENCES
For additional information, please see the following article in theMicrosoft Knowledge Base:
167225 How To Access an Oracle Database Using RDO
Properties

Article ID: 177358 - Last Review: 12/05/2015 08:11:54 - Revision: 1.2

  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • kbnosurvey kbarchive kbhowto kboracle kbrdo KB177358
Feedback