Home » Infrastructure » Unix » Problem in loading in data with the help of sqlldr
| Problem in loading in data with the help of sqlldr [message #539790] |
Wed, 18 January 2012 10:06  |
 |
xal_kaushal
Messages: 4
Registered: January 2012
Location: India
|
Junior Member |
|
|
Dear Friends :
Below is the data which i have to load
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 18 17:47:01 2012
Copyright © 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6
57140002205124| 23| ST04| 9418285932| 17-JAN-12 11.17.31.820253 AM| Used
54171025176597| 49.86| TU03| 9411165512| 17-JAN-12 11.20.32.943855 AM| Used
54171025182725| 49.86| TU03| 9456310464| 17-JAN-12 11.37.14.346299 AM| Used
(1) first thing i want to remove the txt which is orange txt
what syntax should i use .I am using this script to remove the txt and delete space b/w the fields ---
sed '/^$/d'FILENAME|grep -iv serialno,amount,class,msisdn,vdate,status| awk -F" " '{print$1","print$2","print$3","print$4","print$5","}
(2) my query for creating the table is
CREATE TABLE VOMSDATA
(
SERIALNO NUMBER(20),
AMOUNT NUMBER(7,2),
CLASS VARCHAR2(10),
MSISDN NUMBER(12),
VDATE TIMESTAMP(6),
STATUS VARCHAR2(8 BYTE)
)
and my control file for loading the data is
load data
infile 'path'
badfile 'path'
DISCARDFILE 'path'
truncate into table vomsdata
fields terminated by ","
trailing nullcols
(
SERIALNO,
AMOUNT,
CLASS,
MSISDN,
VDATE TIMESTAMP,
STATUS
)
When i am running my loader the loader execute and all the fiels move to the bad file
error---
Record 1: Discarded - all columns null.
Record 3: Discarded - all columns null.
Record 5: Discarded - all columns null.
Record 2: Rejected - Error on table VOMSDATA, column SERIALNO.
ORA-01722: invalid number
Record 4: Rejected - Error on table VOMSDATA, column SERIALNO.
ORA-01722: invalid number
is my ctl file is wrong
my guess is problem is with the coloured charters or vdate timestamp.
Pls guide me to resolve this issue
Regards
Kaushal
|
|
|
|
|
|
| Re: Problem in loading in data with the help of sqlldr [message #539796 is a reply to message #539790] |
Wed, 18 January 2012 10:30  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Kaapi:ora magvivek$ cat someting
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 18 17:47:01 2012
Copyright © 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6
57140002205124| 23| ST04| 9418285932| 17-JAN-12 11.17.31.820253 AM| Used
54171025176597| 49.86| TU03| 9411165512| 17-JAN-12 11.20.32.943855 AM| Used
54171025182725| 49.86| TU03| 9456310464| 17-JAN-12 11.37.14.346299 AM| Used
#-- No need for grep -iv here.
#-- This piece of code could be made much simpler.
#--
Kaapi:ora magvivek$ sed '/^$/d' someting | grep -iv serialno,amount,class,msisdn,vdate,status | awk -F" " 'NR>7 {print$1$2$3$4$5}' > newFile
Kaapi:ora magvivek$ cat newFile
57140002205124|23|ST04|9418285932|17-JAN-12
54171025176597|49.86|TU03|9411165512|17-JAN-12
54171025182725|49.86|TU03|9456310464|17-JAN-12
Kaapi:ora magvivek$ cat somectl.ctl
load data
infile 'newFile.txt'
truncate into table vomsdata
fields terminated by "|"
trailing nullcols
(
SERIALNO,
AMOUNT,
CLASS,
MSISDN,
VDATE TIMESTAMP,
STATUS
)
Kaapi:ora magvivek$ sqlldr userid=dbadmin/xxxx control=somectl.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Jan 18 11:25:40 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 3
[Updated on: Wed, 18 January 2012 10:43] Report message to a moderator |
|
|
|
 Re: Problem in loading in data with the help of sqlldr [message #539817 is a reply to message #539796] |
Wed, 18 January 2012 11:28 |
|
xal_kaushal
Messages: 4
Registered: January 2012
Location: India
|
Junior Member |
|
|
Thanks mahesh
your suggestion help me a lot but
now i am loading the data it return this error ---
Record 1: Rejected - Error on table VOMSDATA, column SERIALNO.
ORA-12899: value too large for column SERIALNO (actual: 45, maximum: 25)
Record 2: Rejected - Error on table VOMSDATA, column SERIALNO.
ORA-12899: value too large for column SERIALNO (actual: 45, maximum: 25)
Record 3: Rejected - Error on table VOMSDATA, column SERIALNO.
ORA-12899: value too large for column SERIALNO (actual: 43, maximum: 25)
Record 4: Rejected - Error on table VOMSDATA, column SERIALNO.
ORA-12899: value too large for column SERIALNO (actual: 42, maximum: 25)
Record 5: Rejected - Error on table VOMSDATA, column SERIALNO.
ORA-12899: value too large for column SERIALNO (actual: 41, maximum: 25)
[EDITED by LF: removed unnecessary quote of the whole previous message]
[Updated on: Thu, 19 January 2012 01:23] by Moderator Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 11:46:37 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
