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.

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:
http://68.142.116.68/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i45647

hope this helps
Brian

Dear Alvaro,

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

-gustaf neumann