| SQLLDR help [message #608659] |
Fri, 21 February 2014 15:06  |
 |
krishna_oracle
Messages: 1
Registered: February 2014
|
Junior Member |
|
|
How to load spaces for Nulls using SQLLDR
My table definition is
desc emp02
Name Null Type
---- -------- --------
ID NOT NULL CHAR(3)
NAME NOT NULL CHAR(7)
DEPT NOT NULL CHAR(11)
My Input data file is
100 Thomas Sales
200 Jason Technology
300 Mayla Technology
301 Hayla
302 Planning
400 Nisha Marketing
500 Randy Technology
501 Ritu Accounting
My CTL file , i have written as per your below suggestion
load data
infile '/home/krishna/emp01.txt'
into table EMP02
(
ID POSITION(1:3) CHAR,
NAME "nvl(:NAME,' ')" POSITION(6:12) CHAR,
DEPT "nvl(:DEPT,' ')" POSITION(13:23) CHAR
)
But i am getting issues, can you please help me.
[mod-edit: code tags added by bb; next time please add them yourself]
[Updated on: Sat, 22 February 2014 13:10] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: SQLLDR help [message #608700 is a reply to message #608659] |
Sat, 22 February 2014 13:19  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
When you post without code tags, the spacing is destroyed, so it makes it difficult to tell what the spacing might have been. I added code tags to your data file so that we can see where the null values are. You were on the right track with using nvl, but you put it in the wrong place. The nvl needs to be after the position and data type. Please see the example below.
SCOTT@orcl12c> HOST TYPE emp01.txt
100 Thomas Sales
200 Jason Technology
300 Mayla Technology
301 Hayla
302 Planning
400 Nisha Marketing
500 Randy Technology
501 Ritu Accounting
SCOTT@orcl12c> HOST TYPE test.ctl
load data
infile 'emp01.txt'
into table EMP02
( ID POSITION ( 1: 3) CHAR
, NAME POSITION ( 5:11) CHAR "nvl (:NAME, ' ')"
, DEPT POSITION (12:22) CHAR "nvl (:DEPT, ' ')" )
SCOTT@orcl12c> CREATE TABLE emp02
2 (id CHAR( 7) NOT NULL,
3 name CHAR( 7) NOT NULL,
4 dept CHAR(11) NOT NULL)
5 /
Table created.
SCOTT@orcl12c> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SQL*Loader: Release 12.1.0.1.0 - Production on Sat Feb 22 11:14:50 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 8
Table EMP02:
8 Rows successfully loaded.
Check the log file:
test.log
for more information about the load.
SCOTT@orcl12c> SELECT * FROM emp02
2 /
ID NAME DEPT
------- ------- -----------
100 Thomas Sales
200 Jason Technology
300 Mayla Technology
301 Hayla
302 Planning
400 Nisha Marketing
500 Randy Technology
501 Ritu Accounting
8 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
