Home » RDBMS Server » Server Utilities » DECODE not working while using with SQL Loader (Oracle 9i)
DECODE not working while using with SQL Loader [message #475871] Sat, 18 September 2010 12:04 Go to next message
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 #475873 is a reply to message #475871] Sat, 18 September 2010 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe because "staus" is not "status".

Regards
Michem
Re: DECODE not working while using with SQL Loader [message #475874 is a reply to message #475873] Sat, 18 September 2010 13:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
There is also a comma missing after person_id.
Re: DECODE not working while using with SQL Loader [message #475884 is a reply to message #475871] Sun, 19 September 2010 00:13 Go to previous messageGo to next message
okkadu
Messages: 2
Registered: September 2010
Junior Member
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')"
)

Corrected them but problem exist
Re: DECODE not working while using with SQL Loader [message #475885 is a reply to message #475884] Sun, 19 September 2010 00:19 Go to previous messageGo to next message
BlackSwan
Messages: 22508
Registered: January 2009
Senior Member
>Corrected them but problem exist

I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: DECODE not working while using with SQL Loader [message #475942 is a reply to message #475884] Sun, 19 September 2010 13:36 Go to previous message
Barbara Boehmer
Messages: 7937
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

Previous Topic: need help with importing packages/triggers/functions
Next Topic: Export/Import Backup
Goto Forum:
  


Current Time: Sat Jul 26 12:29:51 CDT 2014

Total time taken to generate the page: 0.09237 seconds