Tom,

What would you do if you were given a sql loader file with the dates of the following formats?

1/10/2001 16:39:22
02/1/2001 10:12:33
01/22/2000 21:00:19

Tom,

First of all, Thank You!

We assumed the date formats were causing the problem. However, this date column had about 7,000 values of #EMPTY out of the 600,000 + rows. Since the file was over 200mb we decided to load it into all varchars, replace the invalid values with nulls and then insert into the target table with the date format as you suggested.

Loading from a pipe seems like a terrific way to Direct Loader (still the best enhancement ever) on real world files

And in 9i -- you can direct path load using SQL functions as well -- never a need to "awk" the file again (and probably lets you just skip that pipe in 99.99% of the cases)

$ cat t.ctl
LOAD DATA
INFILE t.dat
INTO TABLE NXBenefit
FIELDS TERMINATED BY ","
(plan,
 effective "todate_pkg.thedate(:effective)"
)

$ cat t.dat
dental plan, 01-11-01
vision plan, 03/05/01
vision plan, 03.05.01
vision plan, jan-01-01
vision plan, 01-jan-01
vision plan, 01-jan-2001

$ sqlldr / t direct=true

SQL*Loader: Release 9.0.1.2.0 - Production on Tue Apr 9 19:10:26 2002

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Load completed - logical record count 6.

$ plus

SQL*Plus: Release 9.0.1.0.0 - Production on Tue Apr 9 19:10:30 2002

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.2.0 - Production
With the Partitioning option
JServer Release 9.0.1.2.0 - Production

ops$tkyte@ORA9I.WORLD> select * from nxbenefit
  2  /

PLAN                      EFFECTIVE
------------------------- ---------
dental plan               01-NOV-01
vision plan               03-MAY-01
vision plan               03-MAY-01
vision plan               01-JAN-01
vision plan               01-JAN-01
vision plan               01-JAN-01

6 rows selected.


External tables take this to a WHOLE another level -- don't even need sqlldr!  Just insert /*+ append */ into table select * from flat_file -- direct path load without the muss. 

Hi Tom,

I have a strange doubt and you are the only one i can fall back on. thanks for all the support.

I am generating a temp.dat file by doing a 'select * ' from a particular query. A couple of columns there will have data in 7 decimals (999999.1234567). when i upload that temp.dat file using sql*loader (positional), SQL*loader is rounding off the 7 decimals to 4 decimals while loading the data on to the table. What could be the problem???.

The desc of table structure is below for that column.
DURATION NOT NULL NUMBER(8,7)

Thanks for all the support.

Cheers

example please (you are not using 999999.1234567 -- that won't fit in a number(8,7).

Here is my example:

ops$tkyte@ORA920.LOCALHOST> create table t ( n number(8,7) );
Table created.

ops$tkyte@ORA920.LOCALHOST> !cat t.ctl
LOAD DATA
INFILE *
INTO TABLE T
FIELDS TERMINATED BY ","
(n)
BEGINDATA
999999.1234567
9.1234567

ops$tkyte@ORA920.LOCALHOST> !sqlldr / t

SQL*Loader: Release 9.2.0.1.0 - Production on Wed Nov 13 10:56:01 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 2

ops$tkyte@ORA920.LOCALHOST> select * from t;

         N
----------
 9.1234567

ops$tkyte@ORA920.LOCALHOST>


<b>one row rejected -- the other loaded just fine.  A thought I have is that it is not a number(8,7) but something bigger and you are loading 99999.1234567 and SQLPlus's default format isn't showing you all of the decimals.  consider:</b>



ops$tkyte@ORA920.LOCALHOST> create table t ( n number );
Table created.

ops$tkyte@ORA920.LOCALHOST> !sqlldr / t

SQL*Loader: Release 9.2.0.1.0 - Production on Wed Nov 13 10:57:18 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 2

ops$tkyte@ORA920.LOCALHOST> select * from t;

         N
----------
999999.123
 9.1234567

<b>appears truncated, but it is not:</b>

ops$tkyte@ORA920.LOCALHOST> set numformat 9999999.999999999
ops$tkyte@ORA920.LOCALHOST> /

                 N
------------------
  999999.123456700
       9.123456700

ops$tkyte@ORA920.LOCALHOST> 

Quote:

"
21 exception
22 when others then null;
23 end;
"

Who are you, and what have you done with the real Tom? You know, the one who wrote ..."I truly wish we didn't even support WHEN OTHERS"?

Be careful what you wish for ;)

