Skip to content

How do you systematically check whether any IFSAPP/IFSINFO database objects have been compiled with NLS_LENGTH_SEMANTICS set to BYTE instead of CHAR (which can cause 'buffer too small' errors), and fix all of them at once rather than one at a time?

Answer

First confirm your current session's setting with SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_LENGTH_SEMANTICS' — if it shows BYTE, fix the session with ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR before compiling anything. To find every already-affected object, query all_plsql_object_settings for owner IN ('IFSINFO','IFSAPP') AND nls_length_semantics = 'BYTE', which both identifies affected objects and lets you build the corresponding ALTER ... COMPILE statement for each (PACKAGE, PACKAGE BODY, or other object type). Wrap that in a PL/SQL loop with EXECUTE IMMEDIATE to recompile every affected object as CHAR in one pass, then refresh the Dictionary and Reference caches afterward. Some third-party SQL client tools default their own database sessions to BYTE semantics, which is a common way objects end up compiled wrong in the first place — check your tool's own connection/session preferences for an equivalent NLS_LENGTH_SEMANTICS setting.

← Browse Developer Studio, deployment, and upgrades questions