Skip to main content

All,

Looking for some guidance.  IFS Apps 8 SP1 installation.

We had a major incident yesterday when users lost the ability to carry out the Shop Order Receipt process - users were receiving an ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

The issue began at 11:11.  We finally resolved the matter some time later after finding that a number of packages, but critically INVENTORY_TRANSACTION_HIST_API, had the NLS_LENGTH_SEMANTICS changed from CHAR to BYTES at exactly the time the issue began.

We reviewed all other instances of IFS (Dev, Test and another copy of Live) and the parameter is set to CHAR.  We changed INVENTORY_TRANSACTION_HIST_API from BYTE to CHAR and the issue was resolved.

In our system parameters we have the following setting which as I understand it means we are operating a Unicode system

 

We have reviewed all packages across Dev, Test and Live and there are number of inconsistencies between CHAR and BYTE.

Questions are:

Should all packages be set to CHAR in a unicode system or is there a reason why some would be BYTE and other CHAR.  This post seems to suggest that all should be CHAR - https://docs.ifs.com/techdocs/Foundation1/020_installation/020_installing_fresh_system/010_planning_installation/010_storage_tier_considerations/030_unicode/default.htm

My team use an old version of Allround Automations PL/SQL Developer.  I have a suspicion that this tool is causing the issue though it is unclear at the moment how these changes occurred yesterday.  (and in the past).

Does anyone have experience in this tool and how we check the default parameter that is set NLS_LENGTH_SEMANTICS  (as the screen shot in the article below is for SQL developer which the team do not use).

Best regards - Simon Brooker

 

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!

@Charith Epitawatta 

As you mentioned unicode option enabled feature allows you  to fetch the unicode data. this will make sure whether the data is unicode in PLSQL developer tool.

 

 

Terms & ConditionsCookie settings