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

Question

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

show the the CTL file AND the data you're trying to load - also which schema does the table belong to vs which user are you running sqlldr as ? — thatjeffsmith
– thatjeffsmith, Commented May 23, 2018 at 20:21

Littlefoot · Accepted Answer · 2018-05-23 20:24:30Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related