Flavio Casetta

The "link attributes" of an interactive report allows a developer to specify additional attributes for a column displayed as a link in a interactive report.

A tooltip that will be displayed when a user hovers over the link text with the pointer can be specified using the attribute TITLE, for instance: TITLE="open page xyz".

This column attribute however is not picked up by the translation process of APEX, so it's not something that can be be found in the translation repository.

An easy way to work around the problem if you need to translate the text is as follows:

1. add a new column to the IR report containing:

   APEX_LANG.MESSAGE('MSGOPENXYZ') as MSG1

2. Make the column hidden.
3. Update the LINK ATTRIBUTES column attribute adding TITLE="#MSG1#"
4. Add the message MSGOPENXYZ to the message repository for the main language and for the additional languages.
5. Repeat the steps 1-4 if you have more links needing this.
   
6. Seed you application
7. Publish the application in the additional languages
8. Enjoy the translated tooltip.

I was puzzled when I got an error message allegedly occurring at a line containing an "end loop" statement and it took me a while to figure out that this occurs when either bound of the loop is NULL.

In my case both the initial and final bounds are variables and they were supposed to be not null or so I thought...

Here is a code snippet reproducing the error:

begin
  for i in null..100
  loop
    null;
  end loop;
end;
Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

There are at least two ways of configuring facets in a faceted report in a hierarchical fashion, using different attributes for the child facet, one is straightforward, the other one more convoluted, let's find out in this video I prepared for the #JoelKallmanDay to remember a mentor and friend.

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.