Thanks Tom this was one of my Question which I am waiting from last 3 months to ask you.

Tom

I have a file which has rows with the date in diff formats

12/9/02 16:29 3125 3000 3001 302
13/9/02 11:21 3000 3001 3002 400
14/9/02 09:53:11 AM 3000 3001 3002 400

Your solution is brilliant

Sorry, I showed the next column. The problematic columns are event_data_time,start_date_time and not duration one.
Here is the example.

tigger:~/sched> sqlplus /

SQL*Plus: Release 8.1.6.0.0 - Production on Fri Nov 15 14:03:25 2002

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Release 8.1.6.0.0 - Production
JServer Release 8.1.6.0.0 - Production

system@SKYNEW on tig> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
SERVICE_KEY NOT NULL NUMBER(4)
EVENT_DATE_TIME NOT NULL NUMBER(12,7)
EVENT_NUM NOT NULL NUMBER(1)
TRAFFIC_KEY VARCHAR2(20)
SYNC_CHAN_ID NOT NULL NUMBER(4)
START_DATE_TIME NOT NULL NUMBER(12,7)
DURATION NOT NULL NUMBER(8,7)
DETAIL_KEY NOT NULL VARCHAR2(20)
EVENT_ID NUMBER(5)
VIDEOPLUS_CODE NUMBER(16)
CA_RQST_KEY VARCHAR2(20)
OPPV_PURCHASE_FLAG NUMBER(1)
IPPV_PURCHASE_FLAG NUMBER(1)
FECM_ID NUMBER(5)
FREE_CA_MODE NOT NULL NUMBER(1)
NAME_ID NUMBER(3)


system@SKYNEW on tig> !cat test.ctl
load data
infile *
into table ssr.test
fields terminated by ","
trailing nullcols
(service_key ,
event_date_time,
event_num ,
traffic_key ,
sync_chan_id ,
start_date_time ,
duration ,
detail_key ,
event_id ,
videoplus_code ,
ca_rqst_key ,
oppv_purchase_flag,
ippv_purchase_flag,
fecm_id ,
free_ca_mode )
BEGINDATA
2,52592.8437444,0,0,0,52592.8437444,00000000.0104166,2_82,,,,,,,0
2,52592.8541611,0,0,0,52592.8541611,00000000.0104166,2_83,,,,,,,0


system@SKYNEW on tig> !sqlldr / test

SQL*Loader: Release 8.1.6.0.0 - Production on Fri Nov 15 14:05:21 2002

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Commit point reached - logical record count 2


system@SKYNEW on tig> select * from ssr.test;

SERVICE_KEY EVENT_DATE_TIME EVENT_NUM TRAFFIC_KEY SYNC_CHAN_ID START_DATE_TIME DURATION DETAIL_KEY EVENT_ID VIDEOPLUS_CODE CA_RQST_KEY OPPV_PURCHASE_FLAG IPPV_PURCHASE_FLAG FECM_ID FREE_CA_MODE NAME_ID
-------------- ------------------ ------------ -------------------- ------------ --------------- ---------- -------------------- ----------- -------------- -------------------- ------------------ ------------------ ---------- ------------ ----------
2 52592.8437 0 0 0 52592.8437 .0104166 2_82 259 0
2 52592.8542 0 0 0 52592.8542 .0104166 2_83 260 0

2 rows selected.



The columns have rounded off to four decimals. Am I missing something. Any clues.

Cheers,

Dear Tom, actually i'm loading a flat file via text i/o from forms but is too slow, can u help me to make a ctl file who can handle this format in a flat file

