sql loader control file has FIELDS TERMINATED BY ¶ (Pilcrow)

Question

I have to load data through sql loader in oracle database. In control file FIELDS TERMINATED BY ¶ mention. sql loader unable to recognize pilcrow as a delimiter.

data like

HDR¶name¶Id
BDY¶rohan¶2
TRL¶1

Answer 1

Try this: It may help you.

Fields terminated by '¶' optionally enclosed by '"' 

Demo: First, i have created one table

CREATE TABLE test_table1
(
 ID NUMBER,
 ENAME VARCHAR2(200),
 LNAME VARCHAR2(200)
 );

I have created one control file test.ctl

OPTIONS ( ROWS=5000)
LOAD DATA
INFILE *
BADFILE './test_table1.BAD'
DISCARDFILE './test_table.DSC'
APPEND INTO TABLE test_table1
Fields terminated by '¶' optionally enclosed by '"'
trailing nullcols
(
  ID,
  ENAME,
  LNAME       
)BEGINDATA
1¶A¶B
2¶C¶D

Run from command prompt:

sqlldr control = 'test.ctl' userid = username/password

It works for me.

Answer 2

The example given by S_Sauden works for me as well..

Initially start by trying with a Hex Value in the ctl file:

Fields terminated by X'C2B6'

You can use below query to find hex_value for reference:

select rawtohex( utl_raw.cast_to_raw( '¶' ) ) from dual;

---

If the above idea doesn't work for you then you can check the encoding of the file which you are trying to upload:

 file -i datafile

Above command works for linux to get the charset [encoding]. You can use that information below in ctl file for sqlldr :

load data
CHARACTERSET UTF8
infile 

Its important to sync the characterset of DB, File, Loader to make sure you don't lose any data in translation.

Below query gives you information about your Oracle DB environment:

SELECT * FROM V$NLS_PARAMETERS

Also you should make sure that your OS environment has correct NLS_LANG environment variable set. If nothing is set then please do the needful. You can use the reference link below for a full guide on the same.

export NLS_LANG=[NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]
For example: American_America.UTF8

Reference Link

Please check link for some understanding & reference on importance of characterset.