Forum OpenACS Development: special characters in a category name (Oracle)

I've noticed that the name of a category in Oracle can't have special chars e.g. ñ á é etc.
I did some digging and found that the problem comes from a trigger on the category_translations table, inside this trigger there's a call to category_synonym.reindex, this procedure does this:

-- rebuild synonym index
v_len := length (v_name) - 2;
v_i := 1;
while (v_i <= v_len) loop
insert into category_synonym_index
values (reindex.synonym_id, substr (v_name, v_i , 3));
v_i := v_i + 1;
end loop;

The second column of category_synonym_index table is CHAR(3) in Oracle but when it finds a special char it says:

OCIStmtExecute ()': ORA-01401: inserted value too large for column

I checked this in postgres and it's exactly the same and it works, the second column of category_synonym_index table is character(3).

Does anybody knows if there's a difference on how Oracle and Postgres treat the size of a special char.

Posted by Emmanuelle Raffenne on
Hi Alvaro,

In Oracle, chars are bytes so char(3) means 3 bytes. "Special" characters like the spanish "ñ" "é" "á" use 2 bytes each when encoded in UTF8 so a string of 3 letters that contains one of these, e.g. "mañ", use 4 bytes therefore oracle complains.

Posted by Brian Fenton on
Hi Alvaro,

yeah, in Oracle, if your database character set is UTF8, some non-ASCII characters will be stored in 2 or more bytes. So if you have a PL/SQL variable defined as CHAR(40), you only have 40 bytes available, not 40 characters. The solution to this in PL/SQL is to declare all your variables as CHAR(40 CHAR) instead of simply CHAR(40). Same for VARCHAR2.

Docs here:

hope this helps

Posted by Alvaro Rodriguez on
Hi Brian,

I didn't know about this datatype in oracle, I just tested it and it works fine for me.


Posted by Gustaf Neumann on
Dear Alvaro,

it would be nice if you could provide a patch + upgrade script that we could apply to oacs-head.

-gustaf neumann

Posted by Alvaro Rodriguez on
Dear Gustaf,

That's exactly what I was thinking. I did an independent upgrade script, where do I upload it/add it, I've never done this before.

I also fixed a function in the CR for Oracle, the cr_revision table in Oracle has the field 'filename' and the content_revision.copy function does not copy this column. I was needing that column to use the proc fs::file_copy for my file-storage instance. Would this be a fix to put in head too?