Home » RDBMS Server » Server Utilities » strange issue in sql loader (ORACLE 11GR2 SOLARIS)
strange issue in sql loader [message #562691] Fri, 03 August 2012 07:52 Go to next message
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
Re: strange issue in sql loader [message #562692 is a reply to message #562691] Fri, 03 August 2012 08:01 Go to previous messageGo to next message
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
Re: strange issue in sql loader [message #562693 is a reply to message #562691] Fri, 03 August 2012 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: strange issue in sql loader [message #562698 is a reply to message #562693] Fri, 03 August 2012 08:37 Go to previous messageGo to next message
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
Re: strange issue in sql loader [message #562700 is a reply to message #562698] Fri, 03 August 2012 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: strange issue in sql loader [message #562702 is a reply to message #562700] Fri, 03 August 2012 09:23 Go to previous messageGo to next message
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
Re: strange issue in sql loader [message #562706 is a reply to message #562702] Fri, 03 August 2012 10:10 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The issue is that you declare LOGS_REC_CNT as INTEGER and me as CHAR.

Regards
Michel
Previous Topic: count of data in sql loader
Next Topic: Exporting schema using consistent parameter
Goto Forum:
  


Current Time: Thu Mar 28 17:36:43 CDT 2024