12/01/02 00:15;414;0;303;0;0;0;0;0
00:30;420;0;318;0;0;0;0;0
00:45;432;0;318;0;0;0;0;0
01:00;372;0;309;0;0;0;0;0
....
23:30;159;0;177;0;0;0;0;0
23:45;165;0;165;0;0;0;0;0
12/01/02 24:00;168;0;183;0;0;0;0;0
12/02/02 00:15;159;0;162;0;0;0;0;0
00:30;156;0;117;0;0;0;0;0

where the time header is the day until change the header as u can see
Thanks in advance

Well here is a 96 record ina day for a month 2976 aprox but here is a 194 files to load every one belongs to a specific electronic meter

i have 2976 records in every flat file
I have too 194 flat files to load every month

I mean every month i receive the 194 flat files then i have to upload it to the db. ( oracle 8i standard edition ).

Thanks Tom...!!!

Very useful...!!

Hi Tom,
My load file contains one header record (the name of the file), and one trailor (the number of records and the checksum). I would like load using 9i running SQL on external files (not loading the header and trailor but checking the name of the file and checksum). Would this be possible? Please give an idea, how best to do this, the file contains about 20 million records.
Thanks

In 8.0.6, this function is throwing a ORA-06571, which instructs me to use the pragma restrict references. I'm having a tough time placing the pragma. Can you tell me a) will this package work in 8.0.6. and b) if I need the pragma, where does it go?

As always, thanks

I have rrrr-mm-dd, mm-dd-rr, m-dd-rr, mm-d-rr, etc. And I can't seem to get a complete ist into the array. I you mask 4/12/2003 with dd/mm/rrrr, won't that reject?

I was asking about single-digit days and single-digit months. I wanted to know if both 1/1/2003 and 01/01/2003 could be handled with a mask like mm-dd-yyyy.

I've moved past that, though, to other problems to solve!

Thanks.

Hi there...

Can we use group functions in the control file?
Suppose we are inserting data in 2 tables through a conrol file. (Please consider the emp table for eg) In the first insert statement the data is coming from some emp.dat file. And in the second I want to have the sum of salaries entered for all the employees in the emp table. Is this possible to achieve through a Control File?
I am citing an example :-

OPTIONS
(ERRORS = 50)
LOAD DATA
INFILE "D:\Emp.dat"
APPEND
INTO TABLE EMP
(EMPNO "EMP_SEQ.NEXTVAL"
SALARY POSITION(1:10) INTEGER
FILE_NM CONSTANT 'EMP.DAT')
INTO TABLE TRAILER
** (TOTAL_SALARY "SELECT SUM(SALARY) FROM EMP WHERE
FILE_NM='EMP.DAT'"
FILE_NM CONSTANT 'EMP.DAT'
PRCSS_DT SYSDATE)

You can easily see that I am getting stuck in '**' marked line. How to incorporate this?
Please explain.

Hi

I extracted two lines from your examples

that_column date "dd/mm/yyyy hh24:mi:ss", 01/22/2000 21:00:19

effective "todate_pkg.thedate(:effective)"

why in the first column we have to specify data and the second not when both of them use date datatype???????

the second does NOT use a date:

ops$tkyte@ORA8I.WORLD> create or replace package todate_pkg
  2  as
  3      function thedate
  4               ( p_string in varchar2 )
  5               return date;
  6  end;


theDate function expects a string. 

Hello

I have a question about this package

create or replace package todate_pkg
as
      function thedate
               ( p_string in varchar2 )
               return date;
end;
/

create or replace package body todate_pkg
as
  type fmtArray is table of varchar2(30);
  g_fmts fmtArray :=
         fmtArray( 'dd-mm-rr', 'dd/mm/rr',
                   'dd.mm.rr', 'mon-dd-rr',
                   'dd-mon-rrrr' );
  function thedate( p_string in varchar2 )
  return date
  is
      return_value date;
  begin
      for i in 1 .. g_fmts.count
      loop
      begin
          return_value := to_date(p_string,g_fmts(i) );
          exit;
      exception
          when others then null;
      end;
      end loop;
      if ( return_value is null ) then
          raise PROGRAM_ERROR;
      end if;
      return return_value;
  end;
