Home » RDBMS Server » Server Utilities » Problem loading data using SQL Loader (Oracle 10 G)
Problem loading data using SQL Loader [message #628121] Thu, 20 November 2014 04:59 Go to next message
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 231 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 Go to previous messageGo to next message
cookiemonster
Messages: 13630
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.
Re: Problem loading data using SQL Loader [message #628130 is a reply to message #628121] Thu, 20 November 2014 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66447
Registered: March 2007
Location: Nanterre, France, http://...
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)

Re: Problem loading data using SQL Loader [message #628135 is a reply to message #628129] Thu, 20 November 2014 05:44 Go to previous messageGo to next message
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 135 times)
Re: Problem loading data using SQL Loader [message #628138 is a reply to message #628129] Thu, 20 November 2014 05:55 Go to previous messageGo to next message
Asfakul
Messages: 43
Registered: July 2014
Member
There is no trigger on the Table. I Just created the table myself.
Re: Problem loading data using SQL Loader [message #628178 is a reply to message #628138] Thu, 20 November 2014 16:36 Go to previous message
Barbara Boehmer
Messages: 8916
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:
  


Current Time: Tue Jun 25 08:35:21 CDT 2019