Oracle10gXe - the size problem

[Terence]

I am getting this error when executing sql below, altough i check before with several cmds: ( fSongFieldSize = 60 )

song:= Copy(trackInfoRec.song,1,fSongFieldSize);
SetLength(song,fSongFieldSize);

fSongInsQuery.Params.ParamValues[SongColName]:='';
fSongInsQuery.Params.ParamValues[SongColName]:= song;

[Exception: SQL Error: ORA-12899: Wert zu groß für Spalte "FREEDB"."SONGS"."SONG" (aktuell: 61, maximal: 60)

This is more than strange, because debugger also says string only has length of 60, even if i shorten it to 60-1 the exception raises.
Does anyone had similar problems?
Furthermore it seems that i only have the problem for special strings of length (60), because some of same length are already written to db.
Does it maybe has sth to do with the charset?
NLS_CHARACTERSET AL32UTF8, NLS_NCHAR_CHARACTERSET AL16UTF16

Any help is appreciated..

[firmos]

Hello Fabian,

a unicode charakter Set needs two bytes, mabe you should check this.
Try to set your column encoding to unicode if oracle supports this ?

hth,
helmut

[tomazzupan]

[Exception: SQL Error: ORA-12899: Wert zu groß für Spalte "FREEDB"."SONGS"."SONG" (aktuell: 61, maximal: 60)

NLS_CHARACTERSET AL32UTF8, NLS_NCHAR_CHARACTERSET AL16UTF16

Unicode needs more than one byte for certain characters. Before you create your tables execute this:
alter session set nls_length_semantics=char

This is only necessary while creating tables.

LP, Tomaž

[pol]

I Think the easiest way to avoid this would modify column song to varchar2(120). You know, with varchar2 only the "filled" amount of space is occupied, nothing is wasted.

hth
Rüdiger

[Terence]

Because i want to be independet of what length the db admin has defined for my field i check at initialization how long the field is via:

query.SQL.Append('SELECT '+SongColName);
query.SQL.Append('FROM '+SongTableName);
if not guessDbEmpty
then query.SQL.Append('WHERE '+SongIdColName+'=1');
query.Open;
fSongFieldSize:= query.FieldDefs.Find(SongColName).Size;
query.Close;query.Sql.Clear;

Then later i just use ...
song:= Copy(trackInfoRec.song,1,fSongFieldSize);
SetLength(song,fSongFieldSize);

..to make sure i got problems, but as i already imagined it is necesarry to calculate the allowed size dependet on the charset.
Does someone has an solution/idea how to do that, or a complete other way. That hast to work on all dbms (supported by zeos)!

Tx for hlp.

[pol]

Not a bad trick. But to be shure what the actual length in bytes is, use the lengthb function in a query. This works of course only for Oracle, so it is not what you really want. Hard to try to be dbms-independent.

[Terence]

Hi Toma!

Unicode needs more than one byte for certain characters. Before you create your tables execute this:
alter session set nls_length_semantics=char
This is only necessary while creating tables.

I tried this, but error still remains, instead i get another length for the error - constantly 64, see

" [Exception: SQL Error: ORA-12899: Wert zu groß für Spalte "FREEDB"."SONGS"."SONG" (aktuell: 64, maximal: 60)

[Terence]

BTw howto set charset in oracle and which one do i have for choice?
Like this?
alter session set NLS_CHARACTERSET=AL32UTF8
alter session set NLS_NCHAR_CHARACTERSET=AL16UTF16;
8=1byte, 16=2byte
What is difference between NLS_NCHAR_CHARACTERSET and NLS_CHAR_CHARACTERSET?

[pol]

You can not change the character set after database creation, only by doing an export > create a new database with the new character set > import.
Understandably, as the present character set is "physically there". Still more so, if you want to change from a 1 byte to a 2 byte characterset. Do you want the database to "explode", changing instantly all characters from 1 to 2 bytes? And only for a session?
What you probably want ist the database to behave "as if" it had an 1 bit characterset, so zjat the data fits into your field. I didn't find such a parameter. Would be nice.

I didn't find a definition for NLS_NCHAR_CHARACTERSET and NLS_CHAR_CHARACTERSET (but didn't serch very thoroughly), I would also like to know it.

Greetings,
Rüdiger

[Terence]

I think you misunderstood me. I never wanted to change the characterset for an existing ddt or moreover the data.-
I just wanted to know how to set charset before i create the shema - as you said.
Tx anyway.

[tomazzupan]

B
What is difference between NLS_NCHAR_CHARACTERSET and NLS_CHAR_CHARACTERSET?

NLS_CHAR_CHARACTERSET is used with char and varchar datatypes,
NLS_NCHAR_CHARACTERSET is used with nchar and nvarchar datatypes.
NCHAR and NVARCHAR2 datatypes store only Unicode character data.

LP, Tomaž

[tomazzupan]

Hi Toma!

Unicode needs more than one byte for certain characters. Before you create your tables execute this:
alter session set nls_length_semantics=char
This is only necessary while creating tables.

I tried this, but error still remains, instead i get another length for the error - constantly 64, see

Did you altered session *before* creating tables? If tables were created without this setting then length semantics for tables is set to byte.

LP, Tomaž

[Terence]

Yes, i did and then i used my "old" code again, but as i said the error remains.
Because i would need a code to limit a delphi string in length such way i know how many bytes it will ned in unicode later. That the only way i can avoid trying to commit a "too" long string to the db shema!