The Oracle dictionary view NLS_SESSION_PARAMETERS is helpful too. I
know this available on Oracle 9i, but I don't know if it's available on
Oracle 8i. Oracle 8i docs are not available on OTN because its not
supported by Oracle anymore.
SELECT VALUE
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT';
Daniel Morgan wrote:
I told you a fib. Oracle's default date format is 'DD-MON-RR' which
is "dd-MMM-yy" for System.DataTime. I know this works since I
successfully inserted a row using an OracleParameter.
Also, here is a list of books for Oracle 9i at Oracle Technology
Network. You need to be registered to use OTN. If you are not
registered (its free), I strongly suggest you do.
List of Oracle 9i books on OTN:
http://www.oracle.com/pls/db92/db92.docindex?remark=homepage
Oracle 9i SQL Reference - Format Models
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements4a.htm#34512
In .NET 1.1 Framework Help, Search for "format specifiers, date and
time format strings" and choose Custom DateTime Format Strings.
Or on the web at MSDN Library:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
Right now, I am working on converting the Oracle Date format string to
Mono's System.DateTime format string and vice-versa. This code will
end up in OracleDateTime as internal static functions. I will be
using OCINlsGetInfo since it works for me. OCINlsGetInfo will be
located in OracleConnection as an internal function. I will try to
commit to svn a working version (not complete) tonight.
Daniel Morgan wrote:
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"
_______________________________________________
Mono-list maillist - [email protected]
http://lists.ximian.com/mailman/listinfo/mono-list
