Migrating from sql server to Oracle varchar length issues

Question

Im facing a strange issue trying to move from sql server to oracle. in one of my tables i have column defined by NVARCHAR(255) after reading a bit i understod that SQL server is counting characters when oracle count bytes. So i defined my table in oracle as VARCHAR(510) 255*2 = 510 But when using sqlldr to load the data from a tab delimetered text file i get en error indicating some entries had exiceeded the length of this column. after checking in the sql server using:

SELECT MAX(DATALENGTH(column))
FROM table

i get that the max data length is 510.

I do use Hebrew_CI_AS collationg even though i dont think it changes anything.... I checked in SQL Server also if any of the entries contains TAB but no... so i guess its not a corrupted data.... Any one have an idea?

EDIT After further checkup i've noticed that the issue is due to the data file (in addition to the issue solved by @Justin Cave post.

I have changed the row delimeter to '^' since none of my data contains this character and '|^|' as column delimeter.

creating a control file as follows:

load data
infile data.txt "str '^'"
badfile "data_BAD.txt"
discardfile "data_DSC.txt"
into table table
FIELDS TERMINATED BY '|^|' TRAILING NULLCOLS
(
     col1,
     col2,
     col3,
     col4,
     col5,
     col6
)

The problem is that my data contain <CR> and sqlldr expecting a stream file there for fails on the <CR>!!!! i do not want to change the data since its a textual data (error messages for examples).

Answer 1

What is your database character set

SELECT parameter, value
  FROM v$nls_parameters
 WHERE parameter LIKE '%CHARACTERSET'

Assuming that your database character set is AL32UTF8, each character could require up to 4 bytes of storage (though almost every useful character can be represented with at most 3 bytes of storage). So you could declare your column as VARCHAR2(1020) to ensure that you have enough space.

You could also simply use character length semantics. If you declare your column VARCHAR2(255 CHAR), you'll allocate space for 255 characters regardless of the amount of space that requires. If you change the NLS_LENGTH_SEMANTICS initialization parameter from the default BYTE to CHAR, you'll change the default so that VARCHAR2(255) is interpreted as VARCHAR2(255 CHAR) rather than VARCHAR2(255 BYTE). Note that the 4000-byte limit on a VARCHAR2 remains even if you are using character length semantics.

If your data contains line breaks, do you need the TRAILING NULLCOLS parameter? That implies that sometimes columns may be omitted from the end of a logical row. If you combine columns that may be omitted with columns that contain line breaks and data that is not enclosed by at least an optional enclosure character, it's not obvious to me how you would begin to identify where a logical row ended and where it began. If you don't actually need the TRAILING NULLCOLS parameter, you should be able to use the CONTINUEIF parameter to combine multiple physical rows into a single logical row. If you can change the data file format, I'd strongly suggest adding an optional enclosure character.