Friday, March 15, 2024

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.

Tuesday, March 05, 2024

When #OWNER# is not the OWNER you expected

Oracle provides developers with a ton of dictionary views, many of which containing a column called OWNER, basically most of the DBA* and ALL* views contain such column, with a few exceptions, for instance compare view ALL_INDEXES with ALL_IND_COLUMNS and try to figure out why the latter comes with a column called INDEX_OWNER while the former comes with a simple OWNER.

If you are developing an APEX report based on some of these views and for some reason the report comes with a link to another page where you pass the value of the OWNER column as a parameter using the #OWNER# substitution string, as APEX itself suggests if you click on the helper icon in the link builder, you won't get the OWNER you expect, because #OWNER# is a undocumented APEX substitution string that gets replaced with the name of the primary schema attached to the workspace.

I created a simple application on APEX.ORACLE.COM to show this behavior (open door credentials).

I believe that someone should update the section dedicated to the Substitution strings in the APEX Builder manual and add #OWNER# to the list and I am almost 100% sure that #OWNER# is a legitimate substitution string that gets replaced everywhere, not just accidentally in report links.

So, at the end of the day, if you need to pass the owner of something inside a link, you must rename the column, and I'd recommend avoiding the word OWNER altogether, use SCHEMA instead.


 

Thursday, February 29, 2024

APEX Master Detail side-by-side form ICON_CLASS and ICON_COLOR_CLASS explained

In case you are wondering what to put in the ICON_CLASS and ICON_COLOR_CLASS columns of the query underlying the left side column of a Master Detail side-by-side form, here is an example.

select "FILE_ID",
    null LINK_CLASS,
    apex_page.get_url(p_items => 'P210_FILE_ID', p_values => "FILE_ID") LINK,
    case 
      when x.ACTIVE = 'Y' then 'fa fa-badge-check' 
      else 'fa fa-low-vision' 
    end AS ICON_CLASS,
    null LINK_ATTR,
    case 
      when x.ACTIVE = 'Y' then 'u-color-4' 
      else 'u-color-15' 
    end AS ICON_COLOR_CLASS,
    case when coalesce(:P210_FILE_ID,'0') = x."FILE_ID"
      then 'is-active' 
      else ' '
    end LIST_CLASS,
    (substr("FILE_NAME", 1, 50)||( case when length("FILE_NAME") > 50 then '...' else '' end )) LIST_TITLE,
    (substr("REPORT_NAME", 1, 50)||( case when length("REPORT_NAME") > 50 then '...' else '' end )) LIST_TEXT,
    null LIST_BADGE
from "V_BATCH_REPORT_FILES" x
where (:P210_SEARCH is null
        or upper(x."FILE_NAME") like '%'||upper(:P210_SEARCH)||'%'
        or upper(x."REPORT_NAME") like '%'||upper(:P210_SEARCH)||'%'
    )
  and REPORT_ID = :P200_REPORT_ID
order by "REPORT_ID","FILE_SEQUENCE"

ICON_CLASS refers to the icons of the font awesome set therefore you must provide both the generic fa class and the specific icon class to get the desired icon to show up.

ICON_COLOR_CLASS refers to the set of colors defined by the classes u-color-dd whose actual value depends on selected theme.

The result for the report template Media Type (Show Icons and Show Descriptions in the template options) will be as follows:

 



yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles