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
Last activity: Sep 19, 2025, 02:47 PM