Michael,
You are right. Here 1 Char = 1 Byte.
When a we declare a variable, say, NAME VARCHAR2(5), we normally expect
it to take a name of maximum 5 characters in length. This is valid for
single byte characters. But, when we expect the same variable to store a
name 5 characters long but characters are multi-byte characters, then we
can do the same by using a declaration: NAME VARCHAR2(5 CHAR). So, in
the first case it was taking a declaration: NAME VARCHAR2(5 BYTE)
This behaviour is controlled by the NLS parameter NLS_LENGTH_SEMANTICS.
If the value of this parameter is BYTE (by default) or CHAR then, the
declaration of a variable will follow that behaviour accordingly if we
declare it like: NAME VARCHAR2(5). We can override NLS_LENGTH_SEMANTICS
by specifying BYTE or CHAR in the current scope.
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: Michael Peel
[mailto:oracledba-ezmlmshield-x25051309.[Email address protected]
Sent: Tuesday, January 08, 2008 5:07 PM
To: LazyDBA Discussion
Subject: RE: export schema
My understanding of the statement (I've just tested it) is that 4 bytes
equates to 4 chars. Try changing that to 255 and it should be fine.
I have to say I haven't seen that definition (4 byte) before, so I may
be talking out of my bottom..
M.
-----Original Message-----
From: Pravin Subramanian
[mailto:oracledba-ezmlmshield-x75361503.[Email address protected]
Sent: 08 January 2008 11:18
To: LazyDBA Discussion
Subject: RE: export schema
Hi Michael,
Pauline has specified 4 bytes (256 characters) as the limit. So I don't
understand why must an error be prompted in such a situation. Please do
let
me know.
Thanks and Regards,
Pravin
-----Original Message-----
From: Michael Peel
[mailto:oracledba-ezmlmshield-x13019177.[Email address protected]
Sent: Tuesday, January 08, 2008 4:34 PM
To: LazyDBA Discussion
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 Removed]
.com [Email address protected]
cc
2008/01/08 11:36
AM
Subject
RE: export schema
Hi Pauline,
Viswa is right. If you need more help, plz have a look at the below
link. i
think it will help you a lot.
http://blog.econtentpark.com/2006/08/07/Import-of-a-Excel-or-any-other-f
ile-into-Oracle/
Regards,
Amit
**********************************************************************
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
**********************************************************************
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
============================================================================================================================
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