[Mono-list] Problem with datetime format and oracle...

[Daniel Morgan]

The default date format for Oracle is 'DD-MMM-YY'.  Example: '13-FEB-02'

The fix needs to be able to handle situations where the date format 
could be changed via ALTER SESSION SET NLS_DATE_FORMAT, such as,

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

I have some ideas on how to solve it:
1. OCINlsGetInfo can be used to get the date format.  However, once I 
got this, I would need to reformat the date from OracleDateTime or 
DateTime to Oracle's date and vice-vesa.
2. (I haven't tried this yet - create DllImport's for OCI functions 
OCIDateTimeToText and OCIDateTimeFromText and calls these functions with 
an explicit
    date format string.  Oracle's OCIDate can stay as an IntPtr and use 
other OCI functions to deal with it. 
3. There are even OCI functions to put the OCIDate into a byte array or 
get the OCIDate from a byte array.   OCIDateTimeFromArray and 
OCIDateTimeToArray.  DllImport's are needed for these functions too.

More info can be found in Oracle Call Interface Programmer's Guide.

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96584/oci18m36.htm#512147
**

Here is working code so far...

        private string GetSessionNlsDateFormat()
        {
            byte[] buffer = new Byte[64];
            uint bufflen = (uint) buffer.Length;
            ushort item = (ushort) OciNlsServiceType.DATEFORMAT;
            IntPtr session = connection.Session;
            int st = OciCalls.OCINlsGetInfo (session, 
connection.ErrorHandle,
                ref buffer, bufflen, item);
            // Get length of returned string
            int     rsize = 0;
            IntPtr    env = connection.Environment;
            OciCalls.OCICharSetToUnicode (env, null, buffer, out rsize);
           
            // Get string
            StringBuilder ret = new StringBuilder(rsize);
            OciCalls.OCICharSetToUnicode (env, ret, buffer, out rsize);

            string nlsDateFormat = ret.ToString ();
            return nlsDateFormat;
        }

        internal static int OCINlsGetInfo (IntPtr hndl,
            IntPtr errhp,
            ref byte[] bufp,
            uint buflen,
            ushort item)
        {
            Trace.WriteLineIf(traceOci, "OCINlsGetInfo", "OCI");
            return OciNativeCalls.OCINlsGetInfo (hndl, errhp, bufp, 
buflen, item);
        }

            [DllImport ("oci")]
            internal static extern int OCINlsGetInfo (IntPtr hndl,
                IntPtr errhp,
                [In][Out] byte[] bufp,
                uint buflen,
                ushort item);




Hubert FONGARNAND wrote:

>I use mono to deal with an oracle database...
>the date format of my oracle database is "DD/MM/YY"
>but mono always send date like "DD/MM/YYYY"
>my NLS_LANG parameter is : NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1"
>
>
>  
>