Kscope 2015 - ODTUG Oracle conference

Still not to late to apply

Hollywood, Florida US

You probably we all aware that this years Kscope conference will be taking place in late June 2015. Great news now is that I have confirmed my reservation so looking forward to it already.

It has been a while since I wanted to attend one of these but it was never the right timing. This year (after winning a jack pot..... yeah right!) I finally decided to organize myself to be there.

Just by looking at the list of people presenting on APEX subject I must say I am impressed. 

Check it out here: APEX presentations

They do say it is a biggest Oracle developer event of the year so it will be interesting to see where other people are what is the coolest thing at the moment, check out few of tips and tricks of APEX leading developers etc. 

I may even go that far that I might set my self a new 'yearly thing to do' - be one day a presenter at one of these events. You never know only time will tell.

All good I'll keep you updated and hope to have some new posts coming up.

Hope to see you there,

SLino

XDB username and password required

XDB and ACL configuration issue in Oracle APEX 4.x

Apex 4.2 (PL/SQL gateway) and Oracle 11g

Just a quick post about a problem that we encountered the other day for who knows what time and that is why I decided to blog about it. Maybe it will save us some time next time we/you encounter the same problem.

Story goes like this.....

On UAT server where APEX installation was done months ago users all of the sudden started getting XDB authentication required message.

  

Since these do not come very often it caught us by surprise. 

These are few crucial Cookbook-steps you need to do to check if your ACL permissions are configured properly. 

Key thing here, of course is to find out why this started to happen? 

We managed to track this down to few external files that were added to newly created application referencing some CSS and JavaScript files which other application on the same server did not do.  Okay but why would this now be a problem?

Well simple theory behind this is that APEX images/files are stored within XMLDB, in order to access the XMLDB resources the appropriate ACL (Access Control List) assigned to the images directory need to have anonymous read-contents access otherwise (you guessed it) you are prompted for a username and password.

Please note that after seeing so many posts about the same problem this may help but again depending on your situation it may not. 

Advice would be:

• check that your ANONYMOUS account is not locked - how to do this

select account_status from dba_users where username='ANONYMOUS';

ANONYMOUS is an Oracle user account specifically designed for HTTP access. It has only one system privilege, that is “create session” and the account is locked by default. If it is unlocked it can access objects in the XDB Repository that are protected by an ACL (Access Control Lists) mentioning this rule.  

When APEX is installed then there should be a /sys/acls/ro_anonymous_acl.xml file that grants read access to the /images/ or /i/ directory (depending on the APEX version). Example of such file

<acl description="File /sys/acl/rr_acl.xml"
     xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
     xmlns:dav="DAV:"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
                         http://xmlns.oracle.com/xdb/acl.xsd">
  <ace>
    <principal>ANONYMOUS</principal>
    <grant>true</grant>
    <privilege>
      <read-properties/>
      <read-contents/>
      <resolve />
    </privilege>
  </ace>
</acl>  

If you lock ANONYMOUS or remove the ACL defined privileges then APEX can not show/access these files in XDB Repository folder (/images or /i).

•   if it is you have to unlock it with

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

•  check that your XDB user is not locked

select account_status from dba_users where username='XDB';

• again similar to step 2 if XDB user is locked you have to unlock it with

ALTER USER XDB ACCOUNT UNLOCK;

• next check ACL configuration file first that it contains <read- > line for both properties and contest similar as shown in yellow above

  select xdburitype('/sys/acls/ro_anonymous_acl.xml').getclob() from dual

• if it doesn't add these lines to the ACL config file and give it a go. Note here we had a file containing <read-content> but without a <read-properties> and everything else was fine but things were still not working so it is worth checking before taking next step

• then as last thing check XDB configuration access by running 

DECLARE
    l_configxml XMLTYPE;
    l_value VARCHAR2(5) := 'true';
BEGIN
    l_configxml := DBMS_XDB.cfg_get();
    IF l_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
       DBMS_OUTPUT.put_line('Config Element missing');
    ELSE
      DBMS_OUTPUT.put_line('Config Element exists but may need updating');
    END IF;
END; 

• this will give you an idea where other problem might be. In both cases you can run the following script

SET SERVEROUTPUT ON;

DECLARE
    l_configxml XMLTYPE;
    l_value VARCHAR2(5) := 'true';
BEGIN
    l_configxml := DBMS_XDB.cfg_get();
    IF l_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
        – Add config element
        SELECT insertChildXML
                    (l_configxml,
                    '/xdbconfig/sysconfig/protocolconfig/httpconfig,
                    allow-repository-anonymous-access',
                    XMLType('' ||
                    l_value ||
                    ''),
                    'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
                    )
        INTO l_configxml
        FROM dual;
       
        DBMS_OUTPUT.put_line(‘xdbconfig for anonymous now inserted.');
    ELSE
        – Update existing config element.
        SELECT updateXML
                    (DBMS_XDB.cfg_get(),
                    '/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()',
                    l_value,
                    'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
                    )
            INTO l_configxml
            FROM dual;
        DBMS_OUTPUT.put_line(‘xdbconfig for anonymous now updated.');
    END IF;
   
    DBMS_XDB.cfg_update(l_configxml);
    DBMS_XDB.cfg_refresh;
END;

What script does is it checks for configuration if it does not exist it creates one else it updates existing one. Hopefully by now your popup window should be long gone. 
On top of this the only thing that might be needed is resetting passwords for these two accounts.

ALTER USER ANONYMOUS IDENTIFIED BY anonymous;

ALTER USER XDB IDENTIFIED BY xdb;

And for the reference if none of the above helped some of these queries might be helpful.

SELECT XMLSerialize(DOCUMENT DBMS_XDB.getACLDocument('/images') AS CLOB) FROM DUAL;
SELECT DBMS_XDB.cfg_get() FROM DUAL;
select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;

Beautiful article with all very useful details about ACL and XDB was posted by Marco.

Last note is that if you are using APEX listener for your APEX application there is no need for any of these. Lucky you :)

Well thanks for reading hope it helped.

Cheers,
SLino

Trees, Hierarchical queries in PL/SQL

My intro to simple hierarchical/recursive queries

I am sure we all come across a problem like I did the other day having to implement code based on tree like tables. Structure as simple as:

SELECT project_id,
       project_code,
       project_parent_id

FROM R_PROJECTS

Idea here is to note a few queries I used that proven helpful so far. 

PL/SQL functions that are in play are START WITH, CONNECT_BY, CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH. 

By no means will I claim that I am guru of these features but with a bit of play we all come to something useful at the end and this is the whole point why reinvent when it is already out there.

Of course I could have opted for numerous loops and classic approach but I said now that I do have some time to get into it lets have a look is there an easier way to get in and around the tree structure queries.

Of course ultimate problem is getting all grandparents and their grandchildren across multiple levels with the least acrobatics possible. 

First what is self explanatory is getting all members and their parents plus showing a level they currently are on. 

SELECT project_id,
       project_code,
       project_parent_id,
       level t_level
FROM   R_PROJECTS 
START WITH project_parent_id IS NULL
CONNECT BY PRIOR project_id = project_parent_id;

What we get as result is simply all records from a table "joined" with their parent records plus showing a level of child record. So far so good. 

To get hold of maximum level

SELECT MAX(LEVEL)
FROM PROJECT_HIERARCHY_V
START WITH project_parent_id is null
CONNECT BY PRIOR project_id = project_parent_id;

 For the purpose of easier reading I created a view 

PROJECT_HIERARCHY_V as
SELECT project_id,
       project_code,
       project_parent_id,
       level t_level
FROM   R_PROJECTS 
START WITH project_parent_id IS NULL
CONNECT BY PRIOR project_id = project_parent_id;

For example to get to the number of children each node has counting in all its grandchildren we can use the query above. 

Then our query would be something along

select project_id, ( count(*) -1 ) has_Child_Count
from ( select connect_by_root(project_id) project_id
       from   PROJECT_HIERARCHY_V
       connect by project_parent_id = prior project_id
     )
group by project_id

Keeping in mind that query contains a parents/grandparents records as well hence Count(*) - 1. CONNECTED_BY_ROOT gives this wonderful ability to keep track of your parents info.

Another great and useful example could be to get path to a child for given project_id:

select substr ( SYS_CONNECT_BY_PATH ( project_id, ' <- ' ), 5 ) req_path
 from PROJECT_HIERARCHY_V
 where project_id = 179039 and connect_by_root ( project_parent_id ) is null
 connect by prior project_id = project_parent_id

As Oracle documentation says SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition. Resulting in something like 

410125 <- 360147 <- 179039

Now getting to the core of my initial problem. Some of above queries I used to get all available children under a certain parent ID:

select project_id,
         --root con_over_project,
         IS_PARENT_IND
 from
        (SELECT project_id
              ,CONNECT_BY_ROOT project_id ROOT,                       
              -- simple function that queries table to check if project_paretn_id is null or not                                        CHECK_IS_PARENT_PROJECT(project_id) IS_PARENT_IND
        FROM PROJECT_HIERARCHY_V
        START WITH project_id in (                                   --this query retuns all projects and their leafs of given project_id which is used in another recursive query to get all nodes of these
                 SELECT project_id
                  FROM PROJECT_HIERARCHY_V
                  START WITH project_id = YOUR_PROJECT_ID
                   CONNECT BY PRIOR project_id = project_parent_id)
                CONNECT BY PRIOR project_id = project_parent_id) tree
      /* -- filters only ones we need to calculate figures for excluding Grand parents/parents. By removing a comment you get only leafs that are children
  where IS_PARENT_IND = 'N'
   and project_id != root*/
   group by project_id,
         --root,
           IS_PARENT_IND

A bit more complex would be to get sums of each individual child rolled up to a parent. Similarly if you wanted to count how many children/ grandchildren there are under a certain root in more technical way than shown previously. I noticed this one online and seems to do the trick:

-- for the count of subprojects

select project_id , tot_children, project_parent_id
from (select
           project_id,
           level lev,
           project_parent_id,
           count(project_id) over(partition by connect_by_root (project_id)
                           order by level desc
                           rows between unbounded preceding
                                and     1 preceding
                               ) tot_children
      from PROJECT_HIERARCHY_V
    connect by prior project_id = project_parent_id)
    where lev=1;

Of course key being the OVER( PARTIOTION BY ....) part where by ordering them we actually get rid of each parent record so results only contains children leafs.

Excellent article and more fine details how some of mentioned functions work were found and inspired by Lucas's blog

Well this was all for now and back to work for me.

Cheers,
SLino

First 2015 post

APEX 5.0 EA 3 overview

General impressions and things to look forward to

Hi all it is good to be back after 2 months of holidays. This year we've taken a bigger break to fill in the batteries for the rest of the year. And as you can see I am straight back into APEX and writing my first post of the year. that is why I will try to keep it short and concise.

Ever since Oracle announced EA 3 is here I applied for a new workspace and wanted to see what new have guys prepared for us this time. To tell you the truth the more I play with it and the more I spend time in new APEX 5 version I love it more and more. 

Page designer - I believe this will be the biggest and the most well accepted change compared to APEX 4.x version. It is almost impossible to foresee all benefits of having such a modern and compact development environment to work in. Definitely the biggest gain so far and once used to the new UI people will love it for sure. Things are well organized you can easily find components and properties, considering it is a web based UI I am truly impressed with work done on it. 

Page Universal theme - Again a big plus and enhancements in UI comparing to 4.x theme versions. Interface seems light, even with few minor places for improvement look and feel is really good, responsiveness of component is great too.

Other things - From first time of running Sample Application you notices changes on all levels. Modular layouts are supported which now definitely looks more professional bringing APEX closer to other popular development tools such as Java and .Net.

More intuitive support for modal windows is now here allowing us to use the advantage of modal windows more than before. Great to see effort done in this space. 

More than one interactive report is now available on a single page. How long have we waited for this to happen? Every person at some stage had a request for this feature so it is nice to finally be able to see it included. From other APEX enthusiast I learned that this feature required some extensive changes of core APEX IR logic which at the end resulted in decommissioning and unsporting of gReport function used by many. Details on this I will try to address in one of my future posts.

  I will mention here my only noticed hick up - when wanting to filter values in IR report by selecting appropriate column I didn't like final look and feel of the menu that comes up. Have a look at this example: 

 By clicking on Customer first name this was the result. 

Even though things look promising I sort of felt it didn't quite deliver. As menu shown seems clumsy and doesn't feel like it fits in well with the rest of the UI which is now really nice. Maybe this can be further improved for final release.

Comparing to Action menu which really deserves every recommendation. 

To finish my post I will also mention that is great to see support for Menu list items which bring a lot for overall impression which for me personally really is good. One can really feel that with version 5.x APEX will make another huge step toward being even more accepted among developer communities and new clients.

APEX 5.x - Impressive, promising and exciting.

Cheers, 

SLino