Unpivoting Oracle APEX meta-data

There are APEX dictionary views for most of the data that represents the 'source' that is your APEX application meta-data.

Note: This post has beeen updated to reflect me not looking very hard, but I added a performance comparison... just because.

Today I found one place where I really wanted to run a query to find references to potential data - application substitution strings.

APEX Application Substitution Strings

And yes, I have a CSS rule to right-align those names, to make it easier to read.

I couldn't find any references in the APEX dictionary (correction, see below), so I looked in the Oracle data dictionary to find where it may live.

select * from all_tab_columns 
where column_name like 'SUB%03';

So not only are these stored in a place inaccessible to us mere-mortal developers, they are also stored in 20 sets of name/value columns - not rows.

This means if you have a dozen applications, with references in slightly different locations for each application, then string searches might be a pain.

Unpivot to the rescue!

select * from (
  select id app_id, alias, name -- key facts
      -- all the substitution stringz!
      ,substitution_string_01, substitution_value_01
      ,substitution_string_02, substitution_value_02
      ,substitution_string_03, substitution_value_03
      ,substitution_string_04, substitution_value_04
      ,substitution_string_05, substitution_value_05
      ,substitution_string_06, substitution_value_06
      ,substitution_string_07, substitution_value_07
      ,substitution_string_08, substitution_value_08
      ,substitution_string_09, substitution_value_09
      ,substitution_string_10, substitution_value_10
      ,substitution_string_11, substitution_value_11
      ,substitution_string_12, substitution_value_12
      ,substitution_string_13, substitution_value_13
      ,substitution_string_14, substitution_value_14
      ,substitution_string_15, substitution_value_15
      ,substitution_string_16, substitution_value_16
      ,substitution_string_17, substitution_value_17
      ,substitution_string_18, substitution_value_18
      ,substitution_string_19, substitution_value_19
      ,substitution_string_20, substitution_value_20
  from apex_190200.WWV_FLOWS -- direct from underlying view
)
unpivot (
(str, val) -- new columns
for rec in -- denoted by 
  ((substitution_string_01, substitution_value_01) as '01'
  ,(substitution_string_02, substitution_value_02) as '02'
  ,(substitution_string_03, substitution_value_03) as '03'
  ,(substitution_string_04, substitution_value_04) as '04'
  ,(substitution_string_05, substitution_value_05) as '05'
  ,(substitution_string_06, substitution_value_06) as '06'
  ,(substitution_string_07, substitution_value_07) as '07'
  ,(substitution_string_08, substitution_value_08) as '08'
  ,(substitution_string_09, substitution_value_09) as '09'
  ,(substitution_string_10, substitution_value_10) as '10'
  ,(substitution_string_11, substitution_value_11) as '11'
  ,(substitution_string_12, substitution_value_12) as '12'
  ,(substitution_string_13, substitution_value_13) as '13'
  ,(substitution_string_14, substitution_value_14) as '14'
  ,(substitution_string_15, substitution_value_15) as '15'
  ,(substitution_string_16, substitution_value_16) as '16'
  ,(substitution_string_17, substitution_value_17) as '17'
  ,(substitution_string_18, substitution_value_18) as '18'
  ,(substitution_string_19, substitution_value_19) as '19'
  ,(substitution_string_20, substitution_value_20) as '20'
  )
)
order by app_id, str 

This query transposes all the string/value columns into rows, each denonimated by the new "REC" column.

Note, this query can only be executed by those with the APEX Administrator Role, and if you want to have it within a view that could be executed by other schemas, then select access on wwv_flows is needed with grant option.

Columns unpivoted into rows

By placing the query in a view, I can now make queries like this to find any substitution strings in any applications that have date references.

select * from apx_app_sub_strings
where val like 'APP_DATE%';

Pretty neat, well at least as far as what I was trying to do.

This query may break in future versions, as it's based on an underlying, undocumented view.
I also think that once upon a time, there were fewer pairs.

Update: As the community quickly pointed out, I missed & forgot about a view already dedicated for such a task. I was too busy looking for a column number, when really I should have used APEX_APPLICATION_SUBSTITUTIONS.

I thought I'd see how 'they' solved the problem, and I was a little surprised to see a bunch of UNION statements.

SELECT ...
           f.substitution_string_18,
           f.substitution_value_18,
           f.substitution_string_19,
           f.substitution_value_19,
           f.substitution_string_20,
           f.substitution_value_20
      from wwv_flow_authorized auth,
           wwv_flows f
     where f.id = auth.application_id )
select workspace,
       workspace_display_name,
       application_id,
       application_name,
       substitution_string_01 as substitution_string,
       substitution_value_01  as substitution_value
  from substitution
where substitution_string_01 is not null
union all
select workspace,
       workspace_display_name,
       application_id,
       application_name,
       substitution_string_02 as substitution_string,
       substitution_value_02  as substitution_value
  from substitution
where substitution_string_02 is not null
union all...

After seeing this I couldn't help but run a brute for comparison, running both solutions x times.

iterations:5000
16.55 secs (.003310 secs per iteration) -- union all
 6.54 secs (.001308 secs per iteration) -- unpivot

