Paul,
In your external table creation scrip, just replace the clause:
fields terminated by ','
With:
fields (
JAN position(2:5) VARCHAR2(4 BYTE),
FEB position(9:18) VARCHAR2(10 BYTE),
JAN position(22:29) VARCHAR2(8 BYTE)
)
Thanks & Regards,
Manash Partim Baruah
Collaboration Platform LCS-E-commerce| DBA Team| Tech Mahindra Ltd.|
Sharda Center, 6th Floor Annex Building, Pune-4, India
www.techmahindra.com
-----Original Message-----
From: pauline [mailto:pauline.[Email address protected]
Sent: Tuesday, January 08, 2008 7:12 PM
To: Manash Baruah
Subject: RE: export schema
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email, **and removed
any attachments, and kept your email address secret **from this person,
and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal **Anything below this line is the original email text
Hi Manash,
This is from the output listing all the rejected rows, the actual file
is too big and takes too long to open.
"0271","0271040468","20050412"
"0271","0271038902","20050412"
"0271","0271038902","20050412"
"0271","0271035719","20020725"
I hope this helps.
oracledba-ezmlmsh
ield-x84562866.x1
[Email address protected]
To
.com [Email address protected]
cc
2008/01/08 03:27
PM
Subject
RE: export schema
Paul,
Can you send the first few lines/records of the flat file you are
using...
Thanks & Regards,
Manash Partim Baruah
Collaboration Platform LCS-E-commerce| DBA Team| Tech Mahindra Ltd.|
Sharda Center, 6th Floor Annex Building, Pune-4, India
www.techmahindra.com
-----Original Message-----
From: pauline
[mailto:oracledba-ezmlmshield-x78624381.[Email address protected]
Sent: Tuesday, January 08, 2008 6:51 PM
To: LazyDBA Discussion
Subject: RE: export schema
I get the CHAR(255) from the Error Log, my assumption is that it gets
them from the flat file(.csv), which is exactly what I need to
understand. How does the flat file manage to change the size from
varchar2(4) to char(255).
maybe that's because it is a flat file after all, but how do I change
that back to the required size? 255 is rather too big for a column that
contains only 4 characters. Or am I missing something?
pauline
oracledba-ezmlmsh
ield-x79424671.x1
[Email address protected]
To
.com [Email address protected]
cc
2008/01/08 03:09
PM
Subject
RE: export schema
Maybe it's me getting confused.
Your data source definition is as follows
JAN CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
FEB CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
MAR CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader .
I'm seeing 255 here and assuming that's what you require. I could have
the wrong end of the stick
M.
-----Original Message-----
From: pauline
[mailto:oracledba-ezmlmshield-x81644388.[Email address protected]
Sent: 08 January 2008 13:04
To: LazyDBA Discussion
Subject: RE: export schema
Now you guys are really confusing me.
Let me get this right.
When my souce table specifies 4 bytes, and my destination table
specifies 4bytes, the external table has to be 255bytes as per the error
I had previously posted?
Will that not affect my destination table during the "insert from
select".
Will that also not moarn about the characters?
Pauline
oracledba-ezmlmsh
ield-x10202660.x1
[Email address protected]
To
.com [Email address protected]
cc
2008/01/08 02:43
PM
Subject
RE: export schema
But you specifically defined your external table with 4 byte 10 byte
etc.
This should be 255 (looking at your data source definition).
-----Original Message-----
From: pauline
[mailto:oracledba-ezmlmshield-x26054606.[Email address protected]
Sent: 08 January 2008 12:09
To: LazyDBA Discussion
Subject: RE: export schema
Thanks guys, but increasing the size does not answer my question. The
table that produces the flat file, has the same structure as the one
importing the data. This means that the source table is similar to the
destination table, (except that one is on SQL and the other on Oracle).
What I want to know is why does it change?
I previously used SQL*Loader and I didn't have to change the sizes.
I really appreciate your assistence guys. Thank you in advance.
Regards,
Pauline
oracledba-ezmlmsh
ield-x13019177.x1
[Email address protected]
To
.com [Email address protected]
cc
2008/01/08 01:04
PM
Subject
RE: export schema
HI,
I think it's the fact that your data is longer than the column
definition you've given for the external table.
M.
-----Original Message-----
From: pauline
[mailto:oracledba-ezmlmshield-x1964267.[Email address protected]
Sent: 08 January 2008 10:58
To: LazyDBA Discussion
Subject: RE: export schema
Thank you Viswa, Amit
I have created the external table, now the problem comes when I have to
select data from it. I get an ORA-01401.
It looks like the datatypes are different from those I specified on the
external table. DO you have any idea why this could be the case?
This is the structure of my external table:
create table pauline.xternal_tester
( JAN VARCHAR2(4 BYTE),
FEB VARCHAR2(10 BYTE),
MAR VARCHAR2(8 BYTE)
)
organization external
( default directory xternal_data_dir
access parameters
( records delimited by newline
fields terminated by ','
)
location ('tester.csv')
)
reject limit unlimited;
This is the output of the LOG:
Field Definitions for table XTERNAL_TESTER
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
JAN CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
FEB CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
MAR CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader .
.
error processing column JAN in row 1 for datafile c:\temp\tester.csv
ORA-01401: inserted value too large for column
oracledba-ezmlmsh
ield-x80803713.x1
[Email address protected]
To
.com [Email address protected]
cc
2008/01/08 11:36
AM
Subject
RE: export schema
**********************************************************************
This e-mail is confidential and may be privileged.
It may only be read, copied and used by the intended recipient.
If you have received it in error please contact the sender immediately
by return e-mail. Please then delete the e-mail and any copies of it and
do not use or disclose its contents to any person.
**********************************************************************
Registered Office: Farncombe House, Broadway, Worcestershire, WR12 7LJ
AccuRead Limited Registered Number: 3076187 England GSL UK Limited
Registered Number: 3333860 England Global Solutions Limited Registered
Number: 3189802 England
**********************************************************************
============================================================================================================================
Disclaimer:
This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at <a href="http://www.techmahindra.com/Disclaimer.html">http://www.techmahindra.com/Disclaimer.html</a> externally and <a href="http://tim.techmahindra.com/Disclaimer.html">http://tim.techmahindra.com/Disclaimer.html</a> internally within Tech Mahindra.
============================================================================================================================
Oracle LazyDBA home page