Re: Passing date value from VB to Oracle Stored Procedure

Melinda,

You need to give error messages and code fro me to be certain of my answer. Without knowing more than you wrote the only thing obvious is that you don't have single quotes on the vb_Date below.

You would not pass a numeric date value as the 1st parameter for "to_date", but insead you need to pass something like below assuming you are using RDO's Execute method against an rdoConnection:

   const NLS_DATE_FORMAT = "'DD-MON-YYYY HH24:MI:SS'"

    pConn.Execute "Begin " & vbcrlf _ &

                             "    Execute spName( " & vbcrlf & _
                             "         to_date('24-MAR-1998 22:45:10', " & _
                                                     NLS_DATE_FORMAT & ");" &
vbcrlf & _
                             "End; "

If you were failing to place the single quotes you'd get a message about format and not matching the length. It appareantly reads character by character of the format in to_date and the date string. You can specify any two analogous formats between VB and oracle. Above your vbFormat would have been format(vbDate, "'DD-MMM-YYYY HH:NN:SS'". Note the single quotes in this string and the constant above.

It is a good idea to have a function parse these for you using global constants for both the vb and oracle format strings.

Function fsOraDate(ByVal pDate as Date, Optional pFormat as String = NLS_DATE_FORMAT) as String
So, "to_date('03/24/98', 'MM/DD/YY')" would be had the result of fsOraDate(Now, "mm/dd/yy")

I'll leave the rest for you. Let me know how you make out.

Of course, if you use an rdoQuery object and its rdoParameters collection you need not format the date, but can instead use date fields to describe the rdoParameter. Since you mentioned the to_date function, I assume you are not doing that.

Bob

Melinda Schall wrote:

> Our developers are trying to pass a date value from VB to an Oracle Stored
> Procedure and it is not working. The stored procedure's input parameter is
> defined as DATE. The VB code wants to pass the date format of MM/DD/YYYY.
> I've tried having them pass the value as a string containing the to_date
> function, i.e. to_date(vb_date, 'MM/DD/YYYY'), to the stored procedure but
> this fails as well. It seems like there must be a simple/obvious thing
> wrong...but it's eluding me and the other database developers here.
>
> Thanks in advance for any information.
Received on Tue Mar 24 1998 - 00:00:00 CST