OraFAQ Forum: Server Utilities » strange issue in sql loader

Home » RDBMS Server » Server Utilities » strange issue in sql loader (ORACLE 11GR2 SOLARIS)

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

strange issue in sql loader [message #562691]

Fri, 03 August 2012 07:52

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Hi All.

My sql loader is loading data incorrectly and doesn't give any clue, can you please help. i have 2 column as below , loader can't load 2nd column correctly, please see the loaded data in oracle at last

93625 | SC_RIO_5CQC_20120802062414230.LOG
45036 | SC_RIO_5CQM_20120802062418027.LOG
156882 | SC_RIO_5FEC_20120802062421060.LOG
94007 | SC_RIO_5FEM_20120802062426683.LOG
57322 | SC_RIO_5L1C_20120802062430667.LOG

table
[CREATE TABLE RIO_AUDIT_RECORD
(
TBL_FILE_NAME VARCHAR2(150 BYTE) NULL,
TBL_REC_COUNT NUMBER(10) NULL,
LOGS_FILE_NAME VARCHAR2(150 BYTE) NULL,
LOGS_REC_CNT NUMBER(10) NULL,
DATE_LOADED DATE NULL
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
[/code]

control file

load data
        append into table RIO_AUDIT_RECORD
        FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
        TRAILING NULLCOLS
        (
        LOGS_FILE_NAME          CHAR,
        LOGS_REC_CNT          INTEGER,
        DATE_LOADED SYSDATE
)

see the sample date how it loads

Row# TBL_FILE_NAME TBL_REC_COUNT LOGS_FILE_NAME LOGS_REC_CNT DATE_LOADED

1 93625 542327647 03/08/2012 13:45:08
2 45036 542327647 03/08/2012 13:45:08
3 156882 542327647 03/08/2012 13:45:08
4 94007 542327647 03/08/2012 13:45:08

Report message to a moderator

Re: strange issue in sql loader [message #562692 is a reply to message #562691]

Fri, 03 August 2012 08:01

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

When i change the sequence in control file the data is different this time
Row# TBL_FILE_NAME TBL_REC_COUNT LOGS_FILE_NAME LOGS_REC_CNT DATE_LOADED

6 75 858994226 03/08/2012 13:59:54
7 67 842347314 03/08/2012 13:59:54
8 08 825635637 03/08/2012 13:59:54
9 SC_RIO_5qjc_20120802062502323.LOG 825761916 03/08/2012 13:59:54
10 SC_RIO_5Z4C_20120802062503090.LOG 876028028 03/08/2012 13:59:54

Report message to a moderator

Re: strange issue in sql loader [message #562693 is a reply to message #562691]

Fri, 03 August 2012 08:06

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

Senior Member
Account Moderator

        LOGS_FILE_NAME          CHAR,
        LOGS_REC_CNT          INTEGER,

You have the opposite in the data your posted: first the count then the name.

Regards
Michel

Report message to a moderator

Re: strange issue in sql loader [message #562698 is a reply to message #562693]

Fri, 03 August 2012 08:37

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Hi ,

Yes it is , but i change the sequence but no luck , i have posted the 2nd solution data as well

Report message to a moderator

Re: strange issue in sql loader [message #562700 is a reply to message #562698]

Fri, 03 August 2012 08:53

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

Senior Member
Account Moderator

SQL> select * from RIO_AUDIT_RECORD;

no rows selected

SQL> host sqlldr michel/michel control=t.ctl

SQL*Loader: Release 10.2.0.4.0 - Production on Ven. Août 3 15:51:56 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Commit point reached - logical record count 5

SQL> select * from RIO_AUDIT_RECORD;
TBL_FILE_NAME
-----------------------------------------------------------------------------
TBL_REC_COUNT
-------------
LOGS_FILE_NAME
-----------------------------------------------------------------------------
LOGS_REC_CNT DATE_LOADED
------------ -------------------


SC_RIO_5CQC_20120802062414230.LOG
       93625 03/08/2012 15:51:56


SC_RIO_5CQM_20120802062418027.LOG
       45036 03/08/2012 15:51:56


SC_RIO_5FEC_20120802062421060.LOG
      156882 03/08/2012 15:51:56


SC_RIO_5FEM_20120802062426683.LOG
       94007 03/08/2012 15:51:56


SC_RIO_5L1C_20120802062430667.LOG
       57322 03/08/2012 15:51:56

5 rows selected.

SQL> host type t.ctl
LOAD DATA
INFILE *
INTO TABLE RIO_AUDIT_RECORD
        FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
        TRAILING NULLCOLS
        (
        LOGS_REC_CNT          CHAR,
        LOGS_FILE_NAME          CHAR,
        DATE_LOADED SYSDATE
)
BEGINDATA
93625 | SC_RIO_5CQC_20120802062414230.LOG
45036 | SC_RIO_5CQM_20120802062418027.LOG
156882 | SC_RIO_5FEC_20120802062421060.LOG
94007 | SC_RIO_5FEM_20120802062426683.LOG
57322 | SC_RIO_5L1C_20120802062430667.LOG

Regards
Michel

Report message to a moderator

Re: strange issue in sql loader [message #562702 is a reply to message #562700]

Fri, 03 August 2012 09:23

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Hi Michel,

There is no issue with the control file or sql loader, but i don't know in my case it is unable to load. i tried same thing what you have done.
see the result.
Row# LOGS_FILE_NAME LOGS_REC_CNT TBL_FILE_NAME DATE_LOADED TBL_REC_COUNT

1 93625 1396924242 03/08/2012 15:18:02
2 45036 1396924242 03/08/2012 15:18:02
3 156882 1396924242 03/08/2012 15:18:02
4 94007 1396924242 03/08/2012 15:18:02
5 57322 1396924242 03/08/2012 15:18:02

Any guess

Report message to a moderator

Re: strange issue in sql loader [message #562706 is a reply to message #562702]

Fri, 03 August 2012 10:10

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

Senior Member
Account Moderator

The issue is that you declare LOGS_REC_CNT as INTEGER and me as CHAR.

Regards
Michel

Report message to a moderator

Previous Topic:	count of data in sql loader
Next Topic:	Exporting schema using consistent parameter

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 11 18:40:43 CST 2025