Okay, nearly 1 year since my last post…
I’m presenting at the APEX SIG on 3rd November…
I would love for every attendee to bring one thing that will fit into a hat!
It could be a piece of code or comment on a piece of paper, a memory stick with something on, a photograph, a piece of candy, a used bus ticket – I don’t care – use your imagination. Every single one will be discussed within the context of the presentation.
Please retweet/facebook/e-mail/phone/whatever!
Ben/Munky
Venue: Pennyblack’s at The Mailbox – http://www.pennyblacksbar.com
Time: Monday 29th November arrive 8:00pm – 8:30pm
Be there!
Well, it’s been a long time without a new post from me so I thought I’d get the ball rolling again!
Having been surfing about and happened upon Adrian Billington’s site again, I spotted something pretty nifty. With a bit of rewriting, I started to see a possible benefit to the APEX community – mostly for the handling of multiselect list values in reports.
A common approach to dealing with them is to concatenate colons to the multiselect list value returned in the bind variable and then concatenating colons to the column being searching and using INSTR. Whilst this is okay for small sets of data, it can become problematic for large ones because it will negate the index on the searched column.
Consider this example of the that approach…
SELECT *
FROM emp
WHERE INSTR(':'||:LIST_ITEM||':',':'||empno||':')>0
Here, If you have an index on empno, this won’t be used and a full table scan will occur.
Now consider this (and this is seriously quick)…
WITH t_str AS ( SELECT :LIST_ITEM||':' AS str, (LENGTH(:LIST_ITEM) - LENGTH(REPLACE(:LIST_ITEM,':'))) + 1 AS no_of_elements FROM dual ), t_n_rows AS ( SELECT LEVEL AS i FROM dual CONNECT BY LEVEL <= (SELECT SUM(no_of_elements) FROM t_str) ), t_in_list AS ( SELECT SUBSTR(str,start_pos,(next_pos - start_pos)) AS element_value FROM ( SELECT t_str.str, nt.i AS element_no, INSTR(t_str.str,':',DECODE(nt.i,1,0,1), DECODE(nt.i,1,1,nt.i - 1)) + 1 AS start_pos, INSTR(t_str.str,':',1,DECODE(nt.i,1,1,nt.i)) AS next_pos FROM t_str JOIN t_n_rows nt ON nt.i <= t_str.no_of_elements ) ) SELECT * FROM emp WHERE empno IN (SELECT TO_NUMBER(element_value) FROM t_in_list);
Voila! The index on empno will be used! I know there’s more lines of code here but the performance benefits of it are huge (most of it’s done just using fast dual in the factored subquery). All you need to do is save it in your toolbox and copy & paste when the need arises!
Hope you enjoyed this, more posts coming soon.
PS: Sorry – forgot to link to the actual article by Adrian – hit the word ‘nifty’ now!
Okay, the title to this post may seem a tad specific but the principles are transferable to creating pretty much any Ext Tree JSON object under any circumstances as long as you have the data in the database.
Previously I have be using Mark Lancaster’s example for creating the JSON object for an Ext Tree using the APEX List template. This has been working flawlessly for a good while and I love the ability to rejig the tree hierarchy using the list functionality in APEX.
However, I recently had requirement to implement a custom authorisation for the tree – hiding all entries that the user was not allowed to see. This would have been easy normally – I was using the APEX list functionality so I could just apply conditional display. Unfortunately, I couldn’t use this approach because my target was an URL with a target like:
javascript:myPageFunc(1);
This meant that APEX didn’t know which page the entry was targeting and I therefore couldn’t pass it through to the authorisation function to see if the option was available.
So, I set about creating my own JSON object generating SQL query, I had a google about and many people have done this in PL/SQL but it is normally limited a certain number of levels in the tree and these levels are hard-coded.
Creating the initial object with no authorisation was reasonably straight forward, I had Mark’s example for the syntax and I created a small list in APEX using that template so that I had a reference point to work to.
To add the authorisation I added the page that I wanted the list entry to be checked against to the ‘User defined attributes’ in APEX. In the apex_application_list_entries table, this is stored as entry_attribute_01.
My problems started where there where multiple levels in the tree and the user was authorised to an entry at the lowest level but not to some of the levels above that in the same path. This was overcome by getting the whole path for each entry, checking if the user was authorised to the lowest level and then walking back up the path to make sure they then were shown all the folders in order to get to that leaf.
My colleague, Kevan Gelling, suggested that a pipelined function would help with this so we created this simple one:
CREATE OR REPLACE TYPE t_string_to_table
AS
TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE FUNCTION stringToTable
(
p_String VARCHAR2,
p_Delim VARCHAR2 DEFAULT ':',
p_Start VARCHAR2 DEFAULT 'N',
p_End VARCHAR2 DEFAULT 'N'
)
RETURN t_string_to_table PIPELINED
IS
l_Char VARCHAR2(1);
l_Element VARCHAR2(4000);
l_loopStart NUMBER;
BEGIN
l_Element := NULL;
IF p_Start = 'Y' THEN
l_loopStart := 2;
ELSE
l_loopStart := 1;
END IF;
FOR i IN l_loopStart .. LENGTH(p_String)
LOOP
l_Char := SUBSTR(p_String,i,1);
IF l_Char = p_Delim THEN
PIPE ROW (l_Element);
l_Element := NULL;
ELSIF p_End = 'N' AND i = LENGTH(p_String) THEN
l_Element := l_Element||l_Char;
PIPE ROW (l_Element);
ELSE
l_Element := l_Element||l_Char;
END IF;
END LOOP;
RETURN;
END;
/
Now the SQL for the JSON object is as follows (thanks to Kevan for rationalising the last part a bit):
WITH
menuList AS
(
SELECT list_entry_id,
list_entry_parent_id,
entry_text,
-- In this example, the target is an URL (like javascript:loadPage(1);)
entry_target,
-- entry_attribute_01 is used to denote the target page number
entry_attribute_01,
display_sequence,
-- Insert your authorisation function here
-- 'Y' is just hardcoded for demonstration
-- purposes for example -
--
-- my_auth.isPageAuthorised(:p_user_name,
-- :p_app_id,
-- entry_attribute_01)
--
'Y'
isAuthorised
FROM apex_application_list_entries
WHERE application_id = :p_app_id
AND list_name = 'Main Menu'
),
menuHier AS
(
SELECT list_entry_id,
list_entry_parent_id,
entry_text,
entry_target,
entry_attribute_01,
display_sequence,
isAuthorised,
ROWNUM all_row_num,
LEVEL the_level,
CONNECT_BY_ISLEAF isleaf,
SYS_CONNECT_BY_PATH( isAuthorised, ':' ) authPath,
SYS_CONNECT_BY_PATH( list_entry_id, ':' ) idPath,
(
SELECT COUNT(*)
FROM apex_application_list_entries l2
WHERE l2.list_entry_parent_id = menuList.list_entry_id
) child_count
FROM menuList
START WITH list_entry_parent_id IS NULL
CONNECT BY PRIOR list_entry_id = list_entry_parent_id
ORDER SIBLINGS BY display_sequence,
entry_text
),
menuAuth AS
(
SELECT menuHier.*,
COUNT(*) OVER () row_count,
ROW_NUMBER() OVER (ORDER BY all_row_num) auth_row_num,
ROW_NUMBER() OVER (PARTITION BY the_level,
list_entry_parent_id
ORDER BY all_row_num ) item_row_num,
LEAD(the_level, 1, 1) OVER(ORDER BY all_row_num) lead_level
FROM menuHier
WHERE list_entry_id IN
(
SELECT DISTINCT
i.COLUMN_VALUE
FROM menuHier,
TABLE(stringToTable(idPath)) i
-- Last character in path = Y
WHERE SUBSTR(authPath, LENGTH(authPath), 1) = 'Y'
)
ORDER BY all_row_num
)
SELECT
-- START: JSON
CASE
WHEN the_level = 1 THEN
'['
END
-- ITEM: Indent levels
|| RPAD(' ',(the_level - 1) * 2)
-- ITEM: Comma separator
|| CASE
WHEN item_row_num != 1 THEN
','
END
-- ITEM: Item details
|| '{'
|| '"id":"' || list_entry_id || '"'
|| ',"text":"' || entry_text || '"'
|| ',"href":"' || entry_target || '"'
-- ITEM: Leaf information */
|| CASE
WHEN isleaf = 0 THEN -- With children
',"leaf":false'
|| ',"children":['
WHEN isleaf != 0 THEN -- With no children
',"leaf":true'
END
|| -- END: Item
CASE
WHEN isleaf != 0 THEN
'}'
END
-- END: Level
|| CASE
WHEN isleaf != 0 AND
lead_level < the_level THEN
RPAD(' ', 1 + ((the_level - lead_level) * 2), ']}')
END
-- END: JSON
|| CASE
WHEN auth_row_num = row_count THEN
']'
END treeJSON
FROM menuAuth
This can go anywhere, in an application process, an on-load page process etc, etc. The joy of it is, once you have it working, you can still maintain the hierarchy in the APEX list using the GUI but you can affect it by anything you have in data. You could store ExtJS properties etc in your list entries and carry those through to the tree for example.
This hasn’t been fully tested yet but I will update with any bugs/limitations I come across.
I mentioned a while ago that this book was nearing completion but I have just been advised that it has now been published.
You can get the full description and buy a copy here…
Oracle Application Express 3.2 – The Essentials and More
Well done Matthew and Arie!
My colleague listens to podcasts when he codes. I listen to this (just the same song on repeat)…
http://www.youtube.com/watch?v=Ec8Wjz9k1qU
I don’t even like it that much – it just allows me to switch off and get on with the job (nice chorus though!).
So… comment – what do you listen to?
I expect, at least to get us started, comments from – Kevan, Joel, Tobias, Matt, Jari and Mike!
I was deeply saddened to hear of the loss of Scott Spadafore.
He has done so much for the APEX community and helped so many people. I have learnt alot from his many forum postings and I often find myself looking back to them for guidance. My job is working with a product that he has had so much influence in.
My thoughts are with his family and friends.
Thank you Scott for all of your help.
Just a quick note to say that I will be presenting at the next UKOUG APEX SIG at the end of March.
The presentation will demonstrate the creation of an ExtJS based ‘webtop’ in APEX. It won’t be massively technical and will show the process of going from the ExtJS example to something that is generated and controlled by APEX.
The desktop can act as a portal to other APEX applications and also just looks quite cool!
Hope to see you there!
Everyone should have seen this but I’m going to link to it one more time as the man has come back.
One day- we shall all use Frameworkia…
This genuinely cheers me up when having ‘one of those days’!
Okay, I haven’t posted in a very long while…
So, apologies for the delay, but such is the result of falling off a cliff!
I have mentioned a few times that I was working on some ExtJSy stuff and some people have asked me when it will be ready. Quick answer is not very soon but I would welcome you to have a look at where I’m at so far.
I took great inspiration from Mark Lancaster (I do hope you’ve seen his site), and Matt Nolan at e-DBA. The route I took was to use a PL/SQL package to dynamically (probably strecthing the use of the word there…) create the ExtJS scripts based on the meta data from the APEX tables and a bit of DBMS_SQL, a few application processes and a couple of standalone JS files in the header of the page template. It’s all pretty clean and generic.
On the home page there’s a little portal kind of thing where the positions of things are based on the region positions, columns etc. If you find the page with some grids on it – it uses the alignment, default sort settings etc. from the APEX data as well. So you just enter the report query and settings as normal in APEX and the PL/SQL package picks all of that up to create the grid.
It’s just a starting point at the moment and something I hope to work on when in finer form.
So sorry for the wait and sorry it’s only just started but here’s the link…
Please e-mail me with suggestions for what to do next…
