CODEWITHSUNDEEP
oracle-database
PANDIA LAKSHMANAN
PANDIA LAKSHMANAN

Reputation: 27

"Error at Command Line : 1 Column : 698 Error report - SQL Error: ORA-00984: column not allowed here

INSERT INTO 
FLAG ("OPT_FLAG_KEY","H_KEY","FIRST_NAME","LAST_NAME", 
      "MIDDLE_NAME","TITLE","CREDENTIALS","ADDRESS_LINE_1",
      "ADDRESS_LINE_2","ADDRESS_LINE_3","CITY","STATE",
      "POSTAL_CODE","PHONE_NUMBER","BUSIN_PHONE","DECEASED",
      "OPT_FLAG","OPT_FLAG_DATE","SOU_KEY","SOU_FILE_ID",
      "SOU_FILE_ID_TEXT","BAT_ID","PHONE_NUMBER_SOURCE","BIRTH_DATE")
VALUES(37009326,4,'Daniel','Boyle',NULL,NULL,NULL,'368  Road',
       NULL,NULL,'Doylown','BVBV',1801,NULL,NULL,'NO','OUT',
       TO_CHAR('10-AUG-16','DD/MM/YYYY'),201,
       TO_DATE(SUBSTR('vhic_pavir_20160810.txt',12,8),YYYYMMDD),
       '2016-08-10',598441,NULL,TO_DATE('03-FEB-1952',DD-MM-YYYY));

I have run this query in my DB am getting some error like below

Error at Command Line : 1 Column : 698
Error report
SQL Error: ORA-00984: column not allowed here
00984. 00000 - "column not allowed here" *Cause:
*Action:"

Edit: this is my desc of my table:

Name                Null Type          
------------------- ---- ------------- 
OPT_FLAG_KEY             NUMBER(14)    
H_KEY                    NUMBER(14)    
FIRST_NAME               VARCHAR2(50)  
LAST_NAME                VARCHAR2(50)  
MIDDLE_NAME              VARCHAR2(50)  
TITLE                    VARCHAR2(50)  
CREDENTIALS              VARCHAR2(50)  
ADDRESS_LINE_1           VARCHAR2(100) 
ADDRESS_LINE_2           VARCHAR2(100) 
ADDRESS_LINE_3           VARCHAR2(100) 
CITY                     VARCHAR2(50)  
STATE                    VARCHAR2(20)  
POSTAL_CODE              VARCHAR2(20)  
PHONE_NUMBER             VARCHAR2(100) 
BUSIN_PHONE              VARCHAR2(100) 
DECEASED                 VARCHAR2(5)   
OPT_FLAG                 VARCHAR2(10)  
OPT_FLAG_DATE            DATE          
SOU_KEY                  NUMBER(14)    
SOU_FILE_ID              DATE          
SOU_FILE_ID_TEXT         VARCHAR2(20)  
BATCH_ID                 NUMBER(14)    
PHONE_NUMBER_SOURCE      VARCHAR2(100) 
BIRTH_DATE               DATE

Upvotes: 0

Views: 2932

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

There are several issue with your statement. First, double-quotes are not required on column names (unless you have mixed cases or space), remove them.

  • TO_CHAR('10-AUG-16','DD/MM/YYYY') -> 10-AUG-16 is a string, not a date. Thus it does not make any sense to convert a string to a string

  • TO_DATE(SUBSTR('vhic_pavir_20160810.txt',12,8),YYYYMMDD) -> Format must be in single-quotes, i.e. TO_DATE(SUBSTR('vhic_pavir_20160810.txt',12,8),'YYYYMMDD')

  • '2016-08-10' -> do you like to insert a DATE value or a string? You provided a string, not a date.

  • TO_DATE('03-FEB-1952',DD-MM-YYYY)) -> Format must be in single-quotes, see above. MM means the month number, not the month name.

Upvotes: 1

Related Questions