end;
/


it seems it only treats input string of formats 

'dd-mm-rr', 'dd/mm/rr', 'dd.mm.rr', 'mon-dd-rr', 'dd-mon-rrrr'

however I pass anything to this package it can comvert to proper date format how so? For example

SQL>select todate_pkg.thedate('01@01@99') from dual;

TODATE_PKG.THEDAT
-----------------
19990101 00:00:00

SQL>select todate_pkg.thedate('01012003') from dual;

TODATE_PKG.THEDAT
-----------------
20030101 00:00:00



I dont get it....... also why you use an ext statement inside the loop :-? 

because those formats are rather forgiving


  1* select to_date( '01~01~99', 'dd/mm/rr' ) from dual
ops$tkyte@ORA817DEV> /

TO_DATE('01
-----------
01-JAN-1999



as far as separators go.

I exit the loop as soon as we get a format that "worked".  If I did not, we would either

o return the date as translated by the last format
o return an error

if any of the intermediate formats worked, we would miss that fact if I did not exit the loop. 

Hi Tom,

I am a Avid fan of your excellent service to Oracle developer community, even though this is my first post. I have a doubt, even though i am not facing this problem, For example, if the flat file has records with formats like this

Date
----------
08-13-2003 --------------> mm-dd-yyyy
12-30-2003 --------------> dd-mm-yyyy

Is there any way by which I can identify the format and use it in the sql*loader control file ? Just asking out of curiosity !!! I hope you have a answer to this question (even though it sounds silly ;-)!)

Anand

The format mask has to be interchanged between the first and second records.. sorry for the inconvenience..

Hi Tom,

As i had mentioned earlier it is just a hypothetical situation...But cant that be handled within the function which we are creating and refering in SQL*Loader. May be like finding out what is the format in which the date is stored in... any way i agree that it will not work for dates like "12-12-2003" or dates like that... but otherwise... shouldnt there be a way ?

Tom,

i've got a load of data which we've got the 9i external table definitions for, but we need to load this into an 8i database. Is it possible to do the reverse of the sqlloader external_table=generate_only trick and create sqlloader control files from an external table definition?

Note, the 9i and 8i databases are seperate clients and we have no mechanism for database links etc. (unfortunately)

Thanks in advance

Tom,

We have asc files that have dates in this format: 050404
I added mmddyy to your package array. I thought everything was working nicely, but, I noticed that it's transposing certain dates -- for eg. 010604 was loaded as 06/01/2004, 110503 was loaded as 5/11/2003. I haven't fully diagnosed it, but, I think the problem occurs when the day begins with a zero. Any thoughts? Thanks for the help.

Thanks Tom

Our upload asc file records missing dates as MMMMMM, 
Our application records missing dates as 02/02/0202.  In my ctl file I'm using:

DATEFLD "replace(:dateintr, 'MMMMMM', to_date('02/02/0202', 'MM/DD/YYYY'))"  

these dates, however, are getting loaded as 02/02/2002.  

I'm confused because if I do this from sqlplus:


SQL> select to_date('02/02/0202', 'MM/DD/YYYY') from dual;

TO_DATE('0
----------
02/02/0202

what obvious thing am I missing here :)  Thanks Tom.
 

As usual, most helpful.

Hi Tom,

The above information was very helpful. In my need the coloumn is in timestamp with local time zone. i want to round the value to the date.
i.e '7/26/1999 8:47:30 AM' date should be rounded as 7/26/1999 and to be compared with the date & timestamp column for loading the data into flat file. Please guide me how to do that.

Tom I read your above reply , I have a question

MY data file consists of data like
2003-03-16 00:00:00
2003-04-14 00:00:00

My ctl file is like this
load data
INFILE 'lv2_circadian_rhythms.dat' "str '
@%@%@%@%@@^&$%;%$^&@'"
INTO TABLE LV2DTA.LV2_CIRCADIAN_RHYTHM
REPLACE
FIELDS TERMINATED BY '@^&$%;%$^&@'
TRAILING NULLCOLS
(
CREATED date 'yyyy-mm-dd HH24:MI:SS',
)

