| DECODE not working while using with SQL Loader [message #475871] |
Sat, 18 September 2010 12:04  |
okkadu
Messages: 2
Registered: September 2010
|
Junior Member |
|
|
I had a requirement of loading flatfile into staging table using SQL Loader, One of the columns in the the Flat file is having values FALSE or TRUE and my requirement is that I load 0 for FALSE and 1 for TRUE which can be achieved by simple DECODE function...I did use decode and tried to load several times but did not work. What might be the problem
LOAD DATA
INFILE 'sql_4ODS.txt'
BADFILE 'SQL_4ODS.badtxt'
APPEND
INTO TABLE members
FIELDS TERMINATED BY "|"
( Person_ID
FNAME,
LNAME,
Contact,
status "decode(:staus, 'TRUE', '1','FALSE','0')"
)
I did try putting a trim as well as SUBSTR but did not work....the cloumn just doent get any values in the output (just null or say free space)
Any help would be great.....
|
|
|
|
|
|
|
|
|
|
|
|
| Re: DECODE not working while using with SQL Loader [message #475942 is a reply to message #475884] |
Sun, 19 September 2010 13:36  |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
All we can tell you is that what you have posted works for us. You need to post some sample data, your table structure, and the results of an attempted load of that data, including the log file, similar to what I have shown below. Either your test data and table structure are different or there is a difference between versions. I do seem to recall that there may have been some limitations in using decode in earlier versions. You may have to use case instead or put the decode in a wrapper function that selects into the result variable from dual using sql, instead of assigning the value via pl/sql.
-- sql_4ODS.txt:
1|fname1|lname1|contact1|TRUE|
2|fname2|lname2|contact2|FALSE|
-- test.ctl that you provided:
LOAD DATA
INFILE 'sql_4ODS.txt'
BADFILE 'SQL_4ODS.badtxt'
APPEND
INTO TABLE members
FIELDS TERMINATED BY "|"
( Person_ID,
FNAME,
LNAME,
Contact,
status "decode(:status, 'TRUE', '1','FALSE','0')"
)
-- create table, load data, select from table:
SCOTT@orcl_11gR2> create table members
2 (person_id number,
3 fname varchar2(6),
4 lname varchar2(6),
5 contact varchar2(8),
6 status number)
7 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11gR2> select * from members
2 /
PERSON_ID FNAME LNAME CONTACT STATUS
---------- ------ ------ -------- ----------
1 fname1 lname1 contact1 1
2 fname2 lname2 contact2 0
2 rows selected.
SCOTT@orcl_11gR2>
-- test.log:
SQL*Loader: Release 11.2.0.1.0 - Production on Sun Sep 19 11:26:49 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: test.ctl
Data File: sql_4ODS.txt
Bad File: SQL_4ODS.badtxt
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table MEMBERS, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PERSON_ID FIRST * | CHARACTER
FNAME NEXT * | CHARACTER
LNAME NEXT * | CHARACTER
CONTACT NEXT * | CHARACTER
STATUS NEXT * | CHARACTER
SQL string for column : "decode(:status, 'TRUE', '1','FALSE','0')"
Table MEMBERS:
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.
Space allocated for bind array: 82560 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Sun Sep 19 11:26:49 2010
Run ended on Sun Sep 19 11:26:49 2010
Elapsed time was: 00:00:00.42
CPU time was: 00:00:00.02
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
