Since IFS Applications 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.
The Unicode database character set AL32UTF8 is the recommended database character set for IFS Applications.
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.
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'; -- or -- string_ := UNISTR('LEVERANT\00D6RSFAKTURA'); END;
The example below shows how to use ASCIISTR to find which hex code to use in UNISTR:
SELECT asciistr('Ö') FROM dual; ASCIISTR('Ö') ------------- \00D6
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.
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.
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.
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.