Double quotes in the in the data loaded through SQL loader

Question

I have a problem with the data getting loaded to Oracle tables through SQL Loader.

The problem is that double quotes is getting enclosed to the value inserted.

example:

"917681904           "
"914481475            "
"935848150            "

The problem is with the CHAR fields, especially with the CO_COMERCIAL_SERV.

It should have inserted without the double quotes. I have no clue from where these double quotes are getting added. :(

The control file, which I used earlier is :

OPTIONS (SKIP = 1)
LOAD DATA CHARACTERSET WE8ISO8859P1

APPEND
PRESERVE BLANKS
INTO TABLE "TAB_SAVITHA"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS

(
"NU_SECUE_PSCO",
"CO_CLIENT_COM",
"CO_PRSE",
"CO_COMERCIAL_SERV",
"CO_PROVINCIA_INST",
"NU_DURA_ANOS",
"IN_LINEA"
)

Later on, I took off double quotes around the column names and gave it as below. But still the same issue.

OPTIONS (SKIP = 1)
LOAD DATA CHARACTERSET WE8ISO8859P1

APPEND
PRESERVE BLANKS
INTO TABLE "TAB_SAVITHA"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS

(
NU_SECUE_PSCO,
CO_CLIENT_COM,
CO_PRSE,
CO_COMERCIAL_SERV,
CO_PROVINCIA_INST,
NU_DURA_ANOS,
IN_LINEA
)

The shell script used to load the data is as below::

cat<<EOD >PBPS.sql 
set linesize 800
set pagesize 3000
set heading off
set feed off

select NU_SECUE_PSCO||'|'||CO_CLIENT_COM||'|'||CO_PRSE||'|'||CO_COMERCIAL_SERV||'|'||CO_PROVINCIA_INST||'|'||NU_DURA_ANOS||'|'||IN_LINEA||'|' FROM PBPS_BKP WHERE HITO =$vpin;

exit
EOD


sqlplus -s $1/$2@$3 @PBPS.sql > PBPS.dat

sqlldr $1/$2@$3 control='./sqlloader/Main.ctl'
rm -f PBPS.sql

I am in bad need of help.

Any help is greatly appreciated.

Thanks, Savitha

Answer 1

try this and see if it works.

FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'