but when I load data in ORacle , my data is loaded
as 3/16/2004 . I want to load it as 3/16/2004 00:00:00

How can I do it ?

Thanks
Lamya

Tnaks a million tom ...it satisifed my manager

Tom,
I've got something wired happening when i use sqlldr to load data in WINDOWS and UNIX platforms.

(1)The defination of the table is:

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 ID                                                 NUMBER(10)
 TXT                                                VARCHAR2(30)
 DT                                                 TIMESTAMP(0)
 C                                                  CHAR(3)
 D                                                  DATE


(2)In UNIX platform, this is the Cut&Paste of my telnet session:
<<<
zhangdba@dwrdb1dv:/home/zhangdba/adhoc
->ls
ts.ctl  ts.dat

zhangdba@dwrdb1dv:/home/zhangdba/adhoc
->cat ts.dat
          1,Hello World,2004-06-01 10:11:12.000000,T,2004-06-01,
          2,Hello World,2004-06-01 10:11:12.000000,F,2004-06-01,

zhangdba@dwrdb1dv:/home/zhangdba/adhoc
->cat ts.ctl
LOAD DATA
INFILE 'ts.dat'
APPEND
INTO TABLE test
FIELDS TERMINATED BY ','
(
ID,
TXT,
DT "to_timestamp(substr(:DT, 1, 19), 'YYYY-MM-DD HH24:MI:SS')",
C,
D "CASE WHEN instr(:D, ':')>0
     THEN to_date(substr(:D, 1, 19), 'YYYY-MM-DD HH24:MI:SS')
     ELSE to_date(substr(:D, 1, 10), 'YYYY-MM-DD')
 END"
)


zhangdba@dwrdb1dv:/home/zhangdba/adhoc
->sqlldr userid=u/p@tns control=ts.ctl

SQL*Loader: Release 9.2.0.5.0 - Production on Fri Aug 13 17:34:31 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 2

zhangdba@dwrdb1dv:/home/zhangdba/adhoc
->ls
ts.bad  ts.ctl  ts.dat  ts.log

zhangdba@dwrdb1dv:/home/zhangdba/adhoc
->cat ts.bad
          1,Hello World,2004-06-01 10:11:12.000000,T,2004-06-01,
          2,Hello World,2004-06-01 10:11:12.000000,F,2004-06-01,

zhangdba@dwrdb1dv:/home/zhangdba/adhoc
->more ts.log
...
Record 1: Rejected - Error on table TEST, column DT.
ORA-01843: not a valid month

Record 2: Rejected - Error on table TEST, column DT.
ORA-01843: not a valid month


Table TEST:
  0 Rows successfully loaded.
  2 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

...
>>>

(3)In WINDOW's session, this is the cut&paste:
<<<
C:\DOCUME~1\JIANHU~1>dir t.*
 Volume in drive C has no label.
 Volume Serial Number is C829-8FDE

 Directory of C:\DOCUME~1\JIANHU~1

08/13/2004  05:35 PM               319 t.ctl
08/13/2004  05:13 PM               132 t.dat
               2 File(s)            451 bytes
               0 Dir(s)   6,168,063,488 bytes free

C:\DOCUME~1\JIANHU~1>type t.dat
          1,Hello World,2004-06-01 10:11:12.000000,T,2004-06-01,
          2,Hello World,2004-06-01 10:11:12.000000,F,2004-06-01,

C:\DOCUME~1\JIANHU~1>type t.ctl
LOAD DATA
INFILE 't.dat'
APPEND
INTO TABLE test
FIELDS TERMINATED BY ','
(
ID,
TXT,
DT "to_timestamp(substr(:DT, 1, 19), 'yyyy-mm-dd hh24:mi:ss')",
C,
D "CASE WHEN instr(:D, ':')>0
     THEN to_date(substr(:D, 1, 19), 'yyyy-mm-dd hh24:mi:ss')
     ELSE to_date(substr(:D, 1, 10), 'yyyy-mm-dd')
 END"
)


