Re: SQL Loader and Date Conversions?

In article <89lvq1$jsb$1_at_nnrp1.deja.com>,   liam_2e_at_my-deja.com wrote:
> here is the actual control file that I am using I goofed up and didn't
> post it before.
>
> load data
> infile 'd:\dataload\DAILY_SALES.out'
> into table daily_sales
> (amount_ordered position (1:2) char,
> purchase_date position (11:21) date 'mmm dd yyyy',
> product_id position (29:40) char,
> act_type position (51) char)
>
> and it seems to work but now I get the error message
>
> Commit point reached - logical record count 1057
>
> and then when i go to sql plus and look in the table no data was
> loaded! I tried using rows=100 in my command line ex: sqlldr user/pass
> rows-100 to see if this would commit after 100 rows but it gives me
> the same error message it just says 100.
> Thanks for all the help
> Liam
>

and what does the .log file have in it?

I believe you'll find it full of:

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
AMOUNT_ORDERED                        1:2     2           CHARACTER
PURCHASE_DATE                       11:21    11           DATE mmm dd
yyyy
PRODUCT_ID                          29:40    12           CHARACTER
ACT_TYPE                               51     1           CHARACTER

Record 1: Rejected - Error on table DAILY_SALES, column PURCHASE_DATE. ORA-01821: date format not recognized

the format you are using, mmm dd yyyy, is not correct. it should be mon dd yyyy

[snip]

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.