Home » RDBMS Server » Server Utilities » sql loader data isse (ORACLE 11GR2 SOLARIS)
sql loader data isse [message #568983] Thu, 18 October 2012 06:54 Go to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Dear All

My loader start and say commit reach logical records 8 as there are 8 records but donot load them and write them into badfile. there is no logs file generation happing so unable to trace.

table
CREATE TABLE ERS_NRT_SRC
(
  POLL_ID           NUMBER(10)                      NULL,
  TIME_OF_POLL      DATE                            NULL,
  SERVICE_DESC      VARCHAR2(50 BYTE)               NULL,
  LOCATION_AGENT    VARCHAR2(30 BYTE)               NULL,
  TRANSACTION_NAME  VARCHAR2(50 BYTE)               NULL,
  POLL_STATUS       NUMBER(1)                       NULL,
  RESPONSE_TIME     NUMBER(10,3)                    NULL,
  SPINE_TRG         VARCHAR2(10 BYTE)               NULL,
  MAX_POLL_ID       NUMBER(10)                      NULL,
  DATE_LOADED       DATE                            NULL
)
control file

load data
        BADFILE '/ersdg3/ERS/ERS_INPUT_LOGS/NRT/BadFiles/FILENAME'
        append into table ERS_NRT_SRC
        TRAILING NULLCOLS
        (
        POLL_ID          INTEGER EXTERNAL
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        TIME_OF_POLL     DATE
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'  "TO_TIMESTAMP(:TIME_OF_POLL,'DD/MM/YYYY HH24:MI:SSXFF')",
        SERVICE_DESC      CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        LOCATION_AGENT  CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        TRANSACTION_NAME CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        POLL_STATUS     CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        RESPONSE_TIME  DECIMAL EXTERNAL
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        SPINE_TRG       CHAR
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        MAX_POLL_ID     INTEGER EXTERNAL
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
        DATE_LOADED   "TRUNC(SYSDATE) -1"
        )

data
5586432110|18/12/2011 23:50:30|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|37.726|LONDON|5586432110^M
5634934112|18/12/2011 23:51:37|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|37.717|LONDON|5634934112^M
3297991637|18/12/2011 23:53:31|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|0.019|LONDON|3297991637^M
7865921458|18/12/2011 23:54:00|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|37.659|LONDON|7865921458^M
5275777285|18/12/2011 23:55:31|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|0.011|LONDON|5275777285^M
7122853136|18/12/2011 23:56:00|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|37.688|LONDON|7122853136^M
8453161906|18/12/2011 23:57:30|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|37.687|LONDON|8453161906^M
4562400420|18/12/2011 23:58:37|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|0.011|LONDON|4562400420^M


Any clue will help me a lot

Report message to a moderator

Re: sql loader data isse [message #568991 is a reply to message #568983] Thu, 18 October 2012 07:12 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
So turn logging and get the exact error message.
Why should we do your debugging for you?

Report message to a moderator

Re: sql loader data isse [message #568994 is a reply to message #568983] Thu, 18 October 2012 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Your data are in the form:
18/12/2011 23:50:30
And so not in the form:
'DD/MM/YYYY HH24:MI:SSXFF'
You have no subsecond part in your data.

Regards
Michel

[Updated on: Thu, 18 October 2012 07:18]

Report message to a moderator

Re: sql loader data isse [message #569002 is a reply to message #568994] Thu, 18 October 2012 08:41 Go to previous messageGo to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Hi

I have changed the date fromat and tunned on loging, but loader doesn't generate the logs and still unable to load the data.

Report message to a moderator

Re: sql loader data isse [message #569014 is a reply to message #569002] Thu, 18 October 2012 12:30 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How exactly did you "turn the logging on"? Could you post the whole loading session (copy/paste command prompt execution).

Report message to a moderator

Re: sql loader data isse [message #569021 is a reply to message #569014] Thu, 18 October 2012 13:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

Report message to a moderator

Re: sql loader data isse [message #569095 is a reply to message #569021] Fri, 19 October 2012 18:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
Your time_of_poll is a date, not a timestamp, so you should be using to_date, not to_timestamp.

Your max poll_id is terminated by "^M" not "|", so you need to change that.

Please see the corrected control file and demonstration below.

-- table:
SCOTT@orcl_11gR2> CREATE TABLE ERS_NRT_SRC
  2  (
  3    POLL_ID		 NUMBER(10)			 NULL,
  4    TIME_OF_POLL	 DATE				 NULL,
  5    SERVICE_DESC	 VARCHAR2(50 BYTE)		 NULL,
  6    LOCATION_AGENT	 VARCHAR2(30 BYTE)		 NULL,
  7    TRANSACTION_NAME  VARCHAR2(50 BYTE)		 NULL,
  8    POLL_STATUS	 NUMBER(1)			 NULL,
  9    RESPONSE_TIME	 NUMBER(10,3)			 NULL,
 10    SPINE_TRG	 VARCHAR2(10 BYTE)		 NULL,
 11    MAX_POLL_ID	 NUMBER(10)			 NULL,
 12    DATE_LOADED	 DATE				 NULL
 13  )
 14  /

Table created.


-- test.ctl control file:
load data
BADFILE 'test.bad'
append into table ERS_NRT_SRC
TRAILING NULLCOLS
(
POLL_ID INTEGER EXTERNAL
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
TIME_OF_POLL DATE
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' "TO_DATE(:TIME_OF_POLL,'DD/MM/YYYY HH24:MI:SS')",
SERVICE_DESC CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
LOCATION_AGENT CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
TRANSACTION_NAME CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
POLL_STATUS CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
RESPONSE_TIME DECIMAL EXTERNAL
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
SPINE_TRG CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
MAX_POLL_ID INTEGER EXTERNAL
TERMINATED BY "^M" OPTIONALLY ENCLOSED BY '"',
DATE_LOADED "TRUNC(SYSDATE) -1"
)

-- test.dat containing data:
5586432110|18/12/2011 23:50:30|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|37.726|LONDON|5586432110^M
5634934112|18/12/2011 23:51:37|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|37.717|LONDON|5634934112^M
3297991637|18/12/2011 23:53:31|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|0.019|LONDON|3297991637^M
7865921458|18/12/2011 23:54:00|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|37.659|LONDON|7865921458^M
5275777285|18/12/2011 23:55:31|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|0.011|LONDON|5275777285^M
7122853136|18/12/2011 23:56:00|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|37.688|LONDON|7122853136^M
8453161906|18/12/2011 23:57:30|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|37.687|LONDON|8453161906^M
4562400420|18/12/2011 23:58:37|XFACTOR_RIO|LSPLIVEA:AGENT1|RIO_NRT|0|0.011|LONDON|4562400420^M


-- load:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log data=test.dat

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Oct 19 16:18:02 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 8

-- results:
SCOTT@orcl_11gR2> select * from ers_nrt_src
  2  /

   POLL_ID TIME_OF_P SERVICE_DESC
---------- --------- --------------------------------------------------
LOCATION_AGENT
------------------------------
TRANSACTION_NAME                                   POLL_STATUS RESPONSE_TIME
-------------------------------------------------- ----------- -------------
SPINE_TRG  MAX_POLL_ID DATE_LOAD
---------- ----------- ---------
5586432110 18-DEC-11 XFACTOR_RIO
LSPLIVEA:AGENT1
RIO_NRT                                                      0        37.726
LONDON      5586432110 18-OCT-12

5634934112 18-DEC-11 XFACTOR_RIO
LSPLIVEA:AGENT1
RIO_NRT                                                      0        37.717
LONDON      5634934112 18-OCT-12

3297991637 18-DEC-11 XFACTOR_RIO
LSPLIVEA:AGENT1
RIO_NRT                                                      0          .019
LONDON      3297991637 18-OCT-12

7865921458 18-DEC-11 XFACTOR_RIO
LSPLIVEA:AGENT1
RIO_NRT                                                      0        37.659
LONDON      7865921458 18-OCT-12

5275777285 18-DEC-11 XFACTOR_RIO
LSPLIVEA:AGENT1
RIO_NRT                                                      0          .011
LONDON      5275777285 18-OCT-12

7122853136 18-DEC-11 XFACTOR_RIO
LSPLIVEA:AGENT1
RIO_NRT                                                      0        37.688
LONDON      7122853136 18-OCT-12

8453161906 18-DEC-11 XFACTOR_RIO
LSPLIVEA:AGENT1
RIO_NRT                                                      0        37.687
LONDON      8453161906 18-OCT-12

4562400420 18-DEC-11 XFACTOR_RIO
LSPLIVEA:AGENT1
RIO_NRT                                                      0          .011
LONDON      4562400420 18-OCT-12


8 rows selected.

Report message to a moderator

Re: sql loader data isse [message #617265 is a reply to message #569095] Thu, 26 June 2014 09:51 Go to previous message
abdulrahi2m2000
Messages: 1
Registered: June 2014
Location: India
Junior Member
Thank you for your valuable inputs.....

Report message to a moderator

Previous Topic: special character while loading
Next Topic: Import a single tablespace from a full export dump file
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 23 02:58:50 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.