how about external table
daniel, May 02, 2005 - 1:55 pm UTC
Wouldn't you recommend the external table?
May 02, 2005 - 2:07 pm UTC
not in 8.1.7....
and in 9i and above, direct path supports sql functions....
but none of that applies to 8i
Impressive...
Darren, May 04, 2005 - 12:50 am UTC
I am always impressed by the speeds which you demonstrate you can get with sqlldr. Would you mind showing us exactly the parameters you used to load 800000 (admittedly small) rows in 2 seconds? I continually run into issues (unable to open the data file for example) when trying to use large (> 20MB read and bindsizes. This is on small Sun server.
Thanks
May 04, 2005 - 9:00 am UTC
that was 2 minutes
Run began on Mon May 02 11:25:18 2005
Run ended on Mon May 02 11:27:05 2005
well, 13 seconds shy of two minutes.
and it was 800,000 rows from all_objects repeated over and over, so they were "average" size rows.
the file was:
-rw-rw-r-- 1 tkyte tkyte 84,521,984 May 2 11:24 t.ctl
and the command used was
sqlldr / t rows=1000 bindsize=1000000
(the biggest impact on performance here would probably be the redo logs, making sure you didn't hit a checkpoint not complete and pause situation)
Doh!
Darren, May 04, 2005 - 7:14 pm UTC
How embarrassing.... A$75k a year and can't even read a time properly. That's more like what I see on my box but it was nice to have it confirmed that it's just that simple ;)
Thanks again.
Sqlloader direct or conventional
Kannan Velayutham Subbiah, May 05, 2005 - 6:28 am UTC
Yes This was helpful in deciding to go for the direct or conventional method. That was even nice to know that lower() works in direct method in sqlloader in 9i version.
using function in sqlldr
Vinayak, May 05, 2005 - 10:16 pm UTC
in 10g sqlldr direct load, when i used functions (user-defined) I was able to load the text file if the file was small (i don't exactly remember, but it was about 50000). but if you increase the file to have say 1 million records, then it did not work.
May 06, 2005 - 7:18 am UTC
"it did not work"
is
"very very vague" -- please, if you are going to say something "doesn't work" please explain how/why it did not work.
Else we have a new myth "sqlldr doesn't work on 1 million records".
sql loader fail for direct load with function
June, May 23, 2005 - 2:01 pm UTC
Hi Tom,
I ran into this issue:
with direct=true,
and with following function applied on columns:
RECORD_NUM position(83:93) " abs(:RECORD_NUM) ",
ACCT_NUM position(114:125),
ACCT_NUM# " '1100'||:ACCT_NUM " ,
L1DATE position(190:200)
DATE(11) 'mon dd yyyy HH24.MI'
the sql loader failed when the data file size is 113084013 bytes.
However, it did work in dev twice before we moved to production (I didn't know the size in dev but my developer told me that it was successful - data was loaded into test database).
Oracle version is 32 bit, 9.2.0.6 in production.
After I changed in production by taking out direct, it works.
Is it the way suppose to work?
Thanks!
June
May 23, 2005 - 7:07 pm UTC
"failed"
what does "failed" mean.
SQL loader Direct "NLS_DATE_FORMAT" issue
Mohan, September 17, 2006 - 8:34 am UTC
I would like to add more comments here when changing from conventional path to sql direct path which I am facing currently a problem.
My environment is ORACLE 9i 64 bit production
Unix is AIX
The current system is running a flat file thru sqlloader in conventional path (direct=false)
The ctl file contain few columns in char and few columns in date. The date column each contain the same format as 'MMDDYY' in .ctl file (below is .ctl file format)
In Flat file the values against the date contain different format.
date_column1 has 'YYMMDD' i.e., 060621
date_column2 has 'YYMMDD' i.e., 060621
date_column3 has 'MMDDYY' i.e., 072106
The nls_date_format is not set it up.
The above is working fine currently. The datatype of the above columns are date.
Now I am chaning the conventional path to direct path i.e, (direct=true)
I am getting the following message as
ORA-01861: literal does not match format string
It looks like the literal ie., the date format is not taking into consideration.
The CTL file format is
,CD_COLOR POSITION(288:289) CHAR
,CD_SHADE POSITION(290:290) CHAR
,DA_EXP_DOCK POSITION(291:296) DATE(06) "MMDDYY" "DECODE(:DA_EXP_DOCK,'
',NULL,'000000',NULL,' 0',NULL,:DA_EXP_DOCK)"
,ID_EDI_SEQ POSITION(297:299) CHAR
,DA_AVAIL_ALLOC POSITION(300:305) DATE(06) "MMDDYY" "DECODE(:DA_AVAIL_ALLOC,'
',NULL,'000000',NULL,' 0',NULL,:DA_AVAIL_ALLOC)"
,DA_APPT POSITION(306:311) DATE(06) "MMDDYY" "DECODE(:DA_APPT,' ',NULL,'000000',NULL,' 0',NULL,:DA_APPT)"
What is the best approach to work by chaning conventional to direct path.
Regards,
Mohan.
September 17, 2006 - 9:36 am UTC
you don't say at all what the table looks like but - this seems like a convulated way to say
x position(a:b)
"decode( :x, ' ', to_date(null),
'000000', to_date(null),
' 0', to_date(null),
to_date(:x,'mmddyy'))"
eg: you never had a date in the input file, you had a string that could have been blanks, 0's or a 0 - and if not, a string that could be converted using a format.
eg: the sqlldr type for this should never ever have been a DATE type - it just wasn't a date.
so, bind as a string and use to_date in the decode (if the column is a date)
your existing decode function returns a STRING - there are implict conversions happening all over the place - needs to be cleaned up a bit and use explicit conversions. Decode looks at the first return type (NULL in your example) and returns that type - so there were implicit to_chars thrown in.
"Sqlloader direct or conventional "
Mohan, September 18, 2006 - 10:52 am UTC
Tom,
Again you reply was the best to fix my requirement. It matches and worked.
Thanks for your time and effort.
Regards,
Mohan.
SQL LOADER problem
Raj, March 14, 2007 - 11:03 pm UTC
Hi Tom,
Oracle Version 9i, Unix Environment
I am trying to load around 2 million rows of records into a table. The flat file contains the fixed length.
How the following things can be achieved while loading the data from flat file thru sql loader.
1. I want to concatenate the fixed position from 1 to 7
8 to 9 into a column. But while concatenating I want the following way. for eg: 1234567:89
2. Column having date datatype. This column should be filled by sysdate while loading thru sql loader
3.Want to validate the date field column. If the valid date value is not in the flat file, then this column in the table should be null while loading.
eg: In flat file I would get the following records format.
20070401(yyyymmdd) is valid one
20070100(yyyymmdd) is invalid.
I need both the records to be present in the table. one should have the value as 20070401 and the other one should be as null due to invalid day of the date.
How this can be achieved? Reply would be appreciated
Thanks
March 15, 2007 - 9:39 am UTC
ops$tkyte%ORA10GR2> desc t
Name Null? Type
---------------------------------------- -------- ----------------------------
FIELD1 VARCHAR2(10)
FIELD2 DATE
FIELD3 DATE
ops$tkyte%ORA10GR2> !cat test.ctl
LOAD DATA
INFILE *
INTO TABLE t
replace
( field1 position(1:9) "substr(:field1,1,7)||':'||substr(:field1,8)",
field2 SYSDATE,
field3 position(11:18) "my_to_date(:field3)"
)
begindata
123456789 20070100
123456789 20070101
ops$tkyte%ORA10GR2> !sqlldr / test
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 15 08:41:47 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 2
ops$tkyte%ORA10GR2> select * from t;
FIELD1 FIELD2 FIELD3
---------- --------- ---------
1234567:89 15-MAR-07
1234567:89 15-MAR-07 01-JAN-07
Sql Loader
Raj, March 15, 2007 - 12:23 pm UTC
Hi Tom,
Thanks for your prompt reply.
what is my_to_date? if it is a procedure what type of validation is been done in this.
Another quick question is
Is the following validation is possible...
"substr(:field1,1,7)||':'||substr(:field1,8)", NULLIF (field1="000000000"),
Basically I want to concatenate and check for 9 zeros if it is there in the flat file then make it as null to field1 while storing in the table.
March 15, 2007 - 12:43 pm UTC
create or replace function my_to_date( p_str in varchar2 ) return date
as
begin
return to_date( p_str, 'yyyymmdd' );
exception
when others
then
if ( sqlcode in ( -1841 /* bad year */, -1843 /* bad day */, -1847 /* bad month */,
-1858 /* non-numeric */ ) )
then
return null;
else
raise;
end if;
end;
doh, forgot that didn't I.....
"decode( substr(), '0000000', null, substr() || ':' || substr() )"
would be that function
Sysdate in Sql Loader
Raj, March 15, 2007 - 4:04 pm UTC
While giving the sysdate with the following way i am unable to populate the date in the table.
I have used the following one
field1 is date datatype
field1 "to_char(sysdate,'ddmmyyyy')"
it is working fine with this way
field1 sysdate
The values are stored date and timestamp aswell. I don't need the timestamp.
Thanks in Advance.
March 16, 2007 - 3:02 pm UTC
why would you to_char(dt) to put it into a date field? if you have a date, just leave it be.
no clue what the to_char is there for at all.
decode in sqlloader
raj, March 15, 2007 - 4:36 pm UTC
I tried the decode function..
"decode( substr(:VISITOR_XID,3,17), '000000000000000', null, substr(:VISITOR_XID,3,10) || ':' || substr(:VISITOR_XID,11,18) )",
It is still giving me the values as zeros instead of null.
Am I missing something here..
March 16, 2007 - 3:05 pm UTC
then vistitor_xid from 3 for 17 is not that string.
makes sense, since your string of zeros in your decode is only 15 characters...
your substr - 17 characters
your constant '000000000000000', 15 characters.
they probably won't match ;)
Using DIRECT = TRUE in sql loader does not work when server and client versions don't match
Asim, April 03, 2008 - 8:08 pm UTC
Hi Tom,
I am really fighting with this. My oracle database version is "Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production" and oracle client is "SQL*Loader: Release 10.1.0.3.0 - Production on Thu Apr 3 20:56:47 2008".
Now problem happens when I try to use DIRECT = TRUE option for the sql loader. I get the following error message.
"SQL*Loader-951: Error calling once/load initialization
ORA-00942: table or view does not exist".
But if I remove the DIRECT option it works for me but it is really really slow.
I am expecting it is due to version incompatibility for database and client, so I tried to see if we can upgrade oracle 9i to Oracle 10g for the database server. But I didn't get any support from the team saying, that might impact many other projects. But they say, in production we will have oracle 10g server but dev it is not possible now to upgrade.
I just wanted to check with you is there any other alternative I could use the DIRECT option with server and client different versions?
Thanks,
Asim
April 03, 2008 - 9:32 pm UTC
you need to have the same version - yes.
why not use
a) external tables (my preference over sqlldr in most all cases)
b) the same client version - eg: put file on server and load from there
Good work.
A reader, April 03, 2008 - 11:54 pm UTC
Hi Tom,
Thanks for your excellent work. Your way of explaining (with proof) is very nice.
On a side note, if I may point out, most of times you tend to use e.g. (for example) where i.e. (that is) would be more appropriate. Some times it hinders the flow of reading your answers.
We have gained so much from you. Thanks for sharing your knowledge.
DIRECT option with sql loader
Asim, April 04, 2008 - 12:04 pm UTC
Thank you for the reply Tom.
Reader, May 14, 2009 - 2:30 pm UTC
Tom,
Can I use to_timestamp function in sql loader direct path load?
OPTIONS (direct=true)
LOAD DATA
APPEND INTO TABLE tbl
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
id ,
name ,
tm_created "to_timestamp(:tm_created,'yyyy-mm-dd HH24:MI:SS.FF')"
)
May 14, 2009 - 5:42 pm UTC
why wouldn't you test it out yourself?
in current releases you can invoke SQL in a direct path load from sqlldr.
ops$tkyte%ORA10GR2> select * from emp;
no rows selected
ops$tkyte%ORA10GR2> !cat emp.ctl
LOAD DATA
INFILE *
INTO TABLE emp
REPLACE
FIELDS TERMINATED BY '|'
(
empno
,ename
,job
,mgr
,hiredate "to_timestamp(:hiredate,'dd-mon-yy')"
,sal
,comm
,deptno
)
BEGINDATA
7369|SMITH|CLERK|7902|17-DEC-80|800||20
7499|ALLEN|SALESMAN|7698|20-FEB-81|1600|300|30
7521|WARD|SALESMAN|7698|22-FEB-81|1250|500|30
7566|JONES|MANAGER|7839|02-APR-81|2975||20
7654|MARTIN|SALESMAN|7698|28-SEP-81|1250|1400|30
7698|BLAKE|MANAGER|7839|01-MAY-81|2850||30
7782|CLARK|MANAGER|7839|09-JUN-81|2450||10
7788|SCOTT|ANALYST|7566|09-DEC-82|3000||20
7839|KING|PRESIDENT||17-NOV-81|5000||10
7844|TURNER|SALESMAN|7698|08-SEP-81|1500|0|30
7876|ADAMS|CLERK|7788|12-JAN-83|1100||20
7900|JAMES|CLERK|7698|03-DEC-81|950||30
7902|FORD|ANALYST|7566|03-DEC-81|3000||20
7934|MILLER|CLERK|7782|23-JAN-82|1300||10
ops$tkyte%ORA10GR2> !sqlldr / emp.ctl direct=y
SQL*Loader: Release 10.2.0.4.0 - Production on Thu May 14 17:21:22 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Load completed - logical record count 14.
ops$tkyte%ORA10GR2> select count(*) from emp;
COUNT(*)
----------
14
ops$tkyte%ORA10GR2> select hiredate from emp;
HIREDATE
---------
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
Reader, June 15, 2009 - 12:53 pm UTC
Tom,
Using to_timestamp in the direct load does not seem to be working. Can you please advice? I tried as below -
create table tst
(empno number
,ename varchar2(20)
,job varchar2(20)
,mgr number
,hiredate timestamp(6)
,sal number
,comm number
,deptno number
);
tst.dat
7369|SMITH|CLERK|7902|17-DEC-80|800||20
7499|ALLEN|SALESMAN|7698|20-FEB-81|1600|300|30
7521|WARD|SALESMAN|7698|22-FEB-81|1250|500|30
7566|JONES|MANAGER|7839|02-APR-81|2975||20
7654|MARTIN|SALESMAN|7698|28-SEP-81|1250|1400|30
7698|BLAKE|MANAGER|7839|01-MAY-81|2850||30
7782|CLARK|MANAGER|7839|09-JUN-81|2450||10
7788|SCOTT|ANALYST|7566|09-DEC-82|3000||20
7839|KING|PRESIDENT||17-NOV-81|5000||10
7844|TURNER|SALESMAN|7698|08-SEP-81|1500|0|30
7876|ADAMS|CLERK|7788|12-JAN-83|1100||20
7900|JAMES|CLERK|7698|03-DEC-81|950||30
7902|FORD|ANALYST|7566|03-DEC-81|3000||20
7934|MILLER|CLERK|7782|23-JAN-82|1300||10
tst.ctl
OPTIONS (direct=true)
LOAD DATA
INFILE '/home/test/tst.dat'
BADFILE '/home/test/tst.bad'
DISCARDFILE '/home/test/tst.dis'
APPEND
INTO TABLE tst
FIELDS TERMINATED BY '|'
(
empno
,ename
,job
,mgr
,hiredate "to_timestamp(:hiredate,'dd-mon-yy')"
,sal
,comm
,deptno
)
sqlldr emp@empdb control=tst.ctl
SQL*Loader: Release 10.2.0.4.0 - Production on Mon Jun 15 12:25:32 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL*Loader-951: Error calling once/load initialization
ORA-26052: Unsupported type 180 for SQL expression on column HIREDATE.
June 15, 2009 - 2:19 pm UTC
suppport for timestamp in direct path load with a function like that is added in 11.1
looks like it was backported to 10.2 on some platforms
contact support, reference Bug 5286177
SQLLDR Direct path load With User_defined functions in 10g
VRR, February 13, 2013 - 1:10 am UTC
Hi Tom,
We are using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit.
In 10g SQL*Loader direct load, when i used functions (user-defined) I was able to load the text file data.
But i read in some documents about SQLLDR i.e.
When calling user-defined functions we must remember that only conventional path may be used. An attempt to use direct path will cause an error:
SQL*Loader-00417 SQL String (on column column_name) not allowed in direct path.
But Direct Path Load with User defined functions is working fine for me and i didn't get the above error.
Please tell me which version of oracle resolved the error SQL*Loader-00417.
Thanks in Advance.
February 13, 2013 - 7:47 am UTC
see the followup above:
Followup May 2, 2005 - 2pm Central time zone: