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 Go to next message
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 #539793 is a reply to message #539790] Wed, 18 January 2012 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58860
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is my ctl file is wrong

Maybe yes or maybe no and your data file is wrong.
What is sure is that the control file and the data file do not match.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Post ALL what is necessary for us to execute what you did.

Regards
Michel

[Updated on: Wed, 18 January 2012 10:26]

Report message to a moderator

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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10615
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

icon8.gif  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 Go to previous messageGo to next message
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

Re: Problem in loading in data with the help of sqlldr [message #539819 is a reply to message #539817] Wed, 18 January 2012 11:39 Go to previous message
Mahesh Rajendran
Messages: 10615
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Well, I cannot comment on data that I do not see .
If you think the data is valid (not too long to fit into the column) then

Did you transfer the input datafile using ftp?
Probably the file is corrupt. See metalink note ID 725966.1
Previous Topic: Installation error
Next Topic: File transfer from Unix machine to Windows machine
Goto Forum:
  


Current Time: Fri Aug 22 06:55:45 CDT 2014

Total time taken to generate the page: 0.08621 seconds