Smart IT - Ticket Console - Ora Error 552 while trying to access a Japan customer data on Ticket Console

Version 2
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy with Smart IT


    COMPONENT:

    Remedy with Smart IT


    APPLIES TO:

    SmartIT 18.05, SmartIT 18.08 with Oracle database



    PROBLEM:

    Use Case 1:
    Unable to access Japanese locale data on SmartIT ticket console, Error SQL database operation failed with ORA Error 552

    Use Case 2:
    Smart IT Dashboard Updates empty ORA-06502
    The Updates section from Smart IT Dashboard page stays in loading for a while and then is showing no results. 

    The logs shows the following error: 
    org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small 
    ORA-06512: at line 1 


    This usually happens due to a character limitation mismatch between parent tables and views. 
    In this case for Updates section - U__SMARTIT_SOCIAL_WORKLOGS view is accessed. This view unions the following views: 

    U__HPD_WORKLOG
    U__CHG_WORKLOG
    U__AST_WORKLOG
    U__CTM_PEOPLE_WORKLOG
    U__TMS_WORKINFO
    U__AAS_WORKINFO
    U__RMS_WORKLOG
    U__PBM_KNOWN_ERROR_WORKLOG
    U__PBM_INVESTIGATION_WORKLOG
    U__RKM_UPDATEREQUESTS
    U__WOI_WORKINFO
    U__SRM_WORKINFO

    These views are using a LOB substr function to truncate the Notes text retrieved: 

    DBMS_LOB.SUBSTR(C1000000151,2000) AS Notes 

    And here is the problem: 
    The Notes field it's a CLOB in HPD_WORKLOG table. 
    For U__HPD_WORKLOG view,
    2000 characters are substr from Notes (C1000000151) and put into a VARCHAR2(4000) field. 
    Oracle limitation for VARCHAR2 field is 4000 bytes.
    In case those 2000 characters substracted are Chines characters for example - this will count as 6000 bytes.

    How to Verify?

     

      1. Add special characters/symbols in a particular incident WorkInfo tab so that it exceeds the limit of the database.
      
      2. Log in from Smart IT and Observed the Dashboard is blank.
      
      3. Now I go back and reduce the data so that it fits the database length of Notes field.
    4. Notice that the SmartIT shows up the data properly.
      
        
      
      When Smart IT is pulling the data from the back-end tables, it is actually pulling only 2000 characters to show up in the Updates section of Dashboard.
    This will work if the locale is English.

    If there is some other locale in that case, the characters size will differ and when only 2000 characters are used, it will exceed the 4000 limit (as each character will be 2 bytes).
      
      

     


    CAUSE:

    Defect ID#: SW00560166


    SOLUTION:

     

      Check SmartIT Debug logs and server side api/filter/sql logs and you will see below filter in server side logs. In this particular case Resolution notes field had a lot of data which is a CLOB field. Below filter truncates data for CLOB fields and displays it on SmartIT Ticket Console:

    Defect ID#: SW00560166
       Target Version : 19.02 cumulative patches and 19.08.

    Workaround:
      
      1. Modified below Set Field Value for filter "SHR:UCC:UnionFieldLoop_SpecialHandling_SmartIT"
      
      From:
      
      (((("DBMS_LOB.SUBSTR(C" + $z1D_FieldId$) + ",2000)") + " AS ") + $z2TF_UnionFieldName$) + ", "

    To:
      
      (((("DBMS_LOB.SUBSTR(C" + $z1D_FieldId$) + ",1000)") + " AS ") + $z2TF_UnionFieldName$) + ", "

    2. And rebuild the Database Union Structure for all 8 forms on form "SHR:Union_ConfigurationConsole"

     


    Article Number:

    000170989


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles