DBA_ERRORS and error lines reported for TRIGGERS

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.

Setting the CARDINALITY (undocumented) hint on two tables

I was trying to solve a performance problem in a query containing a left join on two global temporary tables. 

Clearly if you run an Explain Plan on such tables, the cardinality is totally wrong, but theoretically you should be able to "simulate" the right cardinality with the undocumented hint CARDINALITY.

The second problem is that apparently such hint accepts only one table or I couldn't find any example of usage when you need to set the value for two tables.

So I came up with the following workaround using the WITH clause.
If the resulting plan is correct, then it's easily understood why a query with a cost of 7513724 takes ages to return the results.

WITH 
zoo_01 as (
  select /*+ CARDINALITY(gtt_rpt_zoo_01 38974) */ * from reports.gtt_rpt_zoo_01),
 zoo_03 as (
  select /*+ CARDINALITY(gtt_rpt_zoo_03 38967) */ * from reports.gtt_rpt_zoo_03)
SELECT ...
  FROM zoo_01, zoo_03
 WHERE ...

If there is an alternate method, I'd be glad to know...

Order of appearance of functions and subqueries in a WITH ... SELECT

I don't know if it is the same for you but I tend to forget certain details, that's why often I need to write down some examples in this place to save time for the future.

For instance one thing that I tend to forget is the order in which you can specify functions and subqueries inside the same WITH. 

Functions must come first, then subqueries, if any.

Here is a working example, two functions, two subqueries and the final SELECT.

with 
   -- first function
function msg ( msgnum in integer) return varchar2
as
 s varchar2(200);
 i pls_integer;
begin
 i := utl_lms.get_message(msgnum, 'rdbms', 'ora', 'italian', s);
 return 'ORA-'||to_char(msgnum,'TM9')||': '||s;
end;
  -- second function
function msg2 ( msgnum in integer) return varchar2
as
 s varchar2(200);
 i pls_integer;
begin
 i := utl_lms.get_message(msgnum, 'rdbms', 'ora', 'italian', s);
 return 'ORA-'||to_char(msgnum,'TM9')||': '||s;
end;
 -- some subqueries
 x as (select 13367 as a from dual ),
 y as (select 13368 as a from dual)

-- final query
select msg(a)
  from x
union all
select msg2(a)
 from y;

According to a performance comparison made by Tim Hall, inline functions seems to be faster than equivalent ordinary functions unless PRAGMA UDF has been specified in the latter, in which case ordinary functions seem to outperform the inline ones.

Adding PRAGMA UDF to inline functions doesn't seem to make any difference.