Home » RDBMS Server » Server Utilities » SQLLDR help
SQLLDR help [message #608659] Fri, 21 February 2014 15:06 Go to next message
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 #608661 is a reply to message #608659] Fri, 21 February 2014 15:11 Go to previous messageGo to next message
BlackSwan
Messages: 22844
Registered: January 2009
Senior Member
http://www.orafaq.com/wiki/SQL*Loader_FAQ

how does "Planning" fit into NAME NOT NULL CHAR(7)?

>But i am getting issues,
see a picture of my car
It has issues.
tell me how to make my car go.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

[Updated on: Fri, 21 February 2014 15:16]

Report message to a moderator

Re: SQLLDR help [message #608700 is a reply to message #608659] Sat, 22 February 2014 13:19 Go to previous message
Barbara Boehmer
Messages: 7991
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.

Previous Topic: DataPump Schema
Next Topic: Export backup status in oracle
Goto Forum:
  


Current Time: Wed Oct 01 12:35:58 CDT 2014

Total time taken to generate the page: 0.36853 seconds