Unicode¶
Since IFS Cloud should only be used in an Unicode installation, there are a few initial considerations. Most of the problems encountered are concerned with string handling in PL/SQL.
Database character set¶
The Unicode database character set AL32UTF8 is the recommended database character set for IFS Cloud.
Source code¶
Files containing source code or data insertions are not allowed to use characters other than Ascii characters. If non-Ascii characters must be inserted into the database the functions Unistr
or Asciistr
should be used in these files.
PL/SQL and SQL functions¶
Please try to use functions that operates on characters, rather than functions which operate on bytes. Since Oracle uses multi-byte Unicode character sets, operating on bytes could cause data errors in the database.
Use functions:
Substr, Length, Instr, Asciistr and Unistr.
Avoid using:
Substrb, Lengthb, Instrb, Ascii and Chr.
Example of using Unistr and Asciistr:
The example below shows how to write the Swedish word "LEVERANT�RSFAKTURA" as a string where the Swedish character "�" is above ASCII 127.
DECLARE
string_ VARCHAR2(100);
BEGIN
string_ := 'LEVERANT'||UNISTR('\00D6')||'RSFAKTURA';<br/>-- or<br/>-- string_ := UNISTR('LEVERANT\00D6RSFAKTURA');<br/>END;
The example below shows how to use ASCIISTR to find which hex code to use in UNISTR:
SELECT asciistr('�') FROM dual;
ASCIISTR('�')
-------------
\00D6
Strings exceeding database maxlength¶
The database has a maximum limit for strings. For the most common data type VARCHAR2 this limit is 4000 bytes. If anyone attempts to save strings that are longer (e.g. by entering more than 4000 characters) users will see an Oracle error "ORA-1461 - can bind a LONG value only for insert into a LONG column". This error message is a bit cryptic so we have trapped it in IFS Enterprise Explorer Client which displays a clearer error message.
Oracle supplied packages¶
UTL_FILE¶
When using UTL_FILE to create a file, the server creates a file with a database character set.
e.g. If using UTF8 database character set then the file is created with UTF8 encoding.
When using UTL_FILE to read files, the server expects the encoding to be UTF8.
If the created or read file has the same encoding as the database, everything works as expected, however if they are different you have to consider the encodings that have been used.
Case insensitive search¶
Searches on unicode characters can be carried out ignoring upper or lower case requirements since functions like UPPER
and LOWER
are aware about Unicode characters.
Foreign keys against Oracle dictionary¶
If within IFS we have created tables with foreign key constraints against Oracle's dictionary, then these must be checked to ensure that we have only ASCII characters.