| Loading data into table using SQL*Loader [message #122454] |
Mon, 06 June 2005 10:16  |
mukka_ramesh
Messages: 19
Registered: June 2005
|
Junior Member |
|
|
Following is the control file I used:
LOAD DATA
INFILE 'empdat.dat'
INTO TABLE emp_test1
Fields terminated by "," Optionally enclosed by '"'
(
HIREDATE DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (HIREDATE="NULL"),
DEPTNO NULLIF (DEPTNO="NULL"),
JOB NULLIF (JOB="NULL"),
MGR NULLIF (MGR="NULL"),
ename nullif (ename ="NULL"),
empno nullif (empno="NULL"),
SAL NULLIF (SAL="NULL"),
COMM NULLIF (COMM="NULL")
)
Following is the content of the data file:
"04/12/1984 00:00:00",40,"MANAGER",7800,"JONES",7899,8900,99
NULL,40,"CLERK",7784,"ADAMS",7876,4000,20
None of the record is loaded into table. My requirement is to load the column of the table with NULL when corresponding column in data file is NULL.
What is wrong with this? Can any one help in this regard?
|
|
|
|
| Re: Loading data into table using SQL*Loader [message #122501 is a reply to message #122454] |
Mon, 06 June 2005 16:09  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I used your control file, calling it test.ctl and your data file, and ran the following:
scott@ORA92> CREATE TABLE emp_test1 AS SELECT * FROM emp WHERE 1 = 2
2 /
Table created.
scott@ORA92> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
scott@ORA92> SELECT * FROM emp_test1
2 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7899 JONES MANAGER 7800 12-APR-84 8900 99 40
7876 ADAMS CLERK 7784 4000 20 40
scott@ORA92>
So, it looks like it works just fine to me, assuming that you are actually checking for the word "NULL" and that is what is in your data file, not just a lack of a value. What do you get in your SQL*Loader log file? For example, if you used the code that I used above, then:
EDIT test.log
copy and paste the contents of that file here. It should tell you where your error is. It might also help if you can post the structure of your emp_test1 table. Is it the same as the Oracle emp demo table?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
