Re: I cant understand the DATE values stored in V$SQL_BIND_CAPTURE table (Oracle 10.1.0.4 and up)!

On Jul 3, 7:03 am, yoav.ora..._at_gmail.com wrote:
> I am trying to read the values stored in V$SQL_BIND_CAPTURE. For
> string and numeric values, everything is OK but when I look into DATE
> values I get confused. Here is the query I am running:
>
> SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE
> DATATYPE_STRING='DATE';
>
> It seems that no matter what the default date format is or what the
> NLS_LANG is,
> the values are stored in MM/DD/YY HH24:MI:SS format.
>
> I couldn't find any documentation for this. I am looking for 2
> answers:
>
> 1.In case you have a non-English instance (from SQL*PLUS write "show
> parameter nls_lang"
> and verify it is not AMERICAN), do you get the same format for
> DATEs stroed in
> $SQL_BIND_CAPTURE?
> 2. Do you know what the Oracle expected behavior is?
>
> Any help would be appreciated.
>
> Thanks
> Yoav

SQL> desc V$SQL_BIND_CAPTURE

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------

<snip>
 VALUE_STRING                                       VARCHAR2(4000)
 VALUE_ANYDATA                                      SYS.ANYDATA

The column VALUE_STRING is not a date, it is a character string. Doesn't matter that the character string it contains may *look* like a date .... Received on Tue Jul 03 2007 - 07:17:44 CDT