I made sure the underlying join was the same, and I'm not all that surprised the unpivot did the job quicker.

Update 2: It appears 20.1 has gone with unpivot.

Finding stuff in ALL_VIEWS using XML

Quite some time ago when I first started blogging I wrote a post regarding limitations on searching the ALL_VIEWS dictionary view.

Simply put, if you would like to run a query such as this to find those views that contain the text "booking_no", you're not going to get very far.

SQL> select count(*)
2  from user_views
3  where text like '%booking_no%';
where text like '%booking_no%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

I can't remember where I found this, but there is a way to do perform this search without pumping your data into a temporary table.

select count(*)
from user_views 
where upper(dbms_xmlgen.getxml('select text from user_views where view_name = '''||view_name||'''')) 
  like upper('%booking_no%')

It's essentially looking for "booking_no" within the result of a query that has been converted into canonical XML format.

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <TEXT>SELECT r.code,b.booking_no,e.start_date,e.end_date,o.org_id,o.name
FROM   resources r,bookings b,events e,organisations o
WHERE  r.code = b.resource_code
AND    b.event_no = e.event_no
AND    o.org_id = e.org_id</TEXT>
 </ROW>
</ROWSET>

A word of warning, it is CPU (and probably memory) intensive, so you might like to limit to a particular set of schemas instead of just searching ALL_VIEWS.

Turns out XML has some nifty uses after all ;-)

Updating key-preserved inline views

I learnt something today - there is a scenario where you need update privileges on a table you aren't updating.

Here is my example - I have a table TRAIN_APEX.RESOURCES that I would like to update based on TRAIN.RESOURCE_RATES. So the key factor here is my source table is in a different schema to my destination table - and I only have select privileges on resource_rates.

TRAIN_APEX> SELECT privilege, grantee, table_name FROM all_tab_privs where table_name = 'RESOURCE_RATES';

PRIVILEGE            GRANTEE              TABLE_NAME
-------------------- -------------------- --------------------------------------------------------
SELECT               TRAIN_APEX           RESOURCE_RATES

1 row selected.

This means when I attempt to run an update that uses an elegant key-preserved in-line view, I can't do it!

TRAIN_APEX> UPDATE
  2    (SELECT r.type_code
  3           ,a.standard_rate
  4           ,r.code
  5           ,r.daily_rate
  6     FROM train_apex.resources r
  7         ,train.resource_rates a
  8     WHERE a.type_code = r.type_code)
  9  SET daily_rate = standard_rate;
       ,train.resource_rates a
              *
ERROR at line 7:
ORA-01031: insufficient privileges

I've had a look through the documentation for views and update statements, and I can't see it as a pre-requisite.

Out of curiosity I defined the statement as an actual view to confirm the base table is key-preserved. I knew this already because I've successfully run this statement when the tables are in the same schema, or in scenarios where my account has UPDATE ANY TABLE.

TRAIN_APEX> CREATE VIEW resources_vw AS
  2  SELECT r.type_code
  3        ,a.standard_rate
  4        ,r.code
  5        ,r.daily_rate
  6  FROM train_apex.resources r
  7      ,train.resource_rates a
  8  WHERE a.type_code = r.type_code;

View created.

Elapsed: 00:00:00.05
SQL>
SQL> @view_dml resources_vw

COLUMN_NAME          INSERTABL UPDATABLE DELETABLE
-------------------- --------- --------- ---------
TYPE_CODE            YES       YES       YES
STANDARD_RATE        NO        NO        NO
CODE                 YES       YES       YES
DAILY_RATE           YES       YES       YES

4 rows selected.

So instead, I have to run a "normal" update with a condition to check for presence of rows in my source table, which also means my update isn't as efficient.

UPDATE train_apex.resources r
SET daily_rate =
  (SELECT standard_rate
   FROM train.resource_rates a
   WHERE a.type_code = r.type_code)
WHERE EXISTS
  (SELECT NULL
   FROM train.resource_rates a
   WHERE a.type_code = r.type_code)

Do you think this is a bug or expected behaviour?

Scott

A LONG view ahead

Every time a new version is release I feel compelled to have a little peak at the definition of ALL_VIEWS, to see if columns such as ALL_VIEWS.TEXT have been converted from the LONG datatype. Alas, 11gR2 still has this utilises the LONG datatype, perhaps this is just something that has to be persistent through the ages.

However we can still get around the issue. The reason I find this of interest is sometimes during an impact analysis of say - dropping a column from a table, we need to determine what objects refer to this column. We could start with ALL_DEPENDENCIES, but this isn't granular enough for some analyses.

Due to the many restrictions with the LONG datatype, we can't use the INSTR function to search this dictionary view. Here's one work-around:

> DROP TABLE my_views;

> CREATE TABLE my_views(text CLOB);

> INSERT INTO my_views (text)
SELECT TO_LOB(text) 
FROM all_views;

SAGE@sw10g> SELECT *
  2  FROM   my_views
  3  WHERE INSTR(lower(text),'hiredate') > 0;

TEXT
------------------------------------------------------------------------
select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"

1 row selected.

Got any others?

I do! 
Here is my posting from April 2012 on using XML to solve the problem.