C:\DOCUME~1\JIANHU~1>sqlldr userid=u/p@tns control=t.ctl

SQL*Loader: Release 9.2.0.1.0 - Production on Fri Aug 13 17:36:38 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 2

C:\DOCUME~1\JIANHU~1>more t.log
...
Table TEST:
  2 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
...
>>>



So the question is why the load fails in unix but succeeds in WINDOW? The control file and the data file are the same(FTP them from UNIX to WINDOWS in ASC mode), could that be CHARACTER SET related? If so , how to fix it in UNIX? I tried to use SUBSTRC in UNIX, but not luck.

Thanks for your help as aways,
 

Tom,
here we go
<<<UNIX
->sqlldr userid=u/p@db control=test_all_varchar2.ctl

SQL*Loader: Release 9.2.0.5.0 - Production on Mon Aug 16 14:52:10 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 2

zhangdba@dwrdb1dv:/home/zhangdba/adhoc
->cat test_all_varchar2.ctl
LOAD DATA
INFILE 'ts.dat'
APPEND
INTO TABLE test_all_varchar2
FIELDS TERMINATED BY ','
(
ID,
TXT,
DT,
C,
D
)


zhangdba@dwrdb1dv:/home/zhangdba/adhoc
->cat ts.dat
          1,Hello World,2004-01-01 10:11:12.000000,T,2004-06-01,
          2,Hello World,2004-01-01 10:11:12.000000,F,2004-06-01,

END of UNIX>>>


<<< SQL*PLUS
SQL> desc test_all_varchar2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 VARCHAR2(30)
 TXT                                                VARCHAR2(30)
 DT                                                 VARCHAR2(30)
 C                                                  VARCHAR2(3)
 D                                                  VARCHAR2(30)

SQL>  select * from test_all_varchar2;

ID                             TXT
------------------------------ ------------------------------
DT                             C   D
------------------------------ --- ------------------------------
          1                    Hello World
2004-01-01 10:11:12.000000     T   2004-06-01

          2                    Hello World
2004-01-01 10:11:12.000000     F   2004-06-01


SQL> select dump(dt), dt from test_all_varchar2;

DUMP(DT)
--------------------------------------------------------------------------------
DT
------------------------------
Typ=1 Len=26: 50,48,48,52,45,48,49,45,48,49,32,49,48,58,49,49,58,49,50,46,48,48,
48,48,48,48
2004-01-01 10:11:12.000000

Typ=1 Len=26: 50,48,48,52,45,48,49,45,48,49,32,49,48,58,49,49,58,49,50,46,48,48,
48,48,48,48
2004-01-01 10:11:12.000000
END of SQL* PLUS>>>

really freaky, are you able to reproduce the error in your environment?
BTW, NLS_LANG is unset. 

I did couple tests and when i set NLS_LANG(=AMERICAN_AMERICA.UTF8) to the same as database character set, it succeeded. Which i unset NLS_LANG, it failed.

Tom,

I believe that in sqlldr direct=true, commit occurs only after all the rows are loaded.
so if for some reason my load fails in between, not a single record will get loaded.

Please correct me if i'm wrong.

Hi Tom,

The discussion is splendid. However, I am facing a rather peculiar problem. I have a delimited file and have to load its data into 9 different tables, for which I have written 9 different control files. One of the tables has a date column and the date in the INFILE is of the format MM/DD/YYYY hh24:mi:ss. But the strange thing is when I use

StatementDate Date "MM/DD/YYYY hh24:mi:ss"

I get the error message : Rejected - error on table xxxx , column cccc
ORA-01858: a non-numeric character was found where a numeric was expected.

SO, I thught, maybe the incoming date was not in the proper format and I tried using the to_date(to_char()) sql and I get the message as invalid number or sometimes, I get a invalid month or the hour should be between 0 and 23.

I assure you, I'm using only 10 records (as a sample), but stil get these errors. I don't know what's wrong. PLEASE HELP..........

