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 OBTR 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 OBTR Database Best Practices for your OBTR release.
2. Immediately after creating the OBTR 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 OBTR and the trigger was named “FCUSER_LOGON”, the trigger would look like: CREATE OR REPLACE TRIGGER SYS.FCUSER_LOGON AFTER LOGON ON OBTR.SCHEMA BEGIN EXECUTE IMMEDIATE 'alter session set nls_length_semantics=CHAR'; END;
3. Proceed with the rest of the OBTR installation as per the installation documents. In situations where an operation results in OBTR objects being invalidated, make sure the objects are re-compiled either:
   1. By the OBTR schema user using one of the following options:
   2. SYS.UTL_RECOMP.RECOMP_PARALLEL procedure
   3. SYS.UTL_RECOMP.RECOMP_SERIAL procedure
4. 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 OBTR strongly advises against manually compiling OBTR schema objects (using ALTER…COMPILE) as a non-OBTR 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 OBTR 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).