Flavio Casetta

If you landed here is because you hit the problem of dealing with LONG columns.

There are some good articles elsewhere about how to tackle this old problem, my contribution in this case consists in advising about some limitations that apparently have been overlooked when using the same technique explained below.

It's a mystery to me why after so many years we can't rid of this annoyance once for good, why not "simply" adding a CLOB column equivalent at least in the case of data dictionary columns?
Come on!

I needed to extract the content of the TEXT column from DBA_VIEWS and DBA_MVIEWS, possibly without having to pass through an INSERT into a table (using function TO_LOB), which is the best workaround in case you deal with static data, for one-off operations.

I stumbled upon an old video of Connor McDonald showing how to extract the content of a LONG column exploiting the XML API DBMS_XMLGEN.GETXMLTYPE. This trick seemed to save the day after some adaptation for my case, and actually I was almost ready to celebrate when I started hitting some errors while doing further tests.

To cut a long story short, eventually I encountered the following problems:

1. API documentation for version 19c of DBMS_XMLGEN.SETCONVERTSPECIALCHARS is incorrect as it mentions a parameter "conv" but the real parameter name is "replace". This typo is still present in the latest version of the documentation of 23ai.
   
   
2. DBMS_XMLGEN.GETXMLTYPE and DBMS_XMLGEN.GETXML won't perform special characters escaping via DBMS_XMLGEN.SETCONVERTSPECIALCHARS if the column type is LONG.
   I was getting parsing errors when using Connor's EXTRACTVALUE technique because the XML document contained < or > as spare characters in the source (as in WHERE conditions inside the query source).
   
   
3.  DBMS_XMLGEN.GETXMLTYPE and DBMS_XMLGEN.GETXML will truncate the content to the first 32K for LONG columns.
   

Problem #1 was easily solved, problem #2 was solved extracting the data using REGEXP_SUBSTR instead of EXTRACTVALUE, but this was possible because I was working on a XML document containing a single ROW tag at a time. For multiple rows this solution will not work.

  FUNCTION long2clob
     ( p_qry in clob,  -- must return a single row!
       p_col in varchar2)
  RETURN CLOB
  IS
    c        CLOB;
  BEGIN
    c := regexp_substr(
           dbms_xmlgen.getxml(p_qry),
           '(<ROW>.*<' || p_col || '>(.*)</' || p_col || '>.*</ROW>)',
           1,
           1,
           'cn'
           ,2 
         );
    return c;

  END long2clob;

Problem #3 remains, unless LONG columns are less than 32K.
Unfortunately we do have some views exceeding 32K of source, but considering the usage of this function I'll probably live with this limitation for the moment.
By the way, SQLDeveloper won't allow you to edit a view larger than 32K, and to me this sounds like an invitation to avoid such situations.

Finally, I also tried to see what happens when you supply a LONG column to function JSON_OBJECT, unfortunately it returns the exception:

ORA-40654: Input to JSON generation function has unsupported data type.

That's all folks!
(quote)

As you probably know, the lines shown in views DBA_ERRORS/ALL_ERRORS/USER_ERRORS in the case of triggers are wrong.

But they are not totally wrong, they are just shifted by the amount of lines between the line containing the keyword TRIGGER and either DECLARE or BEGIN, whichever occurs first.

See the example below for an error reported by the dba_errors view on line 2 caused by missing grants on the table used in the variable declaration:

TRIGGER GAGREA2.TR_ASSEGNAZIONE_AI
   AFTER INSERT ON ASSEGNAZIONE
   REFERENCING OLD AS OLD NEW AS NEW
   FOR EACH ROW
DECLARE
   LI_ID_ASSEGNAZIONE_DOM_RICGEN             GAGREA2.DOMANDA_ASG_SPECIFICHE.ID_ASSEGNAZIONE_DOM_RICGEN%TYPE;
BEGIN

   GAGREA2.PKG_TRIGGER.SET_ID_ASSEGNAZIONE_DOM_RICGEN ( :NEW.ID_DOMANDA, LI_ID_ASSEGNAZIONE_DOM_RICGEN );

END TR_ASSEGNAZIONE_AI;

So, the real line number can be obtained adding the number of the line containing DECLARE (or BEGIN if DECLARE is missing) minus 1, that is 2 + 5 - 1 = 6.