From: KUBO Takehiro <kubo@...>
Date: 2003-05-02T22:55:16+09:00
Subject: Re: Some OCI8 comments

Brian Candler <B.Candler@pobox.com> writes:

> Actually, I have come across a problem here, trying to insert dates which
> were exported as CSV from another database; e.g. inserting string
> "2003-05-02 10:45:00" into an Oracle DATE column, I get:
>
> /usr/local/lib/ruby/site_ruby/1.6/DBD/OCI8/OCI8.rb:37:in `raise_error': ORA-01861: literal does not match format string (DBI::DatabaseError)
>
> Does anyone know what 'format string' Oracle is expecting? If I try
> "2003/05/02 10:45:00" it doesn't work either.

 How to check 'format string':
    SELECT value FROM v$nls_parameters WHERE parameter = 'NLS_DATE_FORMAT';
 Default date format of Oracle is 'DD-MON-RR'.

> Of course I can insert
>    TO_DATE(?,'YYYY-MM-DD HH24:MI:SS')

 Or you can change 'format string' by the following SQL:
    ALTER session SET nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
   or
    ALTER session SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

> P.S. I see that the OCI8 DBD in ruby-oci8-0.1.3 supports mapping of Oracle
> error codes to the appropriate DBI exceptions - excellent!

Oracle error codes are too many. I just cover a little of them.
You can see all of the Oracle error codes on $ORACLE_HOME/rdbms/mesg/oraus.msg.

Cheers
-- 
KUBO Takehiro