Home » RDBMS Server » Server Utilities » Sql Loader (oracle 10 g)
Sql Loader [message #567151] Mon, 24 September 2012 12:06 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
Hi,
I have flat file in the following format
1,Alex
02,Paul
3,Allen

Table Structure :
create table emp
( id  number,
  name varchar2(40),
  file_id varchar2(4)
)  



The data file has two column but the table has 3 . The 1st and 3rd column are same but id is number and file_id is character.
How to change in the control file. Need some input from you .
Emp
Id, name ,file_id
1  Alex   1
2  paul   02
3, Allen  3

Thanks in advance
Re: Sql Loader [message #567155 is a reply to message #567151] Mon, 24 September 2012 12:24 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
table is not normalized.
It is silly to have two different columns hold the same data in two different datatypes.
CREATE VIEW
or utilize Virtual Column
Re: Sql Loader [message #567157 is a reply to message #567151] Mon, 24 September 2012 13:23 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
bond007 wrote on Mon, 24 September 2012 13:06

1,Alex
02,Paul
3,Allen


Emp
Id, name ,file_id
1  Alex   1
2  paul   02
3, Allen  3



What determines that ID number 2 will use lowercase for the name and that ID 3 will have a comma appended to the Emp ID?
Aside from those 2 issues, have you tried just putting 2 entires in the control file, one using a TO_NUMBER and the other using data "as is." Of course, TO_NUMBER will fail on any data that is not really a number.
Re: Sql Loader [message #567158 is a reply to message #567155] Mon, 24 September 2012 13:24 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
if id is 2 file_id is 02 . we need to capture file_id exactly as per the data file.
Re: Sql Loader [message #567168 is a reply to message #567158] Mon, 24 September 2012 17:20 Go to previous message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
-- test.dat:
1,Alex
02,Paul
3,Allen


-- test.ctl:
load data
infile test.dat
into table emp
fields terminated by ','
trailing nullcols
(id, name, file_id ":id")


-- table, load, and results:
SCOTT@orcl_11gR2> create table emp
  2    (id	 number,
  3  	name	 varchar2(40),
  4  	file_id  varchar2(4))
  5  /

Table created.

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@orcl_11gR2> select * from emp
  2  /

        ID NAME                                     FILE
---------- ---------------------------------------- ----
         1 Alex                                     1
         2 Paul                                     02
         3 Allen                                    3

3 rows selected.

Previous Topic: Inserting targz files into table from specified folder
Next Topic: load query result in ms-excel sheet
Goto Forum:
  


Current Time: Fri Dec 02 20:29:22 CST 2016

Total time taken to generate the page: 0.06522 seconds