1. Multi-byte Character Support
  2. Introduction
  3. Approach

Approach

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

Please note the approach is suggested for 12c Database version only.
  1. Complete the database installation and set up as per the Oracle Banking Payments Database Best Practices for your Payments release.
  2. Immediately after creating the Payments 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 was PAYMENTS and the trigger was named “PAYMENTS_LOGON”, the trigger would look like:

    CREATE OR REPLACE TRIGGER SYS.PAYMENTS_LOGON AFTER LOGON ON PAYMENTS.SCHEMA BEGIN EXECUTE IMMEDIATE 'alter session set nls_length_semantics=CHAR'; END;/

  3. Proceed with the rest of the Payments installation as per the installation documents.

    In situations where an operation results in Payments objects being invalidated, make sure the objects are re-compiled either:

  4. by the PAYMENTS schema user using one of the following options:
    • Manually using ALTER… COMPILE statements
    • SYS.UTL_RECOMP.RECOMP_PARALLEL procedure
    • SYS.UTL_RECOMP.RECOMP_SERIAL procedure

    Or,

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

    Oracle Banking Payments strongly advises against manually compiling PAYMENTS schema objects (using ALTER…COMPILE) as a non-Payments 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”.

Figure 2-2 STOP


stop

If the PAYMENTS 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).

Parent topic: Introduction