Home » RDBMS Server » Server Utilities » SQL Loader with record Delimiter Issue
| SQL Loader with record Delimiter Issue [message #384614] |
Wed, 04 February 2009 08:22  |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi,
I have to load data into Oracle tables. The data file consists of '#' as a column delimiter and '^' as row delimiter.
Could any one help me how to write a control file with record delimiter.
Could any explain the difference between conventional and direct method and also the clause ' OPTIONALLY ENCLOSED BY '"'.
Any help really appreciated.
Thanks in advance.
|
|
|
|
|
|
| Re: SQL Loader with record Delimiter Issue [message #384671 is a reply to message #384614] |
Wed, 04 February 2009 14:40  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can use utl_raw.cast_to_raw to get the hexidecimal representation of your record delimiter, then you can use that in your control file.
Optionally enclosed by means that the field may or may not begin and end with whatever the enclosure symbol is, but that if it is enclosed then that symbol should not be counted as part of the data and any delimiters within those enclosures should be considered part of the data, not delimiters.
You can read about the conventional and direct path loading methods in the following section of the online documentation:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/ldr_modes.htm#i1007501
The example below demonstrates usage of a feld terminator of # with an optional enclosure character of
~ and a record delimiter of ^.
SCOTT@orcl_11g> select utl_raw.cast_to_raw ('^') as "hexidecimal for ^" from dual
2 /
hexidecimal for ^
--------------------------------------------------------------------------------
5E
SCOTT@orcl_11g>
-- test.dat:
1#2#3#4#^5#6#~7~#8#^9#10#11#12#^13#14#15#16#^
-- test.ctl:
load data
infile test.dat "str X'5E'"
into table test_tab
fields terminated by '#' optionally enclosed by '~'
(col1, col2, col3, col4)
-- test table:
SCOTT@orcl_11g> create table test_tab
2 (col1 number,
3 col2 number,
4 col3 number,
5 col4 number)
6 /
Table created.
-- load:
SCOTT@orcl_11g> host sqlldr scott/tiger control=test.ctl log=test.log
-- results:
SCOTT@orcl_11g> select * from test_tab
2 /
COL1 COL2 COL3 COL4
---------- ---------- ---------- ----------
1 2 3 4
5 6 7 8
9 10 11 12
13 14 15 16
SCOTT@orcl_11g>
|
|
|
|
| Re: SQL Loader with record Delimiter Issue [message #384787 is a reply to message #384671] |
Thu, 05 February 2009 03:40 |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi,
Tried with below eaxmple. But encoutered with following error.
Could anyopne help me in this.
options (skip =1)
load data
infile 'C:\dataload\data\backupprocess_ec1.txt "str X '5E'" '
Replace into table backupprocess_ec
fields terminated by "#"
trailing nullcols
(
BACKUP_ID,
CREATED "(to_timestamp(substr(:created,1,19), 'yyyy-mm-dd hh24:mi:ss'))",
USER_ID,
PROCESS_ID,
CONTENT Char(100000000),
"COMMENT" char(400)
)
error:
C:\Documents and Settings\200417>sqlldr control=C:\dataload\control\backupproces
s_ec.ctl, log=C:\dataload\log\backupprocess_ec.log, BAD=C:\dataload\Bad\backuppr
ocess_ec.bad, USERID=temp/temp@eclipse_dev, Errors=999, Load=200000000, Discard
=C:\dataload\discard\backupprocess_ec.dsc, discardmax=5
SQL*Loader: Release 11.1.0.6.0 - Production on Thu Feb 5 15:07:28 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL*Loader-350: Syntax error at line 3.
Expecting keyword INTO, found "5".
infile 'C:\dataload\data\backupprocess_ec1.txt "str X '5E'" '
^
Any help really appreciated
Thanks in advance
|
|
|
|
|
|
| Re: SQL Loader with record Delimiter Issue [message #384796 is a reply to message #384788] |
Thu, 05 February 2009 04:10 |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi,
Tried but encountered below error.
options (skip =1)
load data
infile C:\dataload\data\backupprocess_ec1.txt "str X '5E'"
into table backupprocess_ec
fields terminated by "#"
trailing nullcols
(
BACKUP_ID,
CREATED "(to_timestamp(substr(:created,1,19), 'yyyy-mm-dd hh24:mi:ss'))",
USER_ID,
PROCESS_ID,
CONTENT Char(100000000),
"COMMENT" char(400)
)
C:\Documents and Settings\200417>sqlldr control=C:\dataload\control\backupproces
s_ec.ctl, log=C:\dataload\log\backupprocess_ec.log, BAD=C:\dataload\Bad\backuppr
ocess_ec.bad, USERID=temp/temp@eclipse_dev, Errors=999, Load=200000000, Discard
=C:\dataload\discard\backupprocess_ec.dsc, discardmax=5
SQL*Loader: Release 11.1.0.6.0 - Production on Thu Feb 5 15:25:15 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL*Loader-350: Syntax error at line 3.
Expecting keyword INTO, found ":".
infile C:\dataload\data\backupprocess_ec1.txt "str X '5E'"
Thanks in advance
|
|
|
|
|
|
| Re: SQL Loader with record Delimiter Issue [message #384809 is a reply to message #384800] |
Thu, 05 February 2009 04:50 |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi,
Again encountered with below error.
C:\Documents and Settings\200417>sqlldr control=C:\dataload\control\backupproces
s_ec.ctl, log=C:\dataload\log\backupprocess_ec.log, BAD=C:\dataload\Bad\backuppr
ocess_ec.bad, USERID=temp/temp@eclipse_dev, Errors=999, Load=200000000, Discard
=C:\dataload\discard\backupprocess_ec.dsc, discardmax=5
SQL*Loader: Release 11.1.0.6.0 - Production on Thu Feb 5 16:05:16 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL*Loader-500: Unable to open file (C:\dataload\data\backupprocess_ec1.txt)
SQL*Loader-555: unrecognized processing option
SQL*Loader-509: System error: The operation completed successfully.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
SQL*Loader-513: Unable to close file (C:\dataload\data\backupprocess_ec1.txt)
SQL*Loader-559: error closing file
SQL*Loader-509: System error: The storage control block address is invalid.
Thanks in advance
|
|
|
|
|
|
| Re: SQL Loader with record Delimiter Issue [message #384846 is a reply to message #384812] |
Thu, 05 February 2009 07:35 |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi,
I tried by removing the the line "str X '5E'". Its able to run without any errors. But records got rejected as record delimiter is '^'.
When i tried by including above line of code in control file.
I am encountered with below error.
C:\Documents and Settings\200417>sqlldr control=C:\dataload\control\backupproces
s_ec.ctl, log=C:\dataload\log\backupprocess_ec.log, BAD=C:\dataload\Bad\backuppr
ocess_ec.bad, USERID=temp/temp@eclipse_dev, Errors=999, Load=200000000, Discard
=C:\dataload\discard\backupprocess_ec.dsc, discardmax=5
SQL*Loader: Release 11.1.0.6.0 - Production on Thu Feb 5 19:01:46 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL*Loader-500: Unable to open file (C:\DataLoad\Data\backupprocess_ec1.txt)
SQL*Loader-555: unrecognized processing option
SQL*Loader-509: System error: The operation completed successfully.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
SQL*Loader-513: Unable to close file (C:\DataLoad\Data\backupprocess_ec1.txt)
SQL*Loader-559: error closing file
SQL*Loader-509: System error: The storage control block address is invalid.
Could anyone help me on this.
Thanks in advance
|
|
|
|
|
|
| Re: SQL Loader with record Delimiter Issue [message #385001 is a reply to message #384869] |
Fri, 06 February 2009 01:07 |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi,
Below attached is the datafile which is to be loaded into database tables.
Column Delimiter as "#"
Row Delimiter as "^"
Below are the table script, Control file and Errors encountered
Table Script
------------
CREATE TABLE "BACKUPPROCESS_EC"
( "BACKUP_ID" NUMBER(10,0) NOT NULL ENABLE,
"CREATED" TIMESTAMP (6) NOT NULL ENABLE,
"USER_ID" NUMBER(10,0) NOT NULL ENABLE,
"PROCESS_ID" NUMBER(10,0) NOT NULL ENABLE,
"CONTENT" CLOB NOT NULL ENABLE,
"COMMENT" VARCHAR2(400 BYTE),
PRIMARY KEY ("BACKUP_ID")
ENABLE
)
Control File
-------------
options (skip =1)
load data
infile 'C:\DataLoad\Data\backupprocess_ec.txt' "str X '5E'"
replace into table backupprocess_ec
fields terminated by "#"
trailing nullcols
(
BACKUP_ID,
CREATED "(to_timestamp(substr(:created,1,19), 'yyyy-mm-dd hh24:mi:ss'))",
USER_ID,
PROCESS_ID,
CONTENT Char(100000000),
"COMMENT" char(400)
)
Errors Encountered
---------------------
C:\Documents and Settings\200417>sqlldr control=C:\dataload\control\backupproces
s_ec.ctl, log=C:\dataload\log\backupprocess_ec.log, BAD=C:\dataload\Bad\backuppr
ocess_ec.bad, USERID=temp/temp@eclipse_dev, Errors=999, Load=200000000, Discard
=C:\dataload\discard\backupprocess_ec.dsc, discardmax=5
SQL*Loader: Release 11.1.0.6.0 - Production on Thu Feb 5 19:01:46 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL*Loader-500: Unable to open file (C:\DataLoad\Data\backupprocess_ec1.txt)
SQL*Loader-555: unrecognized processing option
SQL*Loader-509: System error: The operation completed successfully.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
SQL*Loader-513: Unable to close file (C:\DataLoad\Data\backupprocess_ec1.txt)
SQL*Loader-559: error closing file
SQL*Loader-509: System error: The storage control block address is invalid.
Any Help really appreciated
Thanks in advance
|
|
|
|
|
|
| Re: SQL Loader with record Delimiter Issue [message #385066 is a reply to message #385001] |
Fri, 06 February 2009 05:47 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You have a space between X and '5E' that does not belong there. If you remove the space, 8 rows are loaded. 4 rows are not loaded because the comment data is too long. You could also use:
"str '^'"
instead of
"str X'5E'"
since the ^ is a visible character.
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 20:24:26 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