All of the discussions and responses on this issue (as well as most others) have been helpful but here are 2 very basic questions on SQL Loader. In loading dates:
1. Via SQL Loader, is there any 'requirement' that the format include HH:MM:sec or can the date be loaded as MM/DD/YYYY, if that format is specified?
2. After the date has been loaded/updated/inserted in MM/DD/YYYY format, is there any storage savings vs. the long Oracle format which includes the time?

Thank you for a very enjoyable and useful site.

Tom,

I have data which contains date in unix timestamp "1115333572". I am trying to load and all of the data being rejected because of the date format. And same data I can load using sqlldr using date 'yyyymmdd hh:mm:ss"
in control file but not in external tables. Might be something very small but I don't know what I am missing. Help me out here.

Thanks
NR

Following up on SQL functions being possible in sqlldr 9i. True and almost never had a problem with them. But do you have any idea why the following happens on my 9.2.0.6 DB?

* USER_A has a table T. USER_A grants ALL on T to an T_UPDATE role.

* T_UPDATE role is granted to USER_B.

* USER_B can insert happily into USER_A.T.

* USER_B can direct path sql*load USER_A.T

* USER_B can conventional path sql*load USER_A.T with SQL functions in the .ctl script

* ...but, when USER_B tries to direct path sql*load USER_A.T with SQL functions in the .ctl script (9.0.1 new feature), the logfile shows insufficient privileges and every row is rejected (actually the 6 source rows generate 51 recursive SQL error messages in the log file).

* ...however, when the ALL on T is granted directly to USER_B, the problem goes away and the combination of table-in-other-schema, direct path and SQL functions works happily.

I can't find anything about this as a direct path restriction in the docs... Any clue? Bug? The logfile showing 51 rejects from a 6-record .dat file is a clue that something might be amiss.

Thanks

Tom,

I am getting this after I sql loaded into my table
2006-03-03¿¿07:32:46 how can I get rid of the space??

thanks,

I copy the text file from windows and paste into
a file in linux. That's all I did..

The package as written deals with 5 types of date format, as copied and pasted from above.

g_fmts fmtArray :=
7 fmtArray( 'dd-mm-rr', 'dd/mm/rr',
8 'dd.mm.rr', 'mon-dd-rr',
9 'dd-mon-rrrr' );

If you have other formats to deal with, such as the date/time format, then you need to add it to the list.

Hi Tom, 

I added one more format i.e rrrrmmdd
and when I call the function it is giving different result :

SQL>  select todate_pkg.thedate('20061223') from dual;

TODATE_PKG
----------
20-06-1223

1 row selected.


( I expected 23-12-2006 )


SQL> select todate_pkg.thedate('20361223') from dual;

TODATE_PKG
----------
23-12-2036

1 row selected.

( Got what I expected !!!)

The create package script is copied for your convenience.

Thanks
Jeeva


create or replace package todate_pkg
  as
   function thedate
           ( p_string in varchar2 )
            return date;
end;

/

create or replace package body todate_pkg
as
type fmtArray is table of varchar2(30);
 
g_fmts fmtArray :=
       fmtArray( 'dd-mm-rr', 'dd/mm/rr',
               'dd.mm.rr', 'mon-dd-rr',
                'dd-mon-rrrr', 'rrrrmmdd' );
 
 function thedate( p_string in varchar2 )
 return date
  is
      return_value date;
 begin
     for i in 1 .. g_fmts.count
     loop
    begin
      return_value := to_date(p_string,g_fmts(i) );
        exit;
   exception
       when others then null;
     end;
     end loop;

  if ( return_value is null ) then
     raise PROGRAM_ERROR;
   end if;
   return return_value;
 end;
 
 end;
 
 / 

but don't you see that if a given format is "acceptable" (and they are processed in a given order), it is acceptable.


20061223

is 

20-06-1223


ops$tkyte%ORA10GR2> select to_date( '20061223', 'dd-mm-rr' ) from dual;

TO_DATE('20061223','
--------------------
20-jun-1223 00:00:00


You need to be really careful when your dates could match multiple formats!

consider

010203

what is that date - could be one of many...

 

Thanks Tom,
I solved it by putting the 'rrrrmmdd' first in the list of array.
g_fmts fmtArray :=
fmtArray( 'rrrrmmdd','dd-mm-rr',
'dd/mm/rr','dd.mm.rr',
'mon-dd-rr','dd-mon-rrrr' );

Its now returning the date as required.


Thanks once again..

Jeeva

Hi,
When there is not null constraints in XNAME ,records are loding into table .When define not null constraint giving the below error and however have 10 valid records in my csv but when invoking sqlldr from cmd displaying the below message.Why it showing 42 ,it suppose to read 10
and XCREATED_ON datatype is string and getting the below error
Record 5: Rejected - Error on table "DEL_TYPE", column XCREATED_ON.
ORA-01858: a non-numeric character was found where a numeric was expected


Commit point reached - logical record count 42


Field in data file exceeds maximum length
Record 5: Rejected - Error on table DEL_TYPE.
ORA-01400: cannot insert NULL into ("SCOTT"."DEL_TYPE"."XNAME")

Record 6: Rejected - Error on table DEL_TYPE, column XLOCALE_NAME.
ORA-01401: inserted value too large for column

Record 5: Rejected - Error on table "DEL_TYPE", column XCREATED_ON.
ORA-01858: a non-numeric character was found where a numeric was expected

control file
=======
load data infile
'c:\Delivery_Type.csv'
into table DEL_TYPE
fields terminated by "," optionally enclosed by ' " '
TRAILING NULLCOLS

(

XLOCALE_NAME,

XNAME_ENGLISH,

XNAME,

XDELIVERY,

XLABEL0,

XLABEL1,

XLABEL2,

XLABEL3,

XLABEL4,

XID CONSTANT "1",
XCREATED_BY CONSTANT "admin",
XCREATED_ON CONSTANT "sysdate",
XUPDATED_BY CONSTANT "admin",
XUPDATED_ON CONSTANT "sysdate",
XNEWTS CONSTANT "sysdate",
XTIME_STAMP CONSTANT "sysdate")


Thanks and Regards,
Mohan

Ho Tom,

Sorry for not proper explanation.

1)Successfully Loaded into DEL_TYPE(without any constraints) using sqlloader ,But problem is when defined not null constraints on xname column ,no data being loaded into Table(xname has a values) and below is the contents of the log file

Field in data file exceeds maximum length
Record 5: Rejected - Error on table DEL_TYPE.
ORA-01400: cannot insert NULL into ("SCOTT"."DEL_TYPE"."XNAME")

Record 5: Rejected - Error on table "SCOTT"."DEL_TYPE", column XCREATED_ON.
ORA-01858: a non-numeric character was found where a numeric was expected

2) My csv files only 10 records and suppose to load 10
records but the target having 210 records(without any
constraints)

C:\Documents and Settings\xx>sqlldr scott/tiger control='c:\a1.ctl'

SQL*Loader: Release 9.2.0.1.0 - Production on Fri Jul 13 23:30:35 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 73
Commit point reached - logical record count 137
Commit point reached - logical record count 203
Commit point reached - logical record count 253

C:\Documents and Settings\akil>exit

SQL> select count(*) from del_type;

COUNT(*)
----------
210


control file
=======
load data infile
'c:\Delivery_Type.csv'
into table DEL_TYPE
fields terminated by "," optionally enclosed by ' " '
TRAILING NULLCOLS

( XLOCALE_NAME,
XNAME_ENGLISH,
XNAME,
XDELIVERY,
XLABEL0,
XLABEL1,
XLABEL2,
XLABEL3,
xLABEL4,
XID CONSTANT "1",
XCREATED_BY CONSTANT "admin",
XCREATED_ON CONSTANT "sysdate",
XUPDATED_BY CONSTANT "admin",
XUPDATED_ON CONSTANT "sysdate",
XNEWTS CONSTANT "sysdate",
XTIME_STAMP CONSTANT "sysdate")




Thanks and Regards,
Mohan