Home » RDBMS Server » Server Utilities » ORA-00984: column not allowed here. date. sql loader. (Oracle 12.1.0.2.0 Centos.)
ORA-00984: column not allowed here. date. sql loader. [message #668099] Tue, 06 February 2018 14:13 Go to next message
phem0r
Messages: 2
Registered: February 2018
Junior Member
Hello

I'm trying to use SQLLoader to load csv data into a table but i am receiving the following error -
Record 1: Rejected - Error on table PERSON, column DATETIME_OF_BIRTH. ORA-00984: column not allowed here

Tried to substr and to_date the data due to only needing YYYY-MM-DD -
DATETIME_OF_BIRTH "TO_DATE(substr(DATETIME_OF_BIRTH,1,10),'YYYY-MM-DD')",
but received the same error as above.

Any suggestions are appreciated, thanks.

DDL of the table

  CREATE TABLE "PERSON" 
   (    "PERSON_ID" NUMBER(19,0), 
    "GENDER_CONCEPT_ID" NUMBER(19,0), 
    "YEAR_OF_BIRTH" NUMBER(19,0), 
    "MONTH_OF_BIRTH" NUMBER(19,0), 
    "DAY_OF_BIRTH" NUMBER(19,0), 
    "DATETIME_OF_BIRTH" DATE, 
    "RACE_CONCEPT_ID" NUMBER(19,0), 
    "ETHNICITY_CONCEPT_ID" NUMBER(19,0), 
    "LOCATION_ID" NUMBER(19,0), 
    "PROVIDER_ID" NUMBER(19,0), 
    "CARE_SITE_ID" NUMBER(19,0), 
    "PERSON_SOURCE_VALUE" VARCHAR2(500 CHAR), 
    "GENDER_SOURCE_VALUE" VARCHAR2(500 CHAR), 
    "GENDER_SOURCE_CONCEPT_ID" NUMBER(19,0), 
    "RACE_SOURCE_VALUE" VARCHAR2(500 CHAR), 
    "RACE_SOURCE_CONCEPT_ID" NUMBER(19,0), 
    "ETHNICITY_SOURCE_VALUE" VARCHAR2(500 CHAR), 
    "ETHNICITY_SOURCE_CONCEPT_ID" NUMBER(19,0)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

  ALTER TABLE "PERSON" MODIFY ("ETHNICITY_CONCEPT_ID" NOT NULL ENABLE);
  ALTER TABLE "PERSON" MODIFY ("RACE_CONCEPT_ID" NOT NULL ENABLE);
  ALTER TABLE "PERSON" MODIFY ("YEAR_OF_BIRTH" NOT NULL ENABLE);
  ALTER TABLE "PERSON" MODIFY ("GENDER_CONCEPT_ID" NOT NULL ENABLE);
  ALTER TABLE "PERSON" MODIFY ("PERSON_ID" NOT NULL ENABLE);

Control file

load data 
infile 'filename.csv' "str '\r\n'"
append
into table PERSON
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
           ( 
PERSON_ID integer,
GENDER_CONCEPT_ID integer,
YEAR_OF_BIRTH integer,
MONTH_OF_BIRTH integer,
DAY_OF_BIRTH integer,
DATETIME_OF_BIRTH DATE 'YYYY-MM-DD',
RACE_CONCEPT_ID integer,
ETHNICITY_CONCEPT_ID integer,
LOCATION_ID integer,
PROVIDER_ID integer,
CARE_SITE_ID integer,
PERSON_SOURCE_VALUE CHAR(4000),
GENDER_SOURCE_VALUE CHAR(4000),
GENDER_SOURCE_CONCEPT_ID integer,
RACE_SOURCE_VALUE CHAR(4000),
RACE_SOURCE_CONCEPT_ID integer,
ETHNICITY_SOURCE_VALUE  CHAR(4000),
ETHNICITY_SOURCE_CONCEPT_ID integer)

Attached a csv with some data
  • Attachment: person.csv
    (Size: 1.71KB, Downloaded 976 times)
Re: ORA-00984: column not allowed here. date. sql loader. [message #668100 is a reply to message #668099] Tue, 06 February 2018 15:10 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
http://lmgtfy.com/?q=oracle+sql+loader+date+format+examples

CSV file does NOT contain Oracle DATE datatype with content similar to "1990-11-19T00:00:00-05:00" which contains timezone details
Re: ORA-00984: column not allowed here. date. sql loader. [message #668101 is a reply to message #668099] Tue, 06 February 2018 15:14 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from PERSON;

no rows selected

SQL> host type person.ctl
load data
infile 'person.csv' "str '\r\n'"
append
into table PERSON
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
           (
PERSON_ID char,
GENDER_CONCEPT_ID char,
YEAR_OF_BIRTH char,
MONTH_OF_BIRTH char,
DAY_OF_BIRTH char,
DATETIME_OF_BIRTH char "TO_DATE(substr(:DATETIME_OF_BIRTH,1,10),'YYYY-MM-DD')",
RACE_CONCEPT_ID char,
ETHNICITY_CONCEPT_ID char,
LOCATION_ID char,
PROVIDER_ID char,
CARE_SITE_ID char,
PERSON_SOURCE_VALUE CHAR(4000),
GENDER_SOURCE_VALUE CHAR(4000),
GENDER_SOURCE_CONCEPT_ID char,
RACE_SOURCE_VALUE CHAR(4000),
RACE_SOURCE_CONCEPT_ID char,
ETHNICITY_SOURCE_VALUE  CHAR(4000),
ETHNICITY_SOURCE_CONCEPT_ID char)

SQL> host sqlldr userid=michel/michel control=person.ctl skip=1

SQL*Loader: Release 11.2.0.4.0 - Production on Mar. FÚvr. 6 22:13:07 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 10

SQL> select * from PERSON;
 PERSON_ID GENDER_CONCEPT_ID YEAR_OF_BIRTH MONTH_OF_BIRTH DAY_OF_BIRTH DATETIME_OF_BIRTH   RACE_CONCEPT_ID
---------- ----------------- ------------- -------------- ------------ ------------------- ---------------
ETHNICITY_CONCEPT_ID LOCATION_ID PROVIDER_ID CARE_SITE_ID
-------------------- ----------- ----------- ------------
PERSON_SOURCE_VALUE
------------------------------------------------------------------------------------------------------------------------
GENDER_SOURCE_VALUE
------------------------------------------------------------------------------------------------------------------------
GENDER_SOURCE_CONCEPT_ID
------------------------
RACE_SOURCE_VALUE
------------------------------------------------------------------------------------------------------------------------
RACE_SOURCE_CONCEPT_ID
----------------------
ETHNICITY_SOURCE_VALUE
------------------------------------------------------------------------------------------------------------------------
ETHNICITY_SOURCE_CONCEPT_ID
---------------------------
1000003049              8532          1934              6           30 30/06/1934 00:00:00            8527
            38003564           0      180930            0

1:FEMALE
                   44394
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331
1000004460              8532          1945              2           16 16/02/1945 00:00:00            8527
            38003564           0      174233        72096

1:FEMALE
                   44394
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331
1000005889              8532          1974              5           21 21/05/1974 00:00:00            8527
            38003564           0      111675            0

1:FEMALE
                   44394
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331
1000010952              8532          1958              2            8 08/02/1958 00:00:00        38003598
            38003564           0      195136            0

1:FEMALE
                   44394
23:BLACK
                 47286
10:NOT HISPANIC OR LATINO
                      44331
1000023634              8532          1972              1            2 02/01/1972 00:00:00            8527
            38003564           0      165217        72694

1:FEMALE
                   44394
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331
1000024310              8532          1954              7           14 14/07/1954 00:00:00            8527
            38003564           0      179070        72666

1:FEMALE
                   44394
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331
1000026317              8532          1990             10           22 22/10/1990 00:00:00            8527
            38003564           0      177766            0

1:FEMALE
                   44394
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331
1000026722              8507          1946             11           27 27/11/1946 00:00:00        38003598
            38003564           0      140269        64602

2:MALE
                   44395
23:BLACK
                 47286
10:NOT HISPANIC OR LATINO
                      44331
1000031488              8532          1995              5           15 15/05/1995 00:00:00            8527
            38003564           0      117196            0

1:FEMALE
                   44394
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331
1000035184              8507          1990             11           19 19/11/1990 00:00:00            8527
            38003564           0      117791        72064

2:MALE
                   44395
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331

10 rows selected.

SQL> host type person.log

SQL*Loader: Release 11.2.0.4.0 - Production on Mar. FÚvr. 6 22:13:07 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   person.ctl
Data File:      person.csv
  File processing option string: "str '\r\n'"
  Bad File:     person.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table PERSON, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PERSON_ID                           FIRST     *   ,  O(") CHARACTER
                                                      O(")
GENDER_CONCEPT_ID                    NEXT     *   ,  O(") CHARACTER
                                                      O(")
YEAR_OF_BIRTH                        NEXT     *   ,  O(") CHARACTER
                                                      O(")
MONTH_OF_BIRTH                       NEXT     *   ,  O(") CHARACTER
                                                      O(")
DAY_OF_BIRTH                         NEXT     *   ,  O(") CHARACTER
                                                      O(")
DATETIME_OF_BIRTH                    NEXT     *   ,  O(") CHARACTER
                                                      O(")
    SQL string for column : "TO_DATE(substr(:DATETIME_OF_BIRTH,1,10),'YYYY-MM-DD')"
RACE_CONCEPT_ID                      NEXT     *   ,  O(") CHARACTER
                                                      O(")
ETHNICITY_CONCEPT_ID                 NEXT     *   ,  O(") CHARACTER
                                                      O(")
LOCATION_ID                          NEXT     *   ,  O(") CHARACTER
                                                      O(")
PROVIDER_ID                          NEXT     *   ,  O(") CHARACTER
                                                      O(")
CARE_SITE_ID                         NEXT     *   ,  O(") CHARACTER
                                                      O(")
PERSON_SOURCE_VALUE                  NEXT  4000   ,  O(") CHARACTER
                                                      O(")
GENDER_SOURCE_VALUE                  NEXT  4000   ,  O(") CHARACTER
                                                      O(")
GENDER_SOURCE_CONCEPT_ID             NEXT     *   ,  O(") CHARACTER
                                                      O(")
RACE_SOURCE_VALUE                    NEXT  4000   ,  O(") CHARACTER
                                                      O(")
RACE_SOURCE_CONCEPT_ID               NEXT     *   ,  O(") CHARACTER
                                                      O(")
ETHNICITY_SOURCE_VALUE               NEXT  4000   ,  O(") CHARACTER
                                                      O(")
ETHNICITY_SOURCE_CONCEPT_ID          NEXT     *   ,  O(") CHARACTER
                                                      O(")

value used for ROWS parameter changed from 64 to 13

Table PERSON:
  10 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 255060 bytes(13 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          1
Total logical records read:            10
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Mar. FÚvr. 06 22:13:07 2018
Run ended on Mar. FÚvr. 06 22:13:07 2018

Elapsed time was:     00:00:00.09
CPU time was:         00:00:00.07
Re: ORA-00984: column not allowed here. date. sql loader. [message #668102 is a reply to message #668101] Tue, 06 February 2018 15:27 Go to previous messageGo to next message
phem0r
Messages: 2
Registered: February 2018
Junior Member
Thank you, this worked perfectly, could you tell me how the colon works in this?
Re: ORA-00984: column not allowed here. date. sql loader. [message #668103 is a reply to message #668102] Wed, 07 February 2018 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The expression you give is an SQL expression which means this is an expression that is passed to the SQL engine.
The way to efficiently pass values to the SQL engine is to use bind variables.
SQL*Loader chose to use for bind variable name the same name than the field (or column) you give.
Bind variables are prefixed by a colon in SQL expressions.

Read this topic: Why does Oracle rename the bind variable?

Re: ORA-00984: column not allowed here. date. sql loader. [message #668116 is a reply to message #668099] Wed, 07 February 2018 06:37 Go to previous messageGo to next message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
Aside from your immediate question, your table design is flawed.

If you have

"DATETIME_OF_BIRTH" DATE,

then you do not need separate columns for
"YEAR_OF_BIRTH" NUMBER(19,0),
"MONTH_OF_BIRTH" NUMBER(19,0),
"DAY_OF_BIRTH" NUMBER(19,0),

And even if you did need them (you don't) there would be no need to define them as 19 digits when the max would be 4 digits.
Re: ORA-00984: column not allowed here. date. sql loader. [message #668117 is a reply to message #668116] Wed, 07 February 2018 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It could be virtual columns:
SQL> create table t (
  2  DATETIME_OF_BIRTH DATE,
  3  YEAR_OF_BIRTH number(4) as (extract(year from DATETIME_OF_BIRTH)),
  4  MONTH_OF_BIRTH number(2) as (extract(month from DATETIME_OF_BIRTH)),
  5  DAY_OF_BIRTH number(2) as (extract(day from DATETIME_OF_BIRTH))
  6  )
  7  /

Table created.

SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 DATETIME_OF_BIRTH                         DATE
 YEAR_OF_BIRTH                             NUMBER(4)
 MONTH_OF_BIRTH                            NUMBER(2)
 DAY_OF_BIRTH                              NUMBER(2)

SQL> insert into t (DATETIME_OF_BIRTH) values (date '1956-11-13');

1 row created.

SQL> select * from t;
DATETIME_OF_BIRTH   YEAR_OF_BIRTH MONTH_OF_BIRTH DAY_OF_BIRTH
------------------- ------------- -------------- ------------
13/11/1956 00:00:00          1956             11           13

1 row selected.
Re: ORA-00984: column not allowed here. date. sql loader. [message #668131 is a reply to message #668117] Thu, 08 February 2018 06:25 Go to previous messageGo to next message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
Michel Cadot wrote on Wed, 07 February 2018 07:59

It could be virtual columns:
Except that it's not. The OP gave his full CREATE TABLE statement, and not a virtual column in sight.
Now, he could redefine his table to use virtual columns, and that might be a legitimate design decision. But that's not where he's at at the moment.
Re: ORA-00984: column not allowed here. date. sql loader. [message #668134 is a reply to message #668131] Thu, 08 February 2018 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
he could redefine his table to use virtual columns, and that might be a legitimate design decision.
This was exactly my point, I perfectly know this is currently not the case as I showed how to load the present table with his data file.
And I even thought but did not posted if the application needs them otherwise there are no reasons to have them.

I'd add the current table will lead to inconsistencies sooner or later.

Re: ORA-00984: column not allowed here. date. sql loader. [message #668137 is a reply to message #668134] Thu, 08 February 2018 08:58 Go to previous message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
Sorry Michel. I mis-understood your intent. Smile
Previous Topic: loading data from twitter with sqlldr
Next Topic: DataPump errors on EXCLUDE table syntax
Goto Forum:
  


Current Time: Wed Dec 11 14:21:35 CST 2019