search

ORA-00984: column not allowed here in SQL*Loader

1 anos atrás
Comentários: 0
Visualizações: 130

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 PMI_DATA_SPRINT_REPORTER.PERSON, column DATETIME_OF_BIRTH. ORA-00984: column not allowed here

Show

Tried to subvstr 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)

CSV with an example record(unsure how to put an actual file here..)

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  1934-06-30T00:00:00-05:00   8527    38003564    0   180930  0       1:FEMALE    44394   26:WHITE    47289   10:NOT HISPANIC OR LATINO   44331

ORA-00984: column not allowed here in SQL*Loader

Last updated on FEBRUARY 23, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data.
Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Symptoms

Using the control file:

LOAD DATA
INFILE '\<DIR>\TST_SQLLoader.TXT'
BADFILE '%BAD_FILE%'
DISCARDFILE '%DIS_FILE%'
REPLACE
INTO TABLE <TABLE_NAME>
FIELDS TERMINATED BY ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
SUBLOB,
LOB,
ACCOUNT,
ACCOUNT_TYPE,
TRANSACTION_ID,
PROPERTY,
BREAKOUT,
FLATPERIODUS,
PERIOD,
VALUE,
IDB_CREAT_TS "SYSTIMESTAMP",
FILE_NM "TST_SQLLoader"
)


to load the data into a table the following error is reported by SQL*Loader:

 
FILE_NM "TST_SQLLoader" column is causing the error. The load works fine Without "_".

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

How do I fix column not allowed here in Oracle?

ORA-00984: Column Not Allowed Here tips.
An ORA-00984 will be thrown if a column name (like in the VALUES clause of an INSERT statement), is used in an expression where it is not permitted. ... .
To correct ORA-00984, you simply need to view the syntax of the SQL statement and only use column names where they are appropriate..

What does column not allowed here mean?

The ORA 00984: column not allowed here error occurs when a column is not allowed in the VALUES clause of the insert query, the column name is used in VALUES clause or missing quotes in the insert statement. The VALUES clause should contain values that will be stored in the table.

ora-00984 column allowed sql*loader ora-00984 create table

Postagens relacionadas

How many factors of 400 are not multiples of 2
How many factors of 400 are not multiples of 2

A relatively new discipline, motion graphics began with
A relatively new discipline, motion graphics began with

A policy loan is made possible by
A policy loan is made possible by

Its a small world meaning
Its a small world meaning

Which of the following nutrients yield energy?
Which of the following nutrients yield energy?

What was the significance of the first continental congress in 1774?
What was the significance of the first continental congress in 1774?

When was Fairfield CA founded?
When was Fairfield CA founded?

When assessing the intensity of a patients pain, which question by the nurse is appropriate
When assessing the intensity of a patients pain, which question by the nurse is appropriate

How many liters of water must be added to 16 liters of milk and water contains 10% water to make it 20% water in it?
How many liters of water must be added to 16 liters of milk and water contains 10% water to make it 20% water in it?

How many spectral lines are obtained when an electron drops from 4th Shell to ground shell?
How many spectral lines are obtained when an electron drops from 4th Shell to ground shell?

Publicidade

ÚLTIMAS NOTÍCIAS

Best way to season ribs for the oven
1 anos atrás . por StylizedIntermission
Where to buy sk8 the infinity manga
1 anos atrás . por WholesaleManga
Esqueci de tomar o antibiótico na hora certa o que fazer
1 anos atrás . por ProMusician
What is the correct order in which sound waves are transmitted through the ear I tympanic membrane II ossicles III external auditory canal IV cochlea?
1 anos atrás . por NutritiousAlligator
Which is the correct order of events of sound transmission through the ear 1 sound waves strike the eardrum?
1 anos atrás . por UsedBroth
2022 mazda cx-5 grand touring reserve vs signature
1 anos atrás . por BurlyProjection
How do you make glasses on little alchemy?
1 anos atrás . por HomemadeHeadset
What is the 5 benefits in planting trees?
1 anos atrás . por FavoredPlurality
The Nine Lives of Chloe King Alek
1 anos atrás . por SignificantPriesthood
Where can I watch Rick and Morty
1 anos atrás . por AntisepticCollege

Publicidade

Populer

Publicidade

Sobre

Jurídica

Ajuda

Social

homeendefrjakoptzhhi
direito autoral © 2024 cemle Inc.