Home » RDBMS Server » Server Utilities » field terminator, record terminator (oracle 10g)
field terminator, record terminator [message #478268] Thu, 07 October 2010 11:05 Go to next message
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!
Re: field terminator, record terminator [message #478283 is a reply to message #478268] Thu, 07 October 2010 12:19 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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>
Re: field terminator, record terminator [message #478303 is a reply to message #478283] Thu, 07 October 2010 14:46 Go to previous messageGo to next message
simplesql
Messages: 20
Registered: October 2010
Junior Member
Really Helpful. Thanks.
I read in other forums that its not possible to load a |^| terminated file...
Thanks again.
Re: field terminator, record terminator [message #478308 is a reply to message #478303] Thu, 07 October 2010 14:57 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Don't believe everything you read.
Test for yourself!
Previous Topic: how to export & import database on different network
Next Topic: Error While Impdp
Goto Forum:
  


Current Time: Fri Mar 29 08:28:26 CDT 2024