7 Tips to survive your first ever conference presentation ;)

Are you sometimes bored or think, you would like to try something new? Something that challenges you?

I was bored, so I submitted a last minute  abstract for Apex Connect 2016 last november. You just have to write a little text about what you would like to talk about. We all have something to talk about, right? So that part was easy. And the conference was scheduled for april 2016. You probably think… That is a loooooong time. Lots of time to finish the project, prepare the perfect presentation and practice your speaker skills. Think again!

Deadlines have a habit of sneaking up on you. So...

Tip No. 1 : Start right away!

If it is not part of your usual work anyway, schedule some time every week to work on your project and/or presentation.

You might also want to think about some presentations you can do to practice your speaker skills. We have a regular thing called "DevTalk", where anyone can speak about stuff they're working on. Another opportunity could be a meetup  or a smaller conference in your area. Keep your eyes open for things like that. It really helps.

Tip No. 2: Keep it simple!

While preparing your presentation you should keep in mind, that you usually only have 45 minutes time for your presentation. You don't have time for all the interesting little details you might want to talk about. So try to do an overview, so everyone gets an overview of the topic and has a place to start to dig in. You can always prepare an additional paper or some blog posts with more detailed information.

By the way… you don't have to keep an eye on your watch the whole time. There will probably be someone there, who will give you a sign, when your time runs out.

Tip No. 3: "Testdrive" your final presentation!

Hopefully you are done with your final presentation at least two weeks before the conference. I did a trial run of my presentation for my colleagues. I got some great feedback and i had a feeling how much time i really needed. I ended up changing a few things and delete a view slides, since the presentation took a bit more time then i had anticipated.

Tip No. 4: Don't do last minute changes! (except of course you really have to)

It's the day before the presentation and you are probably quite nervous by now. At least i was and i was constantly going over my slides. And every time i found something, that i thought i could have done better. But I decided to leave it as is. I only did one change. I added a slide with a quote I found on linkedIn earlier that day.

"No matter how many mistakes you make or how slow you progress, you are still way ahead of everyone who isn't trying."

I felt, that this quote sums it up very well and it actually helped me to...

Tip No.5: Have a good nights sleep!

So, not much to say about this one…

Tip No. 6: Don't freak out! Murphy will have his way, anyway…

So, it is the day of the presentation. You might fell like running the other way right now. I sure did. I never was good at speaking in front of a crowd. Especially a crowd, i did not know. So this was the moment i thought: What were you thinking???? The hour before my slot i used to go over my presentation one more time and prepare all the tools i would use in my demo. I felt really well prepared. And then this happened….

I went into the room to set up my laptop, but the projector did not work. The tech guys got on it right away but they did not get it to work before the talk was scheduled to start. Since i knew exactly how much time i needed for my presentation i decided to start without my slides. You usually have some introduction stuff you can do without slides. So i hoped for the best and went on with it…

A few minutes into the presentation the doors opened and a really huge ladder was set up right in front of me… it was kind of surreal, but funny at the same time… they worked on the projector and finally got it to work. So i could move on and finish my presentation with my slides and the demo.

Remote control 2.0 at #apexconn16 during @anjeli2001 first public talk … pic.twitter.com/gDBF9GjQI1

— Roel Hartman (@RoelH) April 26, 2016

Real Hardware issues on the software conference #APEXconn16. And still calm: @anjeli2001 congrats! pic.twitter.com/4ahCZzh5gn

— Stephan La Rocca (@StephanLaRocca) April 26, 2016

Did i mention that this was the room where all the presentations where filmed. So there is proof of this story somewhere.

What I'm trying to say is… No matter how well prepared you feel, there are always things that can and probably will go wrong. In that case: Don't freak out, just smile and talk through it!

Tip No.7: Relax and give yourself a pat on the shoulder!

You've done it! You made it through your very first conference presentation in one piece. Be proud and enjoy the moment.

So, there you have it. My tips to survive your very first conference talk. I actually enjoyed the experience enough to submit 3 abstracts for DOAG 2016 right away.

What about you? Any fun stories about your first public talk?

Refresh template based extjs tree dynamically...

In the last few posts i talked about how to use apex templates to create extjs trees and property grids. In this post i will show you how to refresh these regions dynamically....
Our report statement needs a special result set...

<br />select  dname as CATEGORY<br />      , empno as ID<br />      , ename as NAME<br />      , rank() over (partition by emp.deptno order by empno asc) as ROW_ASC<br />from emp, dept<br />where emp.deptno=dept.deptno<br />

Next thing we need is a template... I called mine "ExtJS_Tree_Report_With_Refresh".... It's basically the same as before....
Row Template 1

<br />{"text":"#CATEGORY#","expanded":true,"leaf":false,"children":[<br />{"text":"#NAME#","id":"#ID#","leaf":true}<br />

Row Template 1 Expression

<br />#ROW_ASC#=1 and #ROWNUM#=1<br />

Row Template 2

<br />]},{"text":"#CATEGORY#","expanded":true,"leaf":false,"children":[<br />{"text":"#NAME#","id":"#ID#","leaf":true}<br />

Row Template 2 Expression

<br />#ROW_ASC#=1 and #ROWNUM#!=1<br />

Row Template 3

<br />,{"text":"#NAME#","id":"#ID#","leaf":true}<br />

Row Template 3 Condition

<br />#ROW_ASC#!=1<br />

Before Rows

<br /><div id="tree-div#REGION_ID#">[<br />

After Rows

<br />]}]</div><br /><script type="text/javascript"><br />  Ext.onReady(function(){<br />  initTreeRegion('#REGION_ID#');<br />  })<br /></script><br />

The function "initTreeRegion" looks like that....

<br />function initTreeRegion(pId) {<br />    var myDiv = $x('tree-div'+pId);<br />    var myData = Ext.util.JSON.decode(myDiv.innerHTML);    <br />        myDiv.innerHTML = '';<br />        new Ext.tree.TreePanel({<br />          renderTo: 'tree-div'+pId,<br />          border:false,<br />          id: 'REGION_'+pId+'_tree',<br />          cls: 'myTreePanel',<br />          margins: '2 2 0 2',<br />          autoScroll: true,<br />          rootVisible: false,<br />          root:{ "text": "rootnode",<br />                 "expanded": true,<br />                 "children": myData <br />                }<br />        });<br />}<br />

It just recreates the extjs tree using the json object generated by refreshing the report region via $a_report()....
You can now easily refresh every tree region like that...

<br />$a_report('9159609248948926','1','100');<br />initTreeRegion('R9159609248948926');<br />

You only need to know the ID of the region...
Have fun...

Post only available in english!

ExtJS Property Grids in APEX...

I found this example for creating an extjs property grid, which made me think how nice it would be, to have this in apex.... so i played around and came up with another report template....
Let's look at the example....

var propsGrid = new Ext.grid.PropertyGrid({
        renderTo: 'prop-grid',
        width: 300,
        autoHeight: true,
        source: {
            '(name)': 'Properties Grid',
            grouping: false,
            autoFitColumns: true,
            productionQuality: false,
            created: new Date(Date.parse('10/15/2006')),
            tested: false,
            version: 0.01,
            borderWidth: 1
        },
        viewConfig : {
            forceFit: true,
            scrollOffset: 2 // the grid will never have scrollbars
        }
    });

As you can see, what we basically have to do is to create the source part.... pretty much usual value pairs....
So, i created a new report template of type "Generic Columns (column template)" ... I called it ExtJS_PropertyGrid_Report...
Before Rows

<div id="prop-grid#REGION_STATIC_ID#"></div>
<script type="text/javascript">
Ext.override(Ext.grid.PropertyGrid, {
       removeProperty: function(property){
            delete this.source[property];
            var r = this.store.getById(property);
            if(r){
                // remove property
                this.store.remove(r);
            }
        }
   });

function initRegion#REGION_STATIC_ID#() {
    var propsGrid = new Ext.grid.PropertyGrid({
        applyTo: 'prop-grid#REGION_STATIC_ID#',
        id: 'myPropertyGrid#REGION_STATIC_ID#',
        width: 300,
        autoHeight: true,
        source: {

I copied more or less the example from above, added #REGION_STATIC_ID# to make the names and ids unique.... Then i just add a value pair for every column in the report.... (Reports, that use this template should only have one row!!!)

Column Template 1

'#COLUMN_HEADER#':'#COLUMN_VALUE#',

I have a comma to much, so i add a dummy entry, which i instantly remove when the grid is created.....
After Rows

'MYDUMMY123':''},
        viewConfig : {
            forceFit: true,
            scrollOffset: 2 // the grid will never have scrollbars
        }
    });
propsGrid.removeProperty('MYDUMMY123'); // remove the dummy entry
}

Ext.onReady(function(){
  initRegion#REGION_STATIC_ID#();
});
</script>

I put the whole magic inside a function, so i can call it again, wenn i refresh the report via $a_report()....
Now we can create a report region with any select, that generates a one row result set.... the column names have to be unique....
For example:

select '01.06.1980' as "Birthday"
     , 'Anja' as "Firstname"
     , 'Hildebrandt' as "Name"
     , 'Developer' as "Profession" 
  from dual

Select your new created template...
The result shoul look something like this....

That's it....
Have fun...
Anja

ExtJS tree via Apex report template....

This morning i needed to show some data in a simple ExtJS tree... Just a view categories with a few children each.... I could have created the needed json string with the pl/sql function i described in an earlier post, but i finally decided to build a little report template to use for this easy stuff...
That's what i came up with....

Create a new report template of type "Named Column (row template)"... I called mine "ExtJS_Tree_Report"...

Define the Row Templates as follows...

Row Template 1

<br />{  "text"     : "#CATEGORY#"<br /> , "expanded" : true<br /> , "leaf"     : false<br /> , "children" : [<br />                  {  "text" : "#NAME#"<br />                   , "id"   : "#ID#"<br />                   , "leaf" : true<br />                  }<br />

Use Based on PL/SQL Expression:

<br />#ROW_ASC#=1 and #ROWNUM#=1<br />

Row Template 2

<br />]},{  "text"     : "#CATEGORY#"<br />    , "expanded" : true<br />    , "leaf"     : false<br />    , "children" : [<br />                    {  "text" : "#NAME#"<br />                     , "id"   : "#ID#"<br />                     , "leaf" : true<br />                    }<br />

Use Based on PL/SQL Expression:

<br />#ROW_ASC#=1 and #ROWNUM#!=1<br />

Row Template 3

<br />,{  "text" : "#NAME#"<br />  , "id"   : "#ID#"<br />  , "leaf" : true<br /> }<br />

Use Based on PL/SQL Expression

<br />#ROW_ASC#!=1<br />

Before Rows

<br /><div id="tree-div#REGION_STATIC_ID#"></div><br /><script type="text/javascript"><br />Ext.onReady(function(){<br />  new Ext.tree.TreePanel({<br />            renderTo    : 'tree-div#REGION_STATIC_ID#',<br />            border      : false,<br />            id          : 'tree-panel',<br />            margins     : '2 2 0 2',<br />            autoScroll  : true,<br />            rootVisible : false,<br />            root:{ <br />                 "text"     : "rootnode",<br />                 "expanded" : true,<br />                 "children" : [<br />

After Rows

<br />     ]} <br />   ]}<br />  }); // new Ext.tree.TreePanel...  <br />}); //Ext.onReady...<br /><br /></script><br />

Now, create a new report region in your page with the following statement...

<br />select  dname as CATEGORY<br />      , empno as ID<br />      , ename as NAME<br />      , rank() over (partition by emp.deptno order by empno asc) as ROW_ASC<br />from emp, dept<br />where emp.deptno=dept.deptno<br />

These are the minimum columns my report template needs...
In the report attributes select your created report template and disable pagination...
That's it... Your result should look like this....

APEX navigation bar in sexy extJS toolbar...

It's been a while ;).... But i have a lot of new APEX and ExtJS stuff to blog about. I've been working on a complete extJs based template for my latest APEX project... So here is my first part of the upcoming series.... It's about how to get the usual apex navigation bar to show up in a sexy extJS toolbar...
There are 3 options i always have in my toolbar - logout, help, home... I usually use image buttons, so my definition looks something like this...


And in more detail....

Now we have to build up a little JSON object via navigation bar template.... This is what i put in my page template....

navigation bar

[{
  xtype: 'box',
  el: 'my_reg_pos_08'
},'->'#BAR_BODY#]

navigation bar entry

,{
  xtype: 'box',
  autoEl: {
    tag: 'a',
    href: '#LINK#',
    cn: '<img src="#IMAGE#" alt="#TEXT#" />',
    title : '#TEXT#'
  }
},{xtype: 'tbspacer', width: 10}

As you can see, I build the complete json string to define my toolbar items.... I also include one of my region-position-divs (defined in the body below), because i need a bit of form-magic inside my toolbar....
The body looks something like this...

body

<div id="my_nav_bar"></div>
<div id="myTabs"></div>
<div id="my_messages">#GLOBAL_NOTIFICATION##SUCCESS_MESSAGE##NOTIFICATION_MESSAGE#</div>
<div id="my_box_body" width="100%">#BOX_BODY#</div>
<div id="my_reg_pos_01">#REGION_POSITION_01#</div>
<div id="my_reg_pos_02">#REGION_POSITION_02#</div>
<div id="my_reg_pos_03">#REGION_POSITION_03#</div>
<div id="my_reg_pos_04">#REGION_POSITION_04#</div>
<div id="my_reg_pos_05">#REGION_POSITION_05#</div>
<div id="my_reg_pos_06">#REGION_POSITION_06#</div>
<div id="my_reg_pos_07">#REGION_POSITION_07#</div>
<div id="my_reg_pos_08">#REGION_POSITION_08#</div>

<script type="text/javascript">
Ext.onReady(function(){
   var nb = #NAVIGATION_BAR#;
   var tb = new Ext.Toolbar({renderTo: 'my_nav_bar', items: nb});

var myform = new Ext.ux.FormViewport({
        layout:'border',
        items:[new Ext.Panel({
                    region:'north',
                    id: 'my_toolbar',
                    height: 40,
                    collapsible: false,
                    margins:'0 0 0 0',
                    allowDomMove:false,
                    tbar: tb                  
                   }),new Ext.Panel({
                    region:'south',
                    id: 'footer',
                    height: 22,
                    collapsible: false,
                    margins:'0 0 0 0',
                    html:'<div id="DeveloperToolbar"></div>'        
                   }),
              new Ext.Panel({
            region:'west',
            id:'my_info',
            title:'Info',
            split:true,
            width: 220,
            minSize: 175,
            maxSize: 400,
            collapsible: true,
            margins:'40 0 5 5',
            cmargins:'40 0 5 5',
            contentEl: 'my_reg_pos_01',
            layoutConfig:{
                animate:true
            }
        }),new Ext.Panel({region:'center',
            margins:'40 5 5 5',
            id: 'my_workarea',
            items: [tabs],
            layout : "fit"
        })
              ]
    });
});
</script>

I don't use any usual html table layout. I only define a bunch of div regions according to the region positions, messages, navigation bar etc...
In the onReady-function i use the #NAVIGATION_BAR# substitution string to put my created json string inside a variable.

var nb = #NAVIGATION_BAR#;

Then i use nb as item parameter for my toolbar object ...

var tb = new Ext.Toolbar({renderTo: 'my_nav_bar', items: nb});

After that i am able to use tb in my actual page layout.... If everything works, it shoul look something like this....

In case your wondering... I used the customized viewport from Mark Lancaster to make sure, that my elements stay inside my apex form...
To get my developer toolbar inside the footer i used another tip from Mark, which you find here...

Regions as tabs using ExtJS... (Regionen als Tabs anzeigen mit ExtJS)

Title: Regions as tabs using ExtJS...

I'm working on an ExtJS-Theme right now. One of the first features i wanted to integrate was to show regions as tabs, but i wanted it to be defined only by the position of the region and its template. To do that i created the following region template.

Ich arbeite momentan an einem ExtJS-Theme. Eine der ersten Funktionalitäten, die ich hierbei vorgesehen habe, ist das anzeigen von Regionen als Tabs. Dabei möchte ich keine großartigen Einstellungen in den einzelnen Regionen selbst vornehmen, sondern nur über die Positionierung definieren, welche Regionen als Tab angezeigt werden. Hierfür hab ich zunächst ein Regionstemplate wie folgt angelegt.

The attribute class=”x-tab” marks the regions that should be displayed as tabs. The title attribute defines the name of the tab (title=”#TITLE#”). Now we have to change a few things in our page template. For my little example i want all regions in the BOX_BODY area to be displayed as tabs, so i place the #BOX_BODY# inside a div-tag.

Das Attribut class=”x-tab” kennzeichnet die umzuwandelnden Regionen. Als Tabbezeichnung wird der Titel der Region angezeigt (title=”#TITLE#”). Als nächstes sind ein paar Änderungen am Seitentemplate notwendig. Für mein kleines Beispiel möchte ich, das alle Regionen im BOX_BODY als Tabs angezeigt werden. Dazu schließe ich diesen in eine benannte DIV-Region ein.

#BOX_BODY#

Next thing to do is to put the following javascript inside the page-template too. It uses the autoTabs-Option of ExtJS, which creates tabs based on existing markup.

Nun nutze ich die autoTabs-Option von ExtJS, um die vorhandenen Regionen im BOX_BODY automatisch in Tabs umzuwandeln. Dazu folgendes Skript ebenfalls ins Seitentemplate kopieren.

In this script two parameters are important. First one is the “autoTabs:true”, which triggers the ExtJS feature we're using to replace our normal regions with tab-regions. Second one is “applyTo: ‘box_body’”, which defines the div-region this feature should be used on. Now we are ready to display regions as tabs by setting our created region template and positioning the region inside the BOX_BODY.

Wichtig hierbei sind die Parameter “autoTabs:true” und “applyTo: ‘box_body’”, die angeben auf welchen Bereich die autoTab-Funktion angewendet werden soll. Nun genügt es beim Erstellen einer neuen Region das neue Regionstemplate anzugeben und die Region im BOX_BODY zu platzieren.

The result should look something like this.

Das Ergebnis sollte etwa so aussehen.

By the way, the usual conditions to show or hide the regions still work.

Übrigens funktioniert das bedingte ein- und ausblenden der Regionen immer noch.

Nice trees with ExtJS (Erstellung eines ExtJS-Baums…)

Bekannterweise ist die in APEX standardmäßig verfügbare Baumstruktur suboptimal und nicht wirklich sexy. Abhilfe kann hier die ExtJS-Bibliothek schaffen, die eine einfach zu definierende Baumstruktur anbietet. Links ist ein Beispiel einer solchen sexy Baumstruktur… In diesem Beitrag möcht ich kurz zeigen, wie sich ein solcher Baum einfach in eine APEX-Anwendung einbinden und mit Daten aus der Datenbank füttern lässt. Meistens wird beim Austausch von Standardelementen der Weg bevorzugt, das Element erst von APEX laden zu lassen und es dann entsprechend zu verändern. Ich denke bei einem Baum ist das nicht unbedingt notwendig, so das ich diesen von Grund auf durch ExtJS generieren werde.

Zunächst mal ein paar Worte zu ExtJS. ExtJS ist ein clientseitiges Javascript bzw. AJAX-Framework, das browserunabhängige Funktionalitäten für die DOM-Manipulation, Event-Handling oder auch asynchrone Serverkommunikation zur Verfügung stellt. Die API von ExtJS ist leicht erlernbar und wirklich intuitiv nutzbar, da sie fast vollständig JSON gestützt ist.

Für das folgende Beispiel wurde eine APEX-Anwendung mit einer Seite erstellt. Innerhalb dieser Seite gibt es eine einfache Region, die als Inhalt eine DIV-Region mit der Id “tree” definiert.

Folgende Stylesheets und Javascript-Dateien aus dem ExtJS-Package sollten im Headerbereich eingebunden sein.

<link rel="stylesheet" type="text/css" href="#IMAGE_PREFIX#javascript/ext-2.2.1/resources/css/ext-all.css" />

<script type="text/javascript" src="#IMAGE_PREFIX#javascript/ext-2.2.1/adapter/ext/ext-base.js"></script>
<script type="text/javascript" src="#IMAGE_PREFIX#javascript/ext-2.2.1/ext-all.js"></script>

Zusätzlich wurde folgende JS-Funktion im Footer der Seite hinterlegt.

    Ext.onReady(function(){
    new Ext.Panel({
      title: 'My first tree',
      renderTo: 'tree',
      layout: 'border',
      width: 500,
      height: 500,
      items: [{
          xtype: 'treepanel',
          id: 'tree-panel',
          region: 'center',
          margins: '2 2 0 2',
          autoScroll: true,
          rootVisible: false,
          root: { text: "rootnode",
              expanded: true,
              children: [{ text:"node 1",
                           id:1,
                           leaf:true
                         },
                         { text: "node 2",
                           id: 2,
                           leaf:false,
                           expanded: true,
                           children:[{ text:"node 1",
                                         id:3,
                                       leaf:true
                                     }
                                    ]
                         }
                        ]
                },

      }]
  });
});

Was passiert hier? Ganz einfach…

Wenn die Seite vollständig geladen ist, wird die onReady-Funktion ausgeführt. Innerhalb dieser Funktion wird ein neues ExtJS-Panel mit dem Titel “My first tree” generiert. Die Option renderTo gibt an, in welchem Bereich der Seite das neu erstellte Panel eingefügt wird. In diesem Beispiel die in unserer Seite definierte DIV-Region.

Innerhalb dieses Panels wird ein TreePanel generiert, was einen nicht sichtbaren (rootVisible:false) Root-Knoten bekommt, der zwei Unterknoten (children) “node 1” und “node 2” enthält. “node 2” wiederum hat einen weiteren Unterknoten.

Die entscheidenden Werte zur Definition einen Knoten sind:

text: Der Text der für den betreffenden Knoten im Baum angezeigt werden soll.

id: Eine eindeutige Bezeichnung für den Knoten, z.B. die tatsächliche numerische ID einer Kategorie oder ähnliches.

leaf: Diese Option gibt an, ob der betreffende Knoten ein Blatt ist oder ob er weiter Unterknoten enthält.

children: Ist ein Array der Unterknoten, sofern vorhanden.

href: Muss gesetzt werden, falls der Knoten als Link funktionieren soll.

Nun hat man natürlich eher selten eine feste Baumstruktur abzubilden. Normalerweise wollen wir eine dynamische Struktur aus der Datenbank laden.

Hierzu erstellen wir eine Datenbank-Funktion, die das JSON-Objekt als Text zurückgibt. Da es relativ mühselig wäre die dafür benötigte Struktur zusammenzubauen, bin ich mal auf die Suche nach einer fertigen Lösung gegangen. Und siehe da, es gibt bereits ein JSON-Datentyp für Oracle. Vielen Dank an Lewis Cunningham dafür.

Ok, bereiten wir also erstmal ein paar Testdaten und die benötigte Funktion auf der Datenbank vor.

create table tbl_mon_category (cat_id number, cat_par_id number, cat_label varchar2(100));

insert into tbl_mon_category (cat_id,cat_par_id,cat_label) values (1,0,'Node 1');
insert into tbl_mon_category (cat_id,cat_par_id,cat_label) values (2,0,'Node 2');
insert into tbl_mon_category (cat_id,cat_par_id,cat_label) values (3,1,'Childnode A for Node 1');
insert into tbl_mon_category (cat_id,cat_par_id,cat_label) values (4,2,'Childnode A for Node 2');
insert into tbl_mon_category (cat_id,cat_par_id,cat_label) values (5,2,'Childnode B for Node 2');
insert into tbl_mon_category (cat_id,cat_par_id,cat_label) values (6,2,'Childnode C for Node 2');
commit;

create or replace package PCK_EXTJS_JSON_EXAMPLE is

-- Author  : AHILDEBRANDT
-- Created : 10.08.2009

-- Purpose : Prüfen, ob eine Kategorie und Unterkategorien hat
function hasChildren(i_cat_id in number) return boolean;

-- Purpose : anhand einer übergebenen Kategorie-ID das
--           vollständige JSON-Objekt generieren (nutzt
--           Datentyp PL/JSON; arbeitet rekursiv!)
function getJsonObject(i_cat_id in number default 0) return json;

-- Purpose : Aufruf der Funktion zum Generieren des
--          JSON-Objekts und Rückgabe des Ergebnisses
--          als String
function getTreeDataDynamic return varchar2;

end PCK_EXTJS_JSON_EXAMPLE;
/
create or replace package body PCK_EXTJS_JSON_EXAMPLE is
/*
-- Author  : AHILDEBRANDT
-- Created : 10.08.2009
-- Purpose : Prüfen, ob eine Kategorie und Unterkategorien hat
*/
function hasChildren(i_cat_id in number) return boolean is
v_count number:=0;
begin
select nvl(count(*),0) into v_count from tbl_mon_category where cat_par_id=i_cat_id;
if v_count>0 then
  return true;
else
  return false;
end if;
end;

/*
-- Author  : AHILDEBRANDT
-- Created : 10.08.2009
-- Purpose : anhand einer übergebenen Kategorie-ID das vollständige JSON-Objekt generieren
--           (nutzt Datentyp PL/JSON)
--           ACHTUNG! REKURSIV!
*/
function getJsonObject(i_cat_id in number default 0) return json is
v_json json:=json();
v_arr_id NUMBER;
v_ele_id NUMBER;
begin
if i_cat_id = 0 then -- wenn Kategorie 0 dann wird das der Root-Knoten mit festen Werten
  v_json.add_member(p_name => 'id', p_value => i_cat_id);
  v_json.add_member(p_name => 'text', p_value => 'root');
else -- sonst regulärer Knoten; Werte für die Kategorie benutzen
  for cat in (select * from tbl_mon_category where cat_id=i_cat_id) loop
    v_json.add_member(p_name => 'id', p_value => i_cat_id);
    v_json.add_member(p_name => 'text', p_value => cat.cat_label);
  end loop;
end if;

if not hasChildren(i_cat_id) then -- wenn Knoten keine Child-Knoten hat
  v_json.add_member(p_name => 'leaf', p_value => true); -- als Blatt markieren und fertig
  v_json.add_member(p_name => 'href', p_value => 'f?p=108:2:'||v('APP_SESSION')); -- einen Link setzen
else -- sonst
  v_json.add_member(p_name => 'leaf', p_value => false); -- als Knoten mit Unterknoten kennzeichnen
  v_json.add_array(p_name => 'children', p_array_id => v_arr_id ); -- Array für Unterknoten hinzufügen
  for child in (select * from tbl_mon_category where cat_par_id=i_cat_id) loop -- Schleife über alle Unterkategorien
    -- Unterknoten über rekursiven Aufruf generieren und ins Array hängen
    v_json.add_array_element(p_array_id => v_arr_id, p_value => getJsonObject(i_cat_id => child.cat_id), p_element_id => v_ele_id);
  end loop;
end if;

return v_json;
end;

/*
-- Author  : AHILDEBRANDT
-- Created : 10.08.2009
-- Purpose : Aufruf der Funktion zum Generieren des JSON-Objekts und Rückgabe des Ergebnisses als String
*/
function getTreeDataDynamic return varchar2 is
v_json json:=json();
begin
v_json:=getJsonObject(i_cat_id => 0);
return(v_json.getString);
end;

end PCK_EXTJS_JSON_EXAMPLE;

Als nächstes benötigen wir einen bedarfsgesteuerten Anwendungsprozess "getTreeData", der die Package-Funktion aufruft.

declare

v_json varchar2(32767);

begin

v_json:=PCK_EXTJS_JSON_EXAMPLE.getTreeDataDynamic;

htp.prn(v_json);

end;

Als letzten Schritt muss nun noch die vorhandene Javascript-Funktion angepasst werden.

Ext.onReady(
function(){

var TreeRequest = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=getTreeData',0);
var TreeResponse = TreeRequest.get();
if (TreeResponse) {
  var v_extjs_tree_data= Ext.util.JSON.decode(TreeResponse);
}

  new Ext.Panel({
    title: 'My first tree',
    renderTo: 'tree',
    layout: 'border',
    width: 500,
    height: 500,
    items: [{
      xtype: 'treepanel',
      id: 'tree-panel',
      region: 'center',
      margins: '2 2 0 2',
      autoScroll: true,
      rootVisible: false,
      root: v_extjs_tree_data,
    }]
  });
}
);

Das Ergebnis sollte in etwa wie folgt aussehen.



Und das war’s auch schon….

Title: How to create an ExtJS-Tree...

You probably all now, that the apex trees aren't really sexy. Therefore i decided to give ExtJS a shot. On the left you can see a tree build with ExtJS. Nice, isn't it? This post shows how you can integrate an ExtJS tree like this in your apex application.

Let's start with a few words about ExtJS. ExtJS is a clientside javascript and ajax framework, that provides browser independent functionality for DOM-manipulation, event-handling or asynchronous server communication. The api is really easy to learn and to use, because it is completely based on JSON.

For the example in this post i created an application with one page. This page contains a region in which a div-region with id "tree" is defined.

The following stylesheets and javascript-files have to be included in the header of the page.

<link rel="stylesheet" type="text/css" href="#IMAGE_PREFIX#javascript/ext-2.2.1/resources/css/ext-all.css" />

<script type="text/javascript" src="#IMAGE_PREFIX#javascript/ext-2.2.1/adapter/ext/ext-base.js"></script>
<script type="text/javascript" src="#IMAGE_PREFIX#javascript/ext-2.2.1/ext-all.js"></script>

Now we put the following function in the page footer.

    Ext.onReady(function(){
    new Ext.Panel({
      title: 'My first tree',
      renderTo: 'tree',
      layout: 'border',
      width: 500,
      height: 500,
      items: [{
          xtype: 'treepanel',
          id: 'tree-panel',
          region: 'center',
          margins: '2 2 0 2',
          autoScroll: true,
          rootVisible: false,
          root: { text: "rootnode",
              expanded: true,
              children: [{ text:"node 1",
                           id:1,
                           leaf:true
                         },
                         { text: "node 2",
                           id: 2,
                           leaf:false,
                           expanded: true,
                           children:[{ text:"node 1",
                                         id:3,
                                       leaf:true
                                     }
                                    ]
                         }
                        ]
                },

      }]
  });
});

After the page is completely loaded the onReady-Function creates an ExtJS-Panel with the title "My first tree", which is rendered to our defined div-region.

Inside this panel the treepanel is created with an invisible root-node and two childnodes "node 1" and "node 2". "node 2" has another childnode.

The most important options to define a node are:

text: text to be displayed in the tree

id: id for the node, which should be unique in the tree

leaf: this option shows wether the node is a leaf or not

children: an array of childnodes if there are any

href: has to be set for the node to work as a link (line 68 next scriptblock)

Usually we need to load dynamic data from the database.

To do that, we will create a database function, that gives us a generated JSON-Object as a string.

After a bit of research i found that Lewis Cunningham already build a JSON-Datatype for Oracle. Thanks for that, because that makes our task really easy.

The following script creates a table, some test data and a package with needed functions.

create table tbl_category (cat_id number, cat_par_id number, cat_label varchar2(100));

insert into tbl_category (cat_id,cat_par_id,cat_label) values (1,0,'Node 1');
insert into tbl_category (cat_id,cat_par_id,cat_label) values (2,0,'Node 2');
insert into tbl_category (cat_id,cat_par_id,cat_label) values (3,1,'Childnode A for Node 1');
insert into tbl_category (cat_id,cat_par_id,cat_label) values (4,2,'Childnode A for Node 2');
insert into tbl_category (cat_id,cat_par_id,cat_label) values (5,2,'Childnode B for Node 2');
insert into tbl_category (cat_id,cat_par_id,cat_label) values (6,2,'Childnode C for Node 2');
commit;

create or replace package PCK_EXTJS_JSON_EXAMPLE is

  -- Author  : AHILDEBRANDT
  -- Created : 10.08.2009

  -- Purpose : check if category has sub-categories
  function hasChildren(i_cat_id in number) return boolean;

  -- Purpose : create JSON-Object for category;
  --           recursive<br/>  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 PCK_EXTJS_JSON_EXAMPLE;
/
create or replace package body PCK_EXTJS_JSON_EXAMPLE is
/*
  -- Author  : AHILDEBRANDT
  -- Created : 10.08.2009
  -- 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_category where cat_par_id=i_cat_id;
  if v_count>0 then
    return true;
  else
    return false;
  end if;  
end;

/*
  -- Author  : AHILDEBRANDT
  -- Created : 10.08.2009
  -- Purpose : create JSON-Object for category;
  --           recursive
*/
function getJsonObject(i_cat_id in number default 0) return json is<br/>  v_json json:=json();
  v_arr_id NUMBER;
  v_ele_id NUMBER;
begin
  if i_cat_id = 0 then -- if category is root then set root-node
    v_json.add_member(p_name => 'id', p_value => i_cat_id);
    v_json.add_member(p_name => 'text', p_value => 'root');
  else -- else --> usual node; use category infos
    for cat in (select * from tbl_category where cat_id=i_cat_id) loop
      v_json.add_member(p_name => 'id', p_value => i_cat_id);
      v_json.add_member(p_name => 'text', p_value => cat.cat_label);
    end loop;
  end if;

  if not hasChildren(i_cat_id) then -- if node has no children
    v_json.add_member(p_name => 'leaf', p_value => true); -- mark as leaf
    v_json.add_member(p_name => 'href', p_value => 'f?p=108:2:'||v('APP_SESSION')); -- set a link if needed
  else -- sonst
    v_json.add_member(p_name => 'leaf', p_value => false); -- mark as node with children
    v_json.add_array(p_name => 'children', p_array_id => v_arr_id ); -- add array for subcategories
    for child in (select * from tbl_category where cat_par_id=i_cat_id) loop -- loop through all sub-categories
      -- create JSON-object for sub-category using recursive call and the append to array
      v_json.add_array_element(p_array_id => v_arr_id, p_value => getJsonObject(i_cat_id => child.cat_id), p_element_id => v_ele_id);
    end loop;
  end if;

  return v_json;
end;

/*
  -- Author  : AHILDEBRANDT
  -- Created : 10.08.2009
  -- 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 => 0);
  return(v_json.getString);
end;

end PCK_EXTJS_JSON_EXAMPLE;
/

Next thing we need is an on-demand application process "getTreeData", that will call our package-function.

declare

v_json varchar2(32767);

begin

v_json:=PCK_EXTJS_JSON_EXAMPLE.getTreeDataDynamic;

htp.prn(v_json);

end;

Only thing left to do is changing our javascript-function...

Ext.onReady(
function(){

var TreeRequest = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=getTreeData',0);
var TreeResponse = TreeRequest.get();
if (TreeResponse) {
  var v_extjs_tree_data= Ext.util.JSON.decode(TreeResponse);
}

  new Ext.Panel({
    title: 'My first tree',
    renderTo: 'tree',
    layout: 'border',
    width: 500,
    height: 500,
    items: [{
      xtype: 'treepanel',
      id: 'tree-panel',
      region: 'center',
      margins: '2 2 0 2',
      autoScroll: true,
      rootVisible: false,
      root: v_extjs_tree_data,
    }]
  });
}
);

The result should look like this...



And that's it....