| SQLLDR loading multiple tables [message #74429] |
Wed, 08 December 2004 08:23  |
Kris Johnson
Messages: 4
Registered: December 2004
|
Junior Member |
|
|
As a relative newcomer to the world of sqlldr, I'm struggling with trying to get my control file set up to load several tables from one input file.
Here's my example...
LOAD DATA
INFILE '/u10/aaa/bbb/ccc.DAT' "FIX 00310"
TRUNCATE
INTO TABLE test_temp_1
when (1:2) = 1
( Field_1 POSITION(1:2) SMALLINT,
Field_2 POSITION(3:3) CHAR(1),
Field_3 POSITION(4:9) DATE "MMDDRR",
Field_4 POSITION(10:18) CHAR(9))
INTO TABLE test_temp_2
when (1:2) <> 1
(
Field_1 POSITION(1:2) SMALLINT,
Field_2 POSITION(3:3) CHAR(1),
Field_3 POSITION(4:9) DATE "MMDDRR",
Field_4 POSITION(10:18) CHAR(9))
The above will blow up because it does not like the = 1 option in the when clause. If in enclose the 1 in quotes ('1') all of the data seems to be loading into test_temp_2.
I believe that the issue is my data. It is coming from a mainframe system that has converted it from EBCIDIC to ASCII.
Field_1 is in some kind of binary format. I've tried using x'001C' or x'0001' but nothing seems to be working. Oracle seems to know how to convert this data when it loads the table so I can I do that same conversion in my when clause?
|
|
|
|
| Re: SQLLDR loading multiple tables [message #74430 is a reply to message #74429] |
Wed, 08 December 2004 11:08  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
There are some things that work by themselves, but don't work in conjunction with others things or only work in conjunction with other things. For example, when clauses only work with positional notation and require quotes around the value, like '1'. It appears that it doesn't work well with your "FIX 00310". So, put quotes around '1' and get rid of the FIX.
LOAD DATA
INFILE '/u10/aaa/bbb/ccc.DAT'
TRUNCATE
INTO TABLE test_temp_1
when (1:2) = '1'
( Field_1 POSITION(1:2) SMALLINT,
Field_2 POSITION(3:3) CHAR(1),
Field_3 POSITION(4:9) DATE "MMDDRR",
Field_4 POSITION(10:18) CHAR(9))
INTO TABLE test_temp_2
when (1:2) <> '1'
(
Field_1 POSITION(1:2) SMALLINT,
Field_2 POSITION(3:3) CHAR(1),
Field_3 POSITION(4:9) DATE "MMDDRR",
Field_4 POSITION(10:18) CHAR(9))
|
|
|
|
| Re: SQLLDR loading multiple tables [message #74431 is a reply to message #74430] |
Wed, 08 December 2004 11:34 |
Kris Johnson
Messages: 4
Registered: December 2004
|
Junior Member |
|
|
I tried your suggestion and here's what I got:
Control File: /u10/aaa/bbb/ccc/ddd.CTL
Data File: /u10/aaa/eee/ddd.DAT
Bad File: /u10/aaa/bbb/ccc/ddd.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 0
Continuation: none specified
Path used: Direct
Table TEST_TEMP_1, loaded when 1:2 = 0X31(character '1') Insert option in effect for this table: TRUNCATE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
Field_1 1:2 2 SMALL INTEGER
Field_2 3:3 1 CHARACTER
Field_3 4:9 6 DATE MMDDRR
Field_4 10:18 9 CHARACTER
Table test_temp_2, loaded when 1:2 != 0X31(character '1') Insert option in effect for this table: TRUNCATE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
Field_1 1:2 2 SMALL INTEGER
Field_2 3:3 1 CHARACTER
Field_3 4:9 6 DATE MMDDRR
Field_4 10:18 9 CHARACTER
Record 2: Rejected - Error on table test_temp_1, column Field_2.
ORA-01400: cannot insert NULL into (Field_2)
There shouldn't be any nulls to be loaded into Field_2.
|
|
|
|
|
|
| Re: SQLLDR loading multiple tables [message #74436 is a reply to message #74433] |
Thu, 09 December 2004 04:23 |
Kris Johnson
Messages: 4
Registered: December 2004
|
Junior Member |
|
|
The problem here is that the data is being ftp'd from a mainframe system. Once on the server, it appears to wrap. When I have the 'FIXED' option, than SQLLDR knows where the next record starts, but if I remove that option, than I'm guessing the SQLLDR starts the next record in the wrong spot and I get the null value error.
So, from your original post you're saying that there is no way to use the "FIXED" option with the WHEN option?
|
|
|
|
| Re: SQLLDR loading multiple tables [message #74440 is a reply to message #74436] |
Thu, 09 December 2004 17:18 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The usual undesired result of trying to use the when clause with a combination of other things is that only one table gets loaded. One solution is to use two control files and two runs of sqlldr, as shown below:
--control1.ctl:
LOAD DATA
INFILE '/u10/aaa/bbb/ccc.DAT' "FIX 00310"
TRUNCATE
INTO TABLE test_temp_1
when (1:2) = '1'
( Field_1 POSITION(1:2) SMALLINT,
Field_2 POSITION(3:3) CHAR(1),
Field_3 POSITION(4:9) DATE "MMDDRR",
Field_4 POSITION(10:18) CHAR(9))
-- control2.ctl:
LOAD DATA
INFILE '/u10/aaa/bbb/ccc.DAT' "FIX 00310"
TRUNCATE
INTO TABLE test_temp_2
when (1:2) <> '1'
(
Field_1 POSITION(1:2) SMALLINT,
Field_2 POSITION(3:3) CHAR(1),
Field_3 POSITION(4:9) DATE "MMDDRR",
Field_4 POSITION(10:18) CHAR(9))
sqlldr username/password control=control1.ctl direct=true log=control1.log bad=control1.bad
sqlldr username/password control=control2.ctl direct=true log=control2.log bad=control2.bad
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
