| data load problem !! [message #158188] |
Wed, 08 February 2006 21:32  |
AlinaC
Messages: 45
Registered: November 2005
Location: India
|
Member |
|
|
Employee Datafile
Tom, 1/B-XYZ street
Jon, 1/C-XYZ Street
Above is a sample data file. Now I would like to import the data into an Oracle table called employee using Oracle 9i SQL Loader utility. But the table has 5 fields (Emp_no,Name , Address,Phone),where as data file has only 3 fields. Is it possible to load data into the table using the above data file? In the table for Emp_no and Phone I would like to put NULL initially.
Regards,
Alina
|
|
|
|
|
|
|
|
|
|
| Re: data load problem !! [message #158919 is a reply to message #158907] |
Wed, 15 February 2006 01:29  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>Now I would like to put oracle table's default values in some of the fileds for which I'm not inserting values from data file.How to do it?
If you want to use some default value if you encounter NULL , then use INTEGER EXTERNAL
or
If the field value is a constant one, use CONSTANT.
oracle@mutation#cat myctl.ctl
LOAD DATA
INFILE 'mydata.data'
INTO TABLE mytable
TRUNCATE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
CODE CHAR,
STATUS INTEGER EXTERNAL "nvl(:STATUS,'S')"
)
oracle@mutation#cat mydata.data
1,a
2
3,b
4,c
oracle@mutation#sqlldr userid=scott/tiger control=myctl.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Mon Feb 13 19:52:07 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 5
oracle@mutation#query mutation scott.mytable
CO S
-- -
1 a
2 S
3 b
4 c
-- just changing the controlfile.
oracle@mutation#sqlldr userid=scott/tiger control=myctl.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Wed Feb 15 02:26:46 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 4
oracle@mutation#query mutation scott.mytable
CO S
-- -
1 S
2 S
3 S
4 S
oracle@mutation#cat myctl.ctl
LOAD DATA
INFILE 'mydata.data'
INTO TABLE mytable
TRUNCATE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
CODE CHAR,
STATUS CONSTANT 'S'
)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
