Sample Header Ad - 728x90

SQL Loader rejecting date: column not allowed here When importing date

0 votes
1 answer
14 views
I am trying to import a file using SQL Loader in Toad and am receiving an error message "ORA-00984 column not allowed here". I previously was getting an error message that there was a non-numeric value and now it's switched to this. The last thing I tried was using TO_DATE(substr(:EFFECTIVEDATE,1,10),'MM-DD-YYYY') I've tried many other things that I don't remember anymore. I've been at this for 2.5 hours. Below is the table information, ctl information and a few records. One of my other concerns is that some of the dates are in "m/d/yyyy" format. Here's what I'm using to create the table:
Create Table saphrdw.VP_ACCRUALTRANV42
(PERSONFULLNAME             nVarChar2(64),
PERSONNUM                   nVarChar2(15),
PAYCODENAME                 nVarChar2(50),
ACCRUALCODENAME             nVarChar2(50),
ACCRUALCODESHORTNM          Char(25),
TOACCRUALCODENAME             nVarChar2(50),
EFFECTIVEDATE           date,
ACCRUALTRANTYPE          Number(10),
ACCRUALTRANTYPENM          nVarChar2(39),
ACCRUALTRANAMOUNT          Number(16,6),
LIMITAMOUNT          Number(16,6),
GRANTAMOUNT          Number(16,6),
CARRYFORWARDAMOUNT          Number(16,6),
ORIGINALDATE          Date,
ENTEREDONDTM          Date,
PROBATIONAMOUNT          Number(16,6),
TOPERSONFULLNAME          nVarChar2(64),
TOPERSONNUM          nVarChar2(15),
ACCRUALCODETYPE          Number(10),
HOURSPERDAYINSECS          Number(10),
ONPROBATIONSW          Number(10),
DISQUALIFIEDSW          Number(10),
SUSPENDEDSW          Number(10),
EXCLUDEDSW          Number(10),
EDITBYRESETSW          Number(10),
ALLOWEDITSW          Number(10),
CURRPAYPERIODSTART          Date,
CURRPAYPERIODEND          Date,
PREVPAYPERIODSTART          Date,
PREVPAYPERIODEND          Date,
NEXTPAYPERIODSTART          Date,
NEXTPAYPERIODEND          Date,
EMPLOYEEID          Number(10),
TOEMPLOYEEID          Number(10),
TOACCRUALCODEID          Number(10),
ACCRUALTRANID          Number(10),
ACCRUALCODEID          Number(10),
PAYCODEID          Number(10),
DATASOURCEID          Number(10),
PERSONID          Number(10),
CLIENTUSERNAME          nVarChar2(70),
FUNCTIONCODE          nVarChar2(2),
DEVICENAME          nVarChar2(50),
ENDDATE          Date,
FUNCTAREAID          Number);
Here's the ctl code:
INTO TABLE SAPHRDW.VP_ACCRUALTRANV42
INSERT 
FIELDS TERMINATED BY ',' optionally enclosed by '"'
TRAILING NULLCOLS

(PERSONFULLNAME,
PERSONNUM,
PAYCODENAME,
ACCRUALCODENAME,
ACCRUALCODESHORTNM,
TOACCRUALCODENAME,
EFFECTIVEDATE "mm/dd/yyyy",
ACCRUALTRANTYPE,
ACCRUALTRANTYPENM,
ACCRUALTRANAMOUNT,
LIMITAMOUNT,
GRANTAMOUNT,
CARRYFORWARDAMOUNT,
ORIGINALDATE,
ENTEREDONDTM,
PROBATIONAMOUNT,
TOPERSONFULLNAME,
TOPERSONNUM,
ACCRUALCODETYPE,
HOURSPERDAYINSECS,
ONPROBATIONSW,
DISQUALIFIEDSW,
SUSPENDEDSW,
EXCLUDEDSW,
EDITBYRESETSW,
ALLOWEDITSW,
CURRPAYPERIODSTART,
CURRPAYPERIODEND,
PREVPAYPERIODSTART,
PREVPAYPERIODEND,
NEXTPAYPERIODSTART,
NEXTPAYPERIODEND,
EMPLOYEEID,
TOEMPLOYEEID,
TOACCRUALCODEID,
ACCRUALTRANID,
ACCRUALCODEID,
PAYCODEID,
DATASOURCEID,
PERSONID,
CLIENTUSERNAME,
FUNCTIONCODE,
DEVICENAME,
ENDDATE,
FUNCTAREAID)
Here are a few lines I'm trying to insert:
PERSONFULLNAME,PERSONNUM,PAYCODENAME,ACCRUALCODENAME,ACCRUALCODESHORTNM,TOACCRUALCODENAME,EFFECTIVEDATE,ACCRUALTRANTYPE,ACCRUALTRANTYPENM,ACCRUALTRANAMOUNT,LIMITAMOUNT,GRANTAMOUNT,CARRYFORWARDAMOUNT,ORIGINALDATE,ENTEREDONDTM,PROBATIONAMOUNT,TOPERSONFULLNAME,TOPERSONNUM,ACCRUALCODETYPE,HOURSPERDAYINSECS,ONPROBATIONSW,DISQUALIFIEDSW,SUSPENDEDSW,EXCLUDEDSW,EDITBYRESETSW,ALLOWEDITSW,CURRPAYPERIODSTART,CURRPAYPERIODEND,PREVPAYPERIODSTART,PREVPAYPERIODEND,NEXTPAYPERIODSTART,NEXTPAYPERIODEND,EMPLOYEEID,TOEMPLOYEEID,TOACCRUALCODEID,ACCRUALTRANID,ACCRUALCODEID,PAYCODEID,DATASOURCEID,PERSONID,CLIENTUSERNAME,FUNCTIONCODE,DEVICENAME,ENDDATE,FUNCTAREAID
"M, P",20316,NULL,Vacaciones 2024,NULL,NULL,1/1/2027,16,GRANT EXPIRATION,-162000,0,162000,NULL,00:00.0,NULL,0,NULL,NULL,2,32400,NULL,0,0,0,0,0,00:00.0,00:00.0,00:00.0,00:00.0,00:00.0,00:00.0,63705,NULL,NULL,77267037,210,NULL,NULL,63705,NULL,NULL,NULL,NULL,NULL
"H, T",99664,Vacation,Vacation Earned,VCE,NULL,12/31/2026,1,TAKEN,-43200,NULL,NULL,NULL,00:00.0,23:32.4,NULL,NULL,NULL,1,28800,NULL,0,0,0,0,1,00:00.0,00:00.0,00:00.0,00:00.0,00:00.0,00:00.0,147650,NULL,NULL,83382465,6,178,1036661,147650,ARPERC1,E,NULL,NULL,2
"H, T",99664,Vacation,Vacation Available,VCA,NULL,12/31/2026,1,TAKEN,-43200,NULL,NULL,NULL,00:00.0,23:32.4,NULL,NULL,NULL,1,28800,NULL,0,0,0,0,1,00:00.0,00:00.0,00:00.0,00:00.0,00:00.0,00:00.0,147650,NULL,NULL,83382468,5,178,1036661,147650,ARPERC1,E,NULL,NULL,2
"L, G",10020,Vacation,Vacation Earned,VCE,NULL,12/31/2026,1,TAKEN,-43200,NULL,NULL,NULL,00:00.0,18:40.7,NULL,NULL,NULL,1,28800,NULL,0,0,0,0,1,00:00.0,00:00.0,00:00.0,00:00.0,00:00.0,00:00.0,147752,NULL,NULL,83382657,6,178,1036661,147752,ARPERC1,E,NULL,NULL,2
"L, G",10020,Vacation,Vacation Available,VCA,NULL,12/31/2026,1,TAKEN,-43200,NULL,NULL,NULL,00:00.0,18:40.7,NULL,NULL,NULL,1,28800,NULL,0,0,0,0,1,00:00.0,00:00.0,00:00.0,00:00.0,00:00.0,00:00.0,147752,NULL,NULL,83382659,5,178,1036661,147752,ARPERC1,E,NULL,NULL,2
"L, G",10020974,Vacation,Vacation Earned,VCE,NULL,12/30/2026,1,TAKEN,-14400,NULL,NULL,NULL,00:00.0,18:40.7,NULL,NULL,NULL,1,28800,NULL,0,0,0,0,1,00:00.0,00:00.0,00:00.0,00:00.0,00:00.0,00:00.0,147752,NULL,NULL,83382656,6,178,1036661,147752,ARPERC1,E,NULL,NULL,2
Asked by Stephanie (1 rep)
Sep 18, 2025, 09:33 PM
Last activity: Sep 19, 2025, 02:47 PM