CODEWITHSUNDEEP
plsqlsql-loader
teepu
teepu

Reputation: 276

sqlldr error for optionally enclosed by '"'

I have dynamic control file as below in shell script

`echo 'LOAD DATA' >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo 'APPEND' >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo 'INTO TABLE TJX_SCR_UPLOAD_HEADER' >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo 'WHEN FILE_RECORD_DESCRIPTOR = ' \'FHEAD\'  >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo 'FIELDS TERMINATED BY ","' >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo 'TRAILING NULLCOLS' >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo '(' >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo 'FILE_RECORD_DESCRIPTOR   position(1),' >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo 'LINE_NO                  FILLER,' >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo 'SCHEDULE_NO              ,' >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo 'SCHEDULE_DATE_TIME       date' \'YYYYMMDD\' ',' >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo 'UPLD_TIME_STAMP          sysdate,' >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo 'SEQ_NO                   "' ${exec_seq} '",' >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo 'FILENAME                 CONSTANT ' \'$FILENAME\'  ',' >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo 'STATUS                   CONSTANT "UPLOADED"' >> $CTL_DIR/tjx_sc_req_upld.ctl
   echo ')' >> $CTL_DIR/tjx_sc_req_upld.ctl`

with above code the shell script also working fine.But when I add OPTIONALLY ENCLOSED BY '"'next to fields terminated by as shown below

echo 'FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ' >> $CTL_DIR/tjx_sc_req_upld.ctl

I am getting syntax error 

./tjx_sc_req_upld.ksh: line 109: syntax error at line 216: `(`' unexpected

when I remove optionally enclosed I don't get error.

Upvotes: 0

Views: 4112

Answers (1)

Gary_W
Gary_W

Reputation: 10360

You are ending up with unmatched quotes. You need to do some escaping in order to get literal single quotes:

echo 'FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '\''"'\'' ' >> $CTL_DIR/tjx_sc_req_upld.ctl

You could switch the quotes around to make it a little less ugly:

echo "FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' " >> $CTL_DIR/tjx_sc_req_upld.ctl

Upvotes: 1

Related Questions