Skip to main content

Search

Welcome to Tibero GTS!

ERROR Code 11032 Occurs When Using the xmlagg Function

Document Type | Troubleshooting 

Category | App Development

Applicable Product Version | 6FS07

Error Code | 11032 

Document Number | TDETS007

 

Issue

An ERROR_EXP_INVALID_PSM_RETURN_TYPE(-11032) error occurs when using the xmlagg function. 

 

Cause

The return type of the xmlagg function is CLOB.
Therefore, access methods such as .extract, or .getStringVal are not allowed, which causes the error.

 

Solutions

This issue can be resolved by installing the internal Tibero package.
You need to install the script $TB_HOME/scripts/pkg/install_xmltype.sql provided by Tibero to enable object type access methods.
 

Procedure

  1. Pre-check: $TB_HOME/scripts/pkg/xmltype_install_checker.sql Run this script to verify whether XMLTYPE is installed, and to check tables with XMLTYPE columns and related PSMs in advance.
  2. Package Installation: $TB_HOME/scripts/pkg/install_xmltype.sql should be executed to install support for XMLTYPE.
  3. Invalid Object Handling: After installation, recompile any Invalid Objects that appear. (It is necessary to have the list and compile scripts prepared in advance from step 1.)
  4. Verify Error Query Operation: Re-run the query that previously caused the error to confirm it now operates correctly.
 

Risks

  1. $TB_HOME/scripts/pkg/install_xmltype.sql contains the statement ALTER SYSTEM FLUSH PPC; .
    - Impact: All parsed queries in the DB instance are cleared, causing all queries to be hard parsed again, which may lead to temporary performance degradation.
    - Conclusion: The statement ALTER SYSTEM FLUSH PPC; cannot be omitted. 
    Since changing XMLTYPE causes previously used SQL functions to be cached in the physical plan and the cached plan to be used after changes, this statement must be executed.
  2. $TB_HOME/scripts/pkg/xmltype_install_checker.sql should be run to check for TABLES with xmltype columns and associated PSM list.
     2.1) If tables with xmltype columns exist, all such tables must be dropped.
     2.2) $TB_HOME/scripts/pkg/xmltype_install_checker.sql should be executed again. 
           (The result should show zero tables.) 
           Then, $TB_HOME/scripts/pkg/install_xmltype.sql should be run.
     2.3) Recreate the tables dropped in step 2.1).
 
Note
 
Backup and Recreation Guide
1.Create a table (A) with an arbitrary clob column.
2.Insert xmltype data into table A using CTAS.
3.Then run install_xmltype.sql.
4.Afterwards, insert data back from table A (with clob column) into the recreated table with xmltype columns. (A clob column is needed for each xmltype column.)
 
     2.4) A list of DEPENDENCY OBJECTS such as PROCEDURE, FUNCTION, VIEW, etc., will be shown, 
           and these will change from VALID to INVALID after running install_xmltype.sql. 
           If invalid, related business procedures may not execute, potentially impacting operations. 
           Therefore, it is necessary to prepare a list and compile scripts in advance for quick recompilation.

 

Error Query

Removing extract('//text()') allows the query to run, but it is recommended to install install_xmltype.sql for proper execution.
SELECT A.ROUTE_ID
    , A.ORD
, A.LINK_ID
, B.XY AS GEO_XY
, NVL( (
        SELECT DISTINCT X.ROUTE_NM
  FROM TEST.TEST_MS_TABLE X
              WHERE X.ROUTE_ID = A.ROUTE_ID
                AND X.LANG_KEY = 'ko')
, 'UNKNOWN') AS ROUTE_NM
 FROM TEST2.TEST_TMP A
    , (SELECT LINKID , XMLAGG(XMLELEMENT(XY, XY || '|')).extract('//text()') AS XY
    FROM (SELECT LINKID , POSX || ',' || POSY AS XY
        FROM TEST3.G_LINKPLINE ORDER BY ORD )
GROUP BY LINKID ) B
WHERE A.LINK_ID = B.LINKID
;

 

 
 

Related to