CODEWITHSUNDEEP
sqloracle-databasesqldatatypessql-loader
Nandhini
Nandhini

Reputation: 11

I'm trying to load data into my oracle table using sql loader

This is my table...it has Number as a column name

CREATE TABLE pwc_it_service (
  "SEQ"                  NUMBER,
  "NUMBER"               VARCHAR2(10),
  "CI_NAME"              VARCHAR2(200),
  "CI_CLASS"             VARCHAR2(200),
  "OWNED_BY_PRIMARY"     VARCHAR2(200),
  "OWNED_BY_SECONDARY"   VARCHAR2(200),
  "MANAGING_TERRITORY"   VARCHAR2(200),
  "LOS"                  VARCHAR2(100),
  "BUSINESS_UNIT"        VARCHAR2(100),
  "IMPORTED"             DATE,
  "LAST_UPDATED"         DATE
)

When i run my ctl file, i get the below error:

Record 1: Rejected - Error on table PWC_IT_SERVICE, column NUMBER.
ORA-01747: invalid user.table.column, table.column, or column specification

how can i insert the values without changing column name

Upvotes: 0

Views: 776

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

It was really a bad idea naming the column "NUMBER" (yes, double quotes and uppercase included). The fact that you can do it doesn't mean that you should do it. Now you have to deal with it and use the same syntax all the time - double quotes and uppercase. Have a look:

SQL> create table test ("NUMBER" varchar2(10));

Table created.

SQL> insert into test (number) values ('A');
insert into test (number) values ('A')
                  *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into test ("number") values ('A');
insert into test ("number") values ('A')
                  *
ERROR at line 1:
ORA-00904: "number": invalid identifier


SQL> insert into test ("NUMBER") values ('A');

1 row created.

SQL>

Do the same in the control file.

load data 
infile *
replace
into table test
( 
"NUMBER" terminated by whitespace
)

begindata
Little
Foot

How it works?

SQL> desc test
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 NUMBER                                 VARCHAR2(10)

SQL> $sqlldr scott/tiger@xe control=test02.ctl log=test02.log

SQL*Loader: Release 11.2.0.2.0 - Production on Sri Svi 23 22:23:07 2018

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

Commit point reached - logical record count 1
Commit point reached - logical record count 2

SQL> select * From test;

NUMBER
----------
Little
Foot

SQL>

Works fine.

However, I'd suggest you to rename that unforunate column. Get rid of double quotes, now and forever.

Upvotes: 1

Related Questions