1.2 Approach

In situations where PL/SQL code would interact with multi-byte character data and the same kind of data would be stored in Oracle Banking Trade Finance tables, follow below approach:

Please note the approach is suggested for 12c and above Database version only.

1. Complete the database installation and set up as per the Oracle Banking Trade Finance Database Best Practices for your Oracle Banking Trade Finance release.
2. Immediately after creating the Oracle Banking Trade Finance application user (schema), create a logon trigger as the SYS user to alter each new session so that the session-level length semantics are maintained as CHAR for the user at all times. For example, if the schema name were OBTF and the trigger was named “FCUSER_LOGON”, the trigger would look like:

   CREATE OR REPLACE TRIGGER SYS.FCUSER_LOGON

   AFTER LOGON ON OBTF.SCHEMA

   BEGIN

   EXECUTE IMMEDIATE 'alter session set nls_length_semantics=CHAR';

   END;

   /

3. Proceed with the rest of the Oracle Banking Trade Finance installation as per the installation documents.
   In situations where an operation results in Oracle Banking Trade Finance objects being invalidated, make sure the objects are re-compiled either:

   1. By the Oracle Banking Trade Finance schema user using one of the following options:
      1. Manually using ALTER… COMPILE statements
      2. SYS.UTL_RECOMP.RECOMP_PARALLEL procedure
      3. SYS.UTL_RECOMP.RECOMP_SERIAL procedure

         Or,

   2. By a user with appropriate privileges on SYS.UTL_RECOMP package (such as the SYS user) using one of the following options only:
      1. SYS.UTL_RECOMP.RECOMP_PARALLEL procedure
      2. SYS.UTL_RECOMP.RECOMP_SERIAL procedure

   Oracle Banking Trade Finance strongly advises against manually compiling Oracle Banking Trade Finance schema objects (using ALTER…COMPILE) as a non- Oracle Banking Trade Finance application user when length semantics of CHAR are being enforced. Such an attempt may change the NLS_LENGTH_SEMANTICS parameter setting of the PL/SQL objects to BYTE and this may result in runtime exceptions within the application, such as:

   “ORA-06502- PL/SQL: numeric or value error: character string buffer too small”.

   Note:

   If the Oracle Banking Trade Finance schema is ever re-compiled using SYS.DBMS_UTILITY.COMPILE_SCHEMA in an environment that is using CHAR semantics, then it must be ensured that for the user executing the procedure, the session-level NLS_LENGTH_SEMANTICS parameter is set to CHAR. This is because while SYS.UTL_RECOMP.x procedures re-use existing PL/SQL object settings, SYS.DBMS_UTILITY.COMPILE_SCHEMA compiles the objects with the NLS settings of the invoking user’s session and may alter the PL/SQL object settings of the objects (including length semantics).