0

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

1 Answer 1

0

The error message means "A column name was used in an expression where it was not permitted, such as in the VALUES clause of an INSERT statement."

I suspect it may be the use of double quotes " " around the data instead of single quotes ' '. Double quotes are generally reserved for identifiers - table or column names etc.

1
  • Thank you for replying. I tried taking out the quotes and am still receiving the same error. I'll keep trying. Commented Sep 19 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.