SQLLDR Date and Time formats

[Welshbird]

ARRG!!!!

Sorry, just needed to do this. I have a file to load into Oracle and am using SQLLDR.

The original file looks like this:

M11502†339923†M11502143629†269382†M11502016266†M†1923†MIC391†2079†DAK10†2079†C†2006-10-10 14:14:00.000†1†30†30†30†B†M†E
M11502†980189†M11502143630†269383†M11502015981†M†2006†EPA200†4909†EPA200†4909†A†2006-10-10 14:30:00.000†1†500†500†500†MU†M†E

and I am using a SQLLDR control file like this:

        load data
          INFILE 'C:\Documents and Settings\fwilliams\My Documents\Enigma\QA_Extract\RawFiles\fee.tab' 
          Append
          into table RX_ITEM
          FIELDS TERMINATED BY '†' 
          OPTIONALLY  ENCLOSED BY '"' 
          TRAILING NULLCOLS
						(RXI_PHA_KEY,            
							RXI_PXR_KEY,            
							RXI_FORM_KEY,           
							RXI_ITEM_KEY,           
							RXI_PATI_KEY,           
							RXI_PATI_SEX,           
							RXI_PATI_YOB,           
							RXI_PRES_KEY,           
							RXI_PRES_FAMILY,        
							RXI_DISP_KEY,           
							RXI_DISP_FAMILY,        
							RXI_EXEMPT,             
							RXI_DATE  "to_date(Trim(:RXI_DATE),'YYYY-MM-DD HH24:MI:SS')",
							RXI_RXT_KEY,            
							RXI_WRITTEN_QTY_C,      
							RXI_WRITTEN_QTY_N,      
							RXI_DISP_QTY,           
							RXI_LABEL_TXT,          
							RXI_SOURCE,             
							RXI_REG_KEY,            
							RXI_DATE_LOADED sysdate)

The problem is the RXI_DATE field.

In my data it looks like this: 2006-10-10 14:14:00.000
or very occasionally like this: 2006-10-10 00:00:00.000

I need to load these into a DATE column in Oracle, and no matter what I try it seems to fail.

I tried using the date format as "YYYY-MM-DD HH25:MI:SS.FFF" and then I thought I remembered that DATE does not use fractions of seconds...

I'm just getting to my wits end here, so I'd really appreciate if someone can notice what I am obviosuly missing...

Oh, the destination table looks like this:

 RXI_PHA_KEY                                                       NOT NULL VARCHAR2(50)
 RXI_PXR_KEY                                                                VARCHAR2(50)
 RXI_FORM_KEY                                                      NOT NULL VARCHAR2(20)
 RXI_ITEM_KEY                                                      NOT NULL NUMBER(38)
 RXI_PATI_KEY                                                      NOT NULL VARCHAR2(50)
 RXI_PATI_SEX                                                               CHAR(1)
 RXI_PATI_YOB                                                               VARCHAR2(4)
 RXI_PRES_KEY                                                      NOT NULL VARCHAR2(50)
 RXI_PRES_FAMILY                                                            VARCHAR2(20)
 RXI_DISP_KEY                                                      NOT NULL VARCHAR2(50)
 RXI_DISP_FAMILY                                                            VARCHAR2(20)
 RXI_EXEMPT                                                                 VARCHAR2(50)
 RXI_DATE                                                          NOT NULL DATE
 RXI_RXT_KEY                                                       NOT NULL VARCHAR2(50)
 RXI_WRITTEN_QTY_C                                                          VARCHAR2(20)
 RXI_WRITTEN_QTY_N                                                 NOT NULL NUMBER
 RXI_DISP_QTY                                                      NOT NULL NUMBER
 RXI_LABEL_TXT                                                              VARCHAR2(255)
 RXI_SOURCE                                                        NOT NULL VARCHAR2(20)
 RXI_REG_KEY                                                       NOT NULL VARCHAR2(50)
 RXI_DATE_LOADED                                                            DATE

Today my signature should have added: And does anyone else know how to do it?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[oradba101]

Hi, willif

I don't believe the TRIM function will work with DATE datatypes.

Regards,



William Chadbourne
Oracle DBA

 

[Welshbird]

OK - I'll try something else then.

Shoudl I be trying to trim this somehow - as I'm fairly sure I need to get rid of the milliseconds...??

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

I tried changing the bit of the control file as below:

RXI_DATE  "to_date(:RXI_DATE,'YYYY-MM-DD HH24:MI:SS')",

and I get the error message:
Record 1: Rejected - Error on table RX_ITEM, column RXI_DATE.
ORA-01830: date format picture ends before converting entire input string
for all rows.

I guess this makes sense as the data looks like it should be:
"YYYY-MM-DD HH24:MI:SS.FFF", so I guess what I need to do is to trim off the milliseconds? Is that right?

I'm losing the will to live 'ere! And I can't find a good website to explain it all too me. If anyone can point me at a (good - and complete!) web resource for SQLLDR I'd be most grateful, and would happily have a go myself to solve it.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

DOH!

RXI_DATE  timestamp "YYYY-MM-DD HH24:MI:SS.FF",

TOO MANY F'in F's!

Loading now.....

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]