I have a view (DB_ADMIN.VW_DBA_MONITOR_CURRENTLYEXEC) to be able to visualize the transactions in Oracle. This view makes use of those already existing in the system: v$sql, V$SQLSTATS, v$sqlarea and gv$process.

And the structure and type of columns was as follows:

I have a procedure that attempts to use such a view, format the output to a single HTML string under certain conditions, and send it via email. Here is a simple summary of that procedure:

DECLARE
    V_BODY_TEXT CLOB;
BEGIN
    select utl_i18n.unescape_reference(xmlagg(xmlelement(e,'
        <tr>
            <td>' || ltrim(rtrim(TO_CLOB(SID))) || '</th>  
            <td>' || ltrim(rtrim(TO_CLOB(SQL_ID))) || '</th>  
            <td>' || ltrim(rtrim(TO_CLOB(USERNAME))) || '</th>  
            <td>' || ltrim(rtrim(TO_CLOB(ONAME))) || '</th> 
            <td>' || ltrim(rtrim(TO_CLOB(MACHINE))) || '</th>  
            <td>' || ltrim(rtrim(TO_CLOB(WAIT_CLASS))) || '</th>  
            <td>' || ltrim(rtrim(TO_CLOB(EVENT))) || '</th>  
            <td>' || ltrim(rtrim(TO_CLOB(MODULE))) || '</th>  
            <td>' || ltrim(rtrim(TO_CLOB(LOGON_TIME))) || '</th>  
            <td>' || ltrim(rtrim(TO_CLOB(START_TIME))) || '</th>  
            <td>' || ltrim(rtrim(TO_CLOB(TIME))) || '</th>  
            <td>' || ltrim(rtrim(TO_CLOB(SQL_TEXT))) || '</th>
        </tr>
    ','').extract('//text()')).getclobval()) x
    INTO V_BODY_TEXT
    FROM DB_ADMIN.VW_DBA_MONITOR_CURRENTLYEXEC WHERE "TIME" > 300;
               
END;

The error presented by the execution of the above code is because the concatenated result only admits 4000 characters and it doesn't matter if I try to cast (TO_CLOB) field by field or the entire concatenated string, I still get the same error every time. result size is exceeded:

About the above code, the use of the TO_CLOB function, is my last attempt to try to solve the problem.

Said error does not allow any "substring" or "len" type action to be applied to it either.

I've also tried creating a table with all the fields of type CLOB, then inserting the result of the view, and using the above procedure now with the table, but I keep getting the same error every time the generated html code exceeds 4000 characters.

Therefore, I go to this community to see what other options they give me to get out of the problem.

CodePudding user response：

The xmlelement function only accepts up to 4000 characters if the initialization parameter MAX_STRING_SIZE = STANDARD, and 32767 characters if MAX_STRING_SIZE = EXTENDED, so there's no point in converting to CLOB that early in your process. See here.

Also, the utl_i18n.unescape_reference function only accepts VARCHAR2 input, not CLOB. See here. This is the most likely place your query is failing.

Try this, working exclusively with VARCHAR2, doing explicit conversions from NUMBER and DATE types, and saving the aggregation into the largest string to the very end:

DECLARE
    V_BODY_TEXT CLOB;
BEGIN
    select xmlagg(utl_i18n.unescape_reference(xmlelement(e,'
        <tr>
            <td>' || TO_CHAR(SID) || '</th>  
            <td>' || ltrim(rtrim(SQL_ID)) || '</th>  
            <td>' || ltrim(rtrim(USERNAME)) || '</th>  
            <td>' || ltrim(rtrim(ONAME)) || '</th> 
            <td>' || ltrim(rtrim(MACHINE)) || '</th>  
            <td>' || ltrim(rtrim(WAIT_CLASS)) || '</th>  
            <td>' || ltrim(rtrim(EVENT)) || '</th>  
            <td>' || ltrim(rtrim(MODULE)) || '</th>  
            <td>' || TO_CHAR(LOGON_TIME,'DD-MON-YYYY') || '</th>  
            <td>' || TO_CHAR(START_TIME,'DD-MON-YYYY') || '</th>  
            <td>' || TO_CHAR(TIME) || '</th>  
            <td>' || ltrim(rtrim(TO_CLOB(SQL_TEXT))) || '</th>
        </tr>
    ',''))) x
    INTO V_BODY_TEXT
    FROM DB_ADMIN.VW_DBA_MONITOR_CURRENTLYEXEC WHERE "TIME" > 300;
           
END;

CodePudding user response：

XMLType methods are deprecated since Oracle 11gr2, use modern (though, it's hard to tell about XML) SQL functions to access XML data without additional serialization/deserialization issues:

• XMLQUERY for extraction.
• XMLCAST for deserialization. It does decoding of HTML/XML stuff like & etc to their text values.

Below is an example of three characters padded with zeroes up to 5000 bytes (so resulting in too large value for varchar2)

with t(col) as (
  select
    xmlelement(e,
      to_clob(rpad('<>&', 4000, '0'))
      || to_clob(rpad('0', 1000, '0'))
    )
  from dual
)
, deser as (
  select
    xmlcast(xmlquery(
      '//text()'
      passing col
      returning content
    ) as clob) as res
  from t
)
select
  length(res),
  trim(trailing '0' from res) as res
from deser

fiddle