Browsing "Older Posts"

Browsing Category "TREE"

Working with the APEX tree

Von Tobias Arnhold → 6.27.2018
Out of a coincidence I haven't used the APEX tree region for years. Now I got the task to create a customizable tree in my application. Since APEX 5 there is a new tree type called "APEX tree" which supports some really cool functions.

Anyway I had to look around to find out what the APEX tree is actually capable of. First of all start with the APEX "Sample Trees" application which you find in the packaged application area.



Morten Braten took that example and described the features really well:
https://ora-00001.blogspot.com/2016/01/working-with-the-apex-5-treeview.html

John Snyders from the APEX team also created 2 blogposts about the "APEX tree":
APEX 5.0 Converting to the new APEX Tree
Add Checkbox Selection to APEX Tree Region

And as he mentioned there is a Javascript library behind it "libraries/apex/widget.treeView.js." which is documented since APEX 18.1 or at least I think it is. Anyway here is a link which I also have to further investigate:
JS Doc: Widget: treeView

German users can read this document provided by MT AG:
http://files.xmasman.de/20160216_MeetupNRW_TillAlbert_WiePflanzeIchEinenBaumApex.pdf
Veröffentlicht von Tobias Arnhold Am 12:19
Kategorien: , ,

Oracle CONNECT BY Anzeige der maximalen Verkettung

Von Tobias Arnhold → 12.26.2014
Der Umgang mit CONNECT BY ist für mich immer wieder ein Highlight. Ob Positiv oder Negativ lasse ich mal außen vor. :)
Unbeachtet meiner Meinung ist es die sinnvollste Lösung um Baum-Verkettungen zu generieren.

Eine Anforderung die ich zuletzt gleich zweimal lösen musste war die Darstellung der finalen Ketten.

Beispiel Quell-Daten:
1:
1:2
1:2:3
1:2:4
1:6
1:6:1

Beispiel Ziel-Daten:
1:2:3
1:2:4
1:6:1

Das Ganze war recht einfach mit Hilfe einer analytischen Funktion zu lösen:
-- Tabele Definition
  CREATE TABLE "T_ROUTE_EXAMPLE" 
   ( 
    "ID" NUMBER, 
 "P_ID" NUMBER, 
 "NAME" VARCHAR2(50), 
 "TYPE" VARCHAR2(20),
 PRIMARY KEY ("ID")
   ) ;

-- Data
REM INSERTING into T_ROUTE_EXAMPLE
SET DEFINE OFF;
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('1',null,'Haus S','BUILDING');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('3','1','Switch_S_1','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('4','3','1/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('5','3','2/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('6','3','3/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('7','1','Switch_S_2','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('8','7','1/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('9','7','2/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('10','4','DHCP Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('11','5','orclapex DB Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('12','6','ORDS Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('13','8','WEB Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('14','9','AD Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('15',null,'Haus G','BUILDING');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('16','15','Switch_G_1','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('17','16','1/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('18','16','2/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('19','16','3/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('20','17','Win Client 1','CLIENT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('21','18','Win Client 2','CLIENT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('22','19','Mac Client 1','CLIENT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('23',null,'Haus D','BUILDING');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('24','23','Switch_D_1','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('25','24','1/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('26','24','2/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('27','23','Switch_D_2','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('28','27','1/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('29','27','2/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('30','23','Switch_D_3','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('31','30','1/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('32','30','2/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('33','28','Mac Client 2','CLIENT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('34','29','Mac Client 3','CLIENT');
Hier das Select um das entsprechende Ergebnis zu generieren: 
SELECT         
      PATH_LENGTH, 
      ID,
      P_ID,
      NAME,
      TYPE,
      ID_LIST,
      NAME_LIST,
      TYPE_LIST
FROM 
  (
  SELECT
        case 
           when instr(':'||LEAD(ID_LIST) OVER (ORDER BY ID_LIST)||':',':'||ID_LIST||':') > 0 
            then null 
            else 'OK' 
        end as FINAL_PATH,
        PATH_LENGTH, 
        ID,
        P_ID,
        NAME,
        TYPE,
        ID_LIST,
        NAME_LIST,
        TYPE_LIST
  FROM 
      (
      SELECT 
            LEVEL as PATH_LENGTH, 
            ID,
            P_ID,
            NAME,
            TYPE,
            SUBSTR(SYS_CONNECT_BY_PATH(ID, ':'),2) as ID_LIST,
            SUBSTR(SYS_CONNECT_BY_PATH(NAME, ':'),2) as NAME_LIST,
            SUBSTR(SYS_CONNECT_BY_PATH(TYPE, ':'),2) as TYPE_LIST
      FROM T_ROUTE_EXAMPLE M1
      start with M1.P_ID is null
      CONNECT BY prior M1.ID = M1.P_ID
      )
  )
WHERE FINAL_PATH = 'OK'
Veröffentlicht von Tobias Arnhold Am 15:23
Kategorien:
APEX-AT-WORK no image

Update an APEX tree dynamically

Von Tobias Arnhold → 12.01.2013
I was asked by an APEX developer if it is possible to dynamically update an APEX tree by changing an APEX item.

Example select:
select case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status,
       level,
       ENAME as title,
       NULL  as icon,
       EMPNO as value,
       ENAME as tooltip,
       NULL  as link
from EMP
start with (:P1_MGR is null AND MGR is null OR :P1_MGR is not null and MGR = :P1_MGR)
connect by prior EMPNO = MGR
order siblings by ENAME


As far as I know APEX has no out of the box function for that. The dynamic action to refresh reports doesn't work with trees. Or am I wrong here?
You can follow different workarounds no one of them will fulfill all your hopes:

1. Wait for APEX 5.0 maybe the APEX team will include a dynamic tree update functionality?
2. Build some custom JS code to dynamically change the tree.
3. Don't make it dynamically. Submit the page after you have changed the APEX item.
4. Use a third party solution for example: dhtmlx tree
5. Build your own tree solution (plug-in).

I would tend to solution 3 and would wait for the next version of APEX. If the customer doesn't accept it and would pay the more efforts I would tend to a third party solution.  

Update 03.12.2013:
Take a look at Tom's blog he made a couple of really good tree based blog posts.
Veröffentlicht von Tobias Arnhold Am 10:32
Kategorien: , , ,
APEX-AT-WORK no image

Creating an ExtJS tree with PL/JSON

Von Tobias Arnhold → 2.04.2010
I searched for a smoother way building ExtJS trees in APEX and found a great post from Anja Hildebrandt: Nice trees with ExtJS (Erstellung eines ExtJS-Baums…)

She used the PL/JSON Utility from Lewis Cunningham: PL/JSON by jkrogsboell, lewiscunningham

During the time now Lewis developed a new version of the utility and Anjas code didn't work anymore. I updated her source code with the new version of PL/JSON 0.8.6.

First I show how you build JSON output with the PL/JSON scripts:
Example - ex4.sql

SQL> declare
  2    obj json;
  3    procedure p(v varchar2) as begin dbms_output.put_line(null);dbms_output.put_line(v); end;
  4  begin
  5    p('you can also put json or json_lists as values:');
  6    obj := json(); --fresh json;
  7    obj.put('text', 'Audi');
  8    obj.put('id', 100);
  9    obj.put('leaf', json_bool(true));
 10    obj.put('href', 'f?p=110:1:');
 11    obj.put('children', json_list('[{"text": "A4"},{"text": "A5"}]'));
 12    obj.print;
 13  end;
 14  /
 
 
you can also put json or json_lists as values:
{
  "text" : "Audi",
  "id" : 100,
  "leaf" : true,
  "href" : "f?p=305:1:",
  "children" : [{
    "text" : "A4"
  }, {
    "text" : "A5"
  }]
}
 
PL/SQL procedure successfully completed

PL/JSON forum example
 
SQL> 
SQL> DECLARE
  2    resultset json;
  3    row_list  json_list := json_list();
  4    columns1   json;
  5    num_rows  number := 2;
  6    columns_length  number := 3;
  7    procedure p(v varchar2) as begin dbms_output.put_line(null);dbms_output.put_line(v); end;
  8  BEGIN
  9    p('Data:');
 10    FOR i IN 1 .. num_rows
 11    LOOP
 12      columns1 := json();
 13      columns1.put('rownum', i);
 14      FOR x IN 1 .. columns_length
 15      LOOP
 16        columns1.put('column' || x, 'Testdata');
 17      END LOOP;
 18      row_list.add_elem(columns1.to_anydata);
 19    END LOOP;
 20    resultset := json();
 21    resultset.put('ResultSet', row_list);
 22    resultset.print;
 23  END;
 24  /
 
 
Data:
{
  "ResultSet" : [{
    "rownum" : 1,
    "column1" : "Testdata",
    "column2" : "Testdata",
    "column3" : "Testdata"
  }, {
    "rownum" : 2,
    "column1" : "Testdata",
    "column2" : "Testdata",
    "column3" : "Testdata"
  }]
}
 
PL/SQL procedure successfully completed
 
SQL>

Updated tree package of Anja

create or replace package PKG_EXTJS_JSON is

  -- Author  : AHILDEBRANDT
  --         : TARNHOLD
  -- Created : 10.08.2009
  -- Updated : 04.02.2010
  -- Purpose : check if category has sub-categories
  function hasChildren(i_cat_id in number) return boolean;

  -- Purpose : create JSON-Object for category;
  --           recursive
  function getJsonObject(i_cat_id in number default 0) return json;

  -- Purpose : function that calls getJsonObject and returns the result as string
  function getTreeDataDynamic return varchar2;

end PKG_EXTJS_JSON;

create or replace package body PKG_EXTJS_JSON is
/*
  -- Author  : AHILDEBRANDT
  --         : TARNHOLD
  -- Created : 10.08.2009
  -- Updated : 04.02.2010
  -- Purpose : check if category has sub-categories
*/
function hasChildren(i_cat_id in number) return boolean is
  v_count number:=0;
begin
  select nvl(count(*),0) into v_count from tbl_categories where c_par_id=i_cat_id and c_active = 'YES';
  if v_count>0 then
    return true;
  else
    return false;
  end if;
end;

/*
  -- Author  : AHILDEBRANDT
  --         : TARNHOLD
  -- Created : 10.08.2009
  -- Updated : 04.02.2010
  -- Created : 10.08.2009
  -- Purpose : create JSON-Object for category;
  --           recursive
*/
function getJsonObject(i_cat_id in number default 0) return json is
  v_json json:=json();
  v_row_list json_list := json_list();
  v_arr_id NUMBER;
  v_ele_id NUMBER;
begin

  if i_cat_id = 1 then -- if category is root then set root-node
    v_json.put(pair_name => 'id', pair_value => i_cat_id);
    v_json.put(pair_name => 'text', pair_value => 'Root');
  else -- else --> usual node; use category infos
    for cat in (select * from tbl_categories where c_id=i_cat_id and c_active = 'YES') loop
      v_json.put(pair_name => 'id', pair_value => i_cat_id);
      v_json.put(pair_name => 'text', pair_value => cat.c_name);
    end loop;
  end if;

  if not hasChildren(i_cat_id) then -- if node has no children
    v_json.put(pair_name => 'leaf', pair_value => json_bool(true)); -- mark as leaf
    v_json.put(pair_name => 'href', pair_value => 'f?p=110:1:'||v('APP_SESSION')); -- set a link if needed
    -- v_json.put(pair_name => 'href', pair_value => 'javascript:show_cat('||i_cat_id|| ');');  -- set a javascript function
  else -- sonst
    v_json.put(pair_name => 'leaf', pair_value => json_bool(false)); -- mark as node with children
    for child in (select * from tbl_categories where c_par_id=i_cat_id and c_active = 'YES') loop -- loop through all sub-categories
      -- create JSON-object for sub-category using recursive call and the append to array
      v_row_list.add_elem(getJsonObject(i_cat_id => child.c_id).to_anydata);
    end loop;
    v_json.put(pair_name => 'children', pair_value => v_row_list); -- add array for subcategories
  end if;

  return v_json;

end;

/*
  -- Author  : AHILDEBRANDT
  --         : TARNHOLD
  -- Created : 10.08.2009
  -- Updated : 04.02.2010
  -- Purpose : function that calls getJsonObject and returns the result as string
*/
function getTreeDataDynamic return varchar2 is
  v_json json:=json();
begin
  v_json:=getJsonObject(i_cat_id => 1);
  return('['||v_json.to_char||']');
end;

end PKG_EXTJS_JSON;

Utilities like this makes developing much more faster!
Veröffentlicht von Tobias Arnhold Am 23:49
Kategorien: , , ,
APEX-AT-WORK no image

APEX navigation concepts

Von Tobias Arnhold → 8.14.2009
Last couple of month I worked on new navigation concepts and wanted to share my ideas and experiences with you.

APEX navigation concepts

Download PDF version: APEX_navigation_concepts.pdf

Feel free to share your opinion about APEX navigation concepts!
Veröffentlicht von Tobias Arnhold Am 10:49
Kategorien: , ,
APEX-AT-WORK no image

ExtJS navigation tree

Von Tobias Arnhold → 6.19.2009
Hi folks,

I added an ExtJS navigation tree to my example application. To get the JSON data out of the database I created a small package which is dynamically creating this data string on every page load.

Take a look at the updated source code: AAW - ExtJS navigation tree

I got some request about the application installation file (application source code). So I decided to publish it to everybody how asks for it via email: tobias-arnhold@hotmail.de
I would use the same procedure as Denes does. You get an developer account and can download the current version yourself.

PS, feedback is always welcome! :D
Veröffentlicht von Tobias Arnhold Am 15:25
Kategorien: , ,
APEX-AT-WORK no image

Advanced APEX trees

Von Tobias Arnhold → 9.26.2008
Last couple of weeks/days when i went through the Oracle APEX forum there was quite a lot of questions about the APEX trees.
I really worked a lot with them and want to describe how to use them.

1. This how to will base on the following table ddl:
CREATE TABLE  "LOG_SYSTEMS"
( "S_ID" NUMBER NOT NULL ENABLE,
"S_NAME" VARCHAR2(100) NOT NULL ENABLE,
"S_DESCRIPTION_SHORT" VARCHAR2(100),
"S_PARENT" NUMBER NOT NULL ENABLE,
"S_RESPONSIBLE_DEPARTMENT" VARCHAR2(5),
"S_CONTACT_PERSON" VARCHAR2(100),
"S_TYPE" VARCHAR2(20)
CONSTRAINT "PK_S_ID" PRIMARY KEY ("S_ID") ENABLE
)
2. How does the standard SQL selection of a APEX tree look like

select "S_ID" id, -- Primary key
"S_PARENT" pid, -- Parent key
"S_NAME" name, -- Displayed value (node description)
'f?p=&APP_ID.:1:&SESSION.::NO::P1_SYSTEM:'||"S_ID" link, -- linked page
null a1,
null a2
from "#OWNER#"."LOG_SYSTEMS"

3. How to improve the displayed value (node)
You can concat several columns:
-- name with description
"S_NAME" || ' (' || "S_DESCRIPTION" || ')' AS name,

-- name with html (big)
'<b>' || "S_NAME" || '</b>' AS name,

-- name with icon in front of it
'<img src="#WORKSPACE_IMAGES#tree_group.png" border="0" style="vertical-align:middle" alt="Group">&nbsp ' || "S_NAME" AS name,

-- Using of the case command to see different node values
CASE
WHEN "S_TYPE" = 'GROUP' THEN '<b>' || "S_NAME" || '</b>'
WHEN "S_TYPE" = 'SYSTEM' THEN "S_NAME" || ' (' || "S_DESCRIPTION" || ')' END AS name,

4. How to use the link column
-- Standard link column would link to page 1 and overwrite the
-- value of :P1_SYSTEM
'f?p=&APP_ID.:1:&SESSION.::NO::P1_SYSTEM:'||"S_ID" link,

-- Using several values to overwrite in page
'f?p=&APP_ID.:1:&SESSION.::NO::P1_SYSTEM,P1_S_NAME,P1_FROM_TREE:'|| "S_ID"||','||"S_NAME"||',YES' AS link,

-- Link to a specified page via node value
'f?p=&APP_ID.:' ||"S_ID" || ':&SESSION.::NO::' AS link,

-- Link to a specified page via node value and javascript
'javascript:change_page(' || "S_ID" || ')'

Go Edit Page > HTML Header
<script language="JavaScript" type="text/javascript">
function change_page(v_page) {
var v_url = 'f?p=&APP_ID.:' + v_page + ':&SESSION.::::';
window.location.href = v_url;
}
</script>

-- If you use your tree as a pop up tree and want to give back a node
-- value to the parent page with submit and close the pop up
-- page automatically afterward
-- Info: I used it as a self made pop up tree item
'javascript:close_page(' || "S_ID" || ',''' || "S_NAME" || ''')' AS link,

-- I also used a hidden item in the pop up page to fill with the parent
-- page id &P101_WHEREFROM.
Go Edit Page > HTML Header
<script language="JavaScript" type="text/javascript">
function close_page(v_id,v_value) {
var v_page = 'P' + '&P100_WHEREFROM.' + '_S_ID';
var v_page_display = 'P' + '&P101_WHEREFROM.' + '_S_NAME';
var l_field_id = opener.document.getElementById(v_page);
l_field_id.value = v_id;
if(l_field_id.getAttribute('onchange') || l_field_id.onchange) {l_field_id.onchange()}
var l_field_value = opener.document.getElementById(v_page_display);
l_field_value.value = v_value;
if(l_field_value.getAttribute('onchange') || l_field_value.onchange) {l_field_value.onchange()}
close();
window.opener.doSubmit('POPUP_RELOAD');
}
</script>

I used these examples in my application TIA: http://apex.oracle.com/pls/otn/f?p=25500:1
Login: guest Password: apexuser
Download it from: http://www.oracle-apex-award.de/TIA-Technische-Infrastruktur.70.0.html
The application is in German but the developments (used variables, hints) are made in English.

Hope you can use it in your application and of course you are welcome http://www.blogger.com/img/blank.gifto give me some feedback.

Update 12.09.2011:
The example application doesn't work anymore. I currently work on a new version which includes the new APEX tree as well:
http://apex-at-work.blogspot.com/2011/09/logbuch.html
I will try to extend the view of the standard APEX tree as well as I did with the old one. Of course I will write about it. :)

Update 26.09.2011:
I just saw a nice example application about APEX trees:
http://apex.oracle.com/pls/apex/f?p=36648:27
Veröffentlicht von Tobias Arnhold Am 21:24
Kategorien:
Subscribe to: Posts ( Atom )