I made few changes to this free utility, a new option "mode=" is introduced to change the load mode, there are four different modes in sqlldr.
INSERT: Load data into empty table, this is the default mode.APPEND: Append data into temp table.REPLACE: delete all rows first, else insert.TRUNCATE: truncate table first, then insert.
The new option will eliminate the requirements of change the SQL loader control file when you want to migrate changed rows to new database.
Another changes are DATE, TIMESTAMP data type relevant, this utility will run the following command to set output format.
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SSXFF'
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SSXFF TZH:TZM'
Now you can use ociuldr to migrate these data types without any problem. Let's take a look at the SQL loader control file generated automatically.
OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE 'uldrdata.txt' "STR X'0a'"
INSERT INTO TABLE t_timestamp
FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS
(
COL1 TIMESTAMP "YYYY-MM-DD HH24:MI:SSXFF"
)
You can test more with these data types.
Comments (3)
Thanks for this great tool! Such a time saver.
One thought, not sure if it's easy.
Is it possible that fixed-length format control file incoporated into this tool when a table name is passed.
Thanks again.
yzhang
Posted by Y. Zhang | September 28, 2007 5:57 AM
Good suggestion, I will try to add fixed-length format support.
Posted by anysql | September 28, 2007 8:02 AM
Thanks too for this excellent tool.
I plan to use ociuldr to export data from an oracle db and load a sybase IQ db.
From my tests, fixed-length files seem to be faster.
So, this feature would be great.
Also maybe someone knows ... should I use a named pipe to improve the data transfer time ?
Posted by chris | April 28, 2008 2:37 AM