OraFAQ Forum: Server Utilities » Problem loading data using SQL Loader

Home » RDBMS Server » Server Utilities » Problem loading data using SQL Loader (Oracle 10 G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem loading data using SQL Loader [message #628121]

Thu, 20 November 2014 04:59

Asfakul
Messages: 43
Registered: July 2014

Member

Hi All,

I am trying to load some data into a table using SQL Loader. My control file looks like this.

load data 

            infile '/home/rmsbatch/rms_load.txt'

            truncate

            into table belk_item_on_order

            trailing nullcols

            (item char,

            on_order_ind char

            )

my sample data looks like this

100000173,null
100732030,null
100000174,null
100732031,null
100000181,null
100000240,null
100732097,null
100000260,null
100000264,null

but data is not loading properly. Please see the attached screenshot for the same.

my oracle table has the structure as below

create table belk_item_on_order 

(

ITEM VARCHAR2(25),

On_Order_Ind VARCHAR2(10)

);

/

COMMIT;

Please help me understand why the data is not loading properly.
/forum/fa/12310/0/

[mod-edit: image inserted into message body by bb]

Attachment: image001.png
(Size: 10.66KB, Downloaded 2790 times)

[Updated on: Thu, 20 November 2014 16:29] by Moderator

Report message to a moderator

Re: Problem loading data using SQL Loader [message #628129 is a reply to message #628121]

Thu, 20 November 2014 05:12

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

That's a really rubbish screenshot. It would have been better to run the query in sqlplus and then copy and paste in code tags.
So it looks like the problem is that 2nd column is getting populated with 0 rather than null?
It which case I would assume there's a trigger on the table doing that and sqlloader isn't responsible.

Report message to a moderator

Re: Problem loading data using SQL Loader [message #628130 is a reply to message #628121]

Thu, 20 November 2014 05:12

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post loader log file.
Post the result of:

select dbms_metadata.get_ddl('TABLE',upper('belk_item_on_order'),USER) from dual;

(replace USER by the schema name if the table is not in your schema)

Report message to a moderator

Re: Problem loading data using SQL Loader [message #628135 is a reply to message #628129]

Thu, 20 November 2014 05:44

Asfakul
Messages: 43
Registered: July 2014

Member

Problem is SQL Loader is showing it has loaded the data successfully but instead of loading the actual data
100000173,null
100732030,null
100000174,null
100732031,null
100000181,null

in table the data looks like this

1,0
1,0
1,0
1,0
1,0

The DML for the table is

CREATE TABLE "RMSAPPS"."BELK_ITEM_ON_ORDER" 

   (           "ITEM" VARCHAR2(25), 

                "ON_ORDER_IND" VARCHAR2(10)

   ) SEGMENT CREATION IMMEDIATE 

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 

 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "RETEK_DATA" "

I have attached the log file.

Attachment: rms_load.log
(Size: 1.55KB, Downloaded 2128 times)

Report message to a moderator

Re: Problem loading data using SQL Loader [message #628138 is a reply to message #628129]

Thu, 20 November 2014 05:55

Asfakul
Messages: 43
Registered: July 2014

Member

There is no trigger on the Table. I Just created the table myself.

Report message to a moderator

Re: Problem loading data using SQL Loader [message #628178 is a reply to message #628138]

Thu, 20 November 2014 16:36

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

You left out the line in your SQL*Loader control file (5th line below) that declares the field terminator.

load data
infile '/home/rmsbatch/rms_load.txt'
truncate
into table belk_item_on_order
fields terminated by ','
trailing nullcols
(item char,
on_order_ind char
)

[Updated on: Thu, 20 November 2014 16:38]

Report message to a moderator

Previous Topic:	IMPDP error ORA-39168
Next Topic:	Dump size is more that size available in dba_data_files

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:44:30 CST 2025