NLS_LENGTH_SEMANTICS - CHAR or BYTE?

Hi @SIMONJB,

If you are using tools such as Toad or Oracle SQL Developer or PLSQL Developer, some of those tools have the NLS_LENGTH_SEMANTICS parameter set to BYTE so that all the database sessions opened from that tool would have this parameter set to BYTE as well. Therefore it is better to check whether the parameter is set to CHAR for the current session when you are recompiling database objects that belong to IFSAPP or IFSINFO.

• To verify whether the parameter is set to CHAR for the current session, you can run below query:

select * from nls_session_parameters where parameter = 'NLS_LENGTH_SEMANTICS';

• If above query output is BYTE, you can run following SQL statement to change it to CHAR:

alter session set NLS_LENGTH_SEMANTICS=char;

• To find out whether there are any IFSAPP and IFSINFO objects that have been compiled as BYTE, you can use following SQL query. 

SELECT name,
           owner,
           'ALTER ' ||
           CASE type
             WHEN 'PACKAGE' then
              'PACKAGE ' || owner || '.' || name || ' COMPILE'
             WHEN 'PACKAGE BODY' then
              'PACKAGE ' || owner || '.' || name || ' COMPILE BODY'
             ELSE
              type || ' ' || owner || '.' || name || ' COMPILE'
           END sql_stmt
      FROM all_plsql_object_settings
     WHERE owner in ('IFSINFO', 'IFSAPP')
       AND nls_length_semantics = 'BYTE'
     ORDER BY type;

• Any of the objects that are output by the above query needs to be recompiled into CHAR. For that, you can run below PLSQL block. This will take several minutes depending on the number of objects that need to be compiled:

DECLARE

  sql_  varchar2(200);
  name  varchar2(200);
  owner varchar2(200);
  cursor stmt_ is
    SELECT name,
           owner,
           'ALTER ' ||
           CASE type
             WHEN 'PACKAGE' then
              'PACKAGE ' || owner || '.' || name || ' COMPILE'
             WHEN 'PACKAGE BODY' then
              'PACKAGE ' || owner || '.' || name || ' COMPILE BODY'
             ELSE
              type || ' ' || owner || '.' || name || ' COMPILE'
           END sql_stmt
      FROM all_plsql_object_settings
     WHERE owner in ('IFSINFO', 'IFSAPP')
       AND nls_length_semantics = 'BYTE'
     ORDER BY type;

BEGIN

  OPEN stmt_;
  LOOP
    FETCH stmt_
      into name, owner, sql_;
    EXIT WHEN stmt_%NOTFOUND;
    EXECUTE IMMEDIATE (sql_);
  END LOOP;
  CLOSE stmt_;

END;

• Once recompiled, refresh Dictionary and Reference caches as well.

In PLSQL Developer, I could not find a setting similar to SQL Developer but I am guessing it is this one under Tools → preferences → Oracle → Options:

Tagging @Chandana Gunasekara for some expert opinion on above Unicode option. :) 

Anyway, the best way to avoid this would be verifying nls_session_parameters for the current session before compiling any database objects by following the step I have mentioned at the top. 

Hope this helps!