OraFAQ Forum: Server Utilities » field terminator, record terminator

Home » RDBMS Server » Server Utilities » field terminator, record terminator (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

field terminator, record terminator [message #478268]

Thu, 07 October 2010 11:05

simplesql
Messages: 20
Registered: October 2010

Junior Member

Hi,
I need to load a file with fields separated by '|^|' and at end of each record has '||*||'.
So in my ctl file what do i mention ?
fields terminated by '|^|' ?
for the record termination wat should I say?
Should I still mention 'trailing null col' in my ctl file...?

Sample data file:
Name|^|Age|^|city||*||
john|^|33|^|||*||
james|^||^|nyc||*||
ken|^|44|^|
washington||*||
the fields are properly terminated with |^| and the records are terminated with ||*||.
Is it true that a file with |^| as field terminator cannot be loaded with sqlldr?
Pls advice. thanks!

Report message to a moderator

Re: field terminator, record terminator [message #478283 is a reply to message #478268]

Thu, 07 October 2010 12:19

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

SQL> desc test
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 NAME                                   VARCHAR2(20)
 AGE                                    NUMBER
 CITY                                   VARCHAR2(20)

SQL>

Control file:

load data
infile *
replace
into table test
fields terminated by '|^|'
  (name,
   age,
   city terminated by '||*||'
)

begindata
Name|^|Age|^|city||*||
john|^|33|^|||*||
james|^||^|nyc||*||
ken|^|44|^|washington||*||

Loading session & checking what we've done:

SQL> $sqlldr scott/tiger@ora10 control=test10.ctl log=test10.log

SQL*Loader: Release 10.2.0.1.0 - Production on ╚et Lis 7 19:19:17 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 4

SQL> select * from test;

NAME                        AGE CITY
-------------------- ---------- --------------------
john                         33
james                           nyc
ken                          44 washington

SQL>

Report message to a moderator