0

i want to import csv file. my script is :

@echo off
set numid=2015092510524361378197540100
sqlldr USER@db/PSW data=csv\2015092510524361378197540100.csv control=ctl\control.ctl log=log\2015092510524361378197540100.log bad=bad\id.bad
pause

my table is :

CREATE TABLE SV (NO1 VARCHAR2(255),NAMA VARCHAR2(255),ALAMAT VARCHAR2(255),id VARCHAR2(20),JAB VARCHAR2(50),numid VARCHAR(55));

my control.ctl is :

OPTIONS (SKIP=43, errors=12000) LOAD DATA  APPEND INTO TABLE sv when NAMA <> '' FIELDS TERMINATED BY ',' optionally enclosed by '"' TRAILING NULLCOLS (no filler,no1 "TRIM (:no1)",nama "TRIM (:nama)", alamat "TRIM (:alamat)",id "TRIM (:id)",jab "TRIM (:jab)",numid "%numid%")

error is :

Record 10: Rejected - Error on table sV, column numid.
ORA-00911: invalid character

please let me know which one is wrong. thanks All

Gary_W
  • 9,933
  • 1
  • 22
  • 40
flyingbird013
  • 446
  • 2
  • 12
  • 28
  • You left out the csv file itself. Specifically record 10. The numid column. Actually I don't know what the percent signs are for around numid in the control file, but the numid inside of them should have a colon in front. – Gary_W Oct 28 '15 at 18:56

1 Answers1

1

Since numid is a VARCHAR2(55), change the table datatype to be VARCHAR2 and the control file to read:

numid "TRIM(:numid)"
Gary_W
  • 9,933
  • 1
  • 22
  • 40