Weblog for the AMIS Technology corner
Posts tagged sql
Oracle 11gR2 – alternative for CONNECT_BY_ISLEAF function for Recursive Subquery Factoring (dedicated to Anton)
Nov 14th
On our blog, we have been discussing the new hierarchical query functionality in Oracle Database 11g Release 2, using Recursive Suquery Factoring. Instead of using CONNECT BY and its close associates such as START WITH, PRIOR, LEVEL and more exotic comrades like SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT and NOCYCLE this release gave us a new, less proprietary and eventually probably more intuitive and functionally rich approach. We have also written how though we have no straightforward alternatives for LEVEL, SYS_CONNECT_BY_PATH and CONNECT_BY_ROOT – in the new recursive approach they are fantastically easy to emulate.
Until recently I have been quite happy with the new hierarchical querying and telling the world how I felt. Then an esteemed colleague – a far more experienced SQL programmer than I am – came up to me and remined me how the recursive sub query syntax at the present does not have a replacement for the CONNECT_BY_ISLEAF function – the SQL function that tells us whether a node produced in an hierarchical query has any children or is at the bottom of the chain – i.e. a leaf node. For leaf nodes (child-less), the function returns a value of one and for parent nodes the value is zero.
Anton (my colleague) was right and unfortunately I did not have a quick retort. However, after giving it some thought I believe I have found a way of emulating the CONNECT_BY_ISLEAF as well, using the new DEPTH FIRST ordering capabilities of the recursive subquery. I hope this will satisfy Anton as well.
SOA & SOA Suite for Oracle Database Professionals – seminars in Perth and Melbourne and Singapore (November 2009)
Oct 20th
Next month, I will visit Australia and Singapore to present on SOA and the Oracle SOA Suite – to Oracle database developers. In this one-day-long seminar, I introduce the key concepts and objectives of SOA (Service Oriented Architecture) as well as the Oracle SOA Suite 11g to an audience of database professionals. Whether you are a DBA or a Database Developer, SOA is unavoidable in the coming period. But what (exactly) is it? And how does it impact – positively or negatively – the work and lives of database professionals? What can a database professional do to work well with SOA and the SOA technology once that starts being implemented in her or his organization?
What is at the heart of Oracle SOA Suite 11g: composite applications, BPEL PM, and the mediator. The session shows how SOA services can be leveraged from the database, from triggers, PL/SQL units, or even SQL and how the database can publish events to the event delivery network. It covers how the SOA infrastructure can access the database, primarily using Oracle Database and Oracle Advanced Queueing adapter and how database developers can help in doing so efficiently. It ends with hints for applying SOA concepts to "normal" database development.
The seminar has a lot of acronyms (to ensure you can converse with the architects) and even more demonstrations, both in SQL*Plus, the browser as well as the SOA Suite 11g design time and run time. You will see in a very practical manner what this talk of Services really is all about. What you can do with services, what the SOA guys will do with your database and how you can improve your database design and PL/SQL code using the same principles that guide SOA design.
We will discuss database features such as packages and views, Advanced Queues, Native Database WebServices, dbms_epg and XML DB and hook the database into the middle tier. After this session, you will no longer produce a blank stare when SOA is discussed – au contraire, you will be able to participate in the discussions, the design and planning and the implementation of SOA initiatives.
Details
Perth – Tuesday 10th November, Melbourne – Monday 16th November (as part of the AUSOUG with 20:20 Foresight National Conference series). See for details on date, time, venue and registration for Perth and Melbourne: http://www.ausoug.org.au/2020/.
The seminar in Singapore is on Friday 20th November (part of the Oracle University Celebrity Specials). Details on the Oracle University Celebrity Specials in Singapore: http://www.oracle.com/education/apac/sg_lucas.html.
Oracle RDBMS 11gR2 – new style hierarchical querying using Recursive Subquery Factoring
Sep 29th
Oracle Database 11g Release 2 introduces the successor to the good old Connect By based hierarchical querying, called Recursive Subquery Factoring. The basics are described in a previous article: http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it. This article will show some additional examples of using this recursive subquery factoring syntax.
The essence of this recursiveness: the subquery consists of two queries unioned together. The first query returns the root-nodes, the starting points in the tree or network. The second query is used to continually retrieve the next step or level: it refers to the subquery itself and finds the next node level based on the levels (root and zero or more previously retrieved levels) already retrieved by the subquery.
In code:
Oracle Database 11gR2 – New analytical function NTH_VALUE
Sep 15th
You are probably familiar with the FIRST_VALUE and LAST_VALUE analytical functions that were introduced some time ago into the Oracle RDBMS, in the 9iR2 release I believe (or at least that is when they made their way into the Standard Edition). These values are used to find the first respectively last value in a window in a partition that has been ordered in a certain way.
Oracle introduces a new, related function in 11gR2, called NTH_VALUE. Instead of simply the first or last value in an analytical window, we can ask for a specific row number, such as 2nd row (ND_VALUE?), the 7th or the 223th. This article shows the syntax for this new function.
Oracle SQL and PL/SQL: juggling with user defined types (ADT) and XMLType – for creating an ADT based XML Service API
Sep 4th
Packages in the Oracle Database are a fine construct to use for creating a service API at PL/SQL level – that through JDBC or other connections into the database can quickly be exposed at other levels than just internally for PL/SQL. A service API has a number of characteristics, that typically include a structured, well documented contract – definition for the data that goes in and comes out of the service -, complex, nested message structures and the ability to communicate in terms of XML.
Using ADTs or user defined types in the Oracle RDBMS is quite often very useful. Presenting such a service-style interface from PL/SQL packages is quite easily realized using custom type definitions – easier both for the definition of the package’s "service contract", the consumer of the package (at least the Database Adapter used in the SOA Suite) and the developers implementing the service contract. Such a contract in terms of nested types quite close resembles the typicall WebService contract that used nested XML documents.
So the first layer of service API can be one in terms of user defined types. However, many potential consumers of the service do not speak ‘Oracle type lingo’. They can deal with strings and numbers and a string may contain a complex block of data when it is constructed as XML, but Oracle Types are beyond them. So we may need a second layer on top of the user defined type service API – a second layer that speaks XML.
Fortunately it is rather simple to turn the XML input to this second layer and convert it to Oracle Object speak (user defined types) and also to take the result from the innner API that is in terms of the user defined types and turn it into [an] XML [message]. The mechanisms we can use for the conversion from UDT (or ADT) to XML and vice versa are XMLType, SYS_XMLGEN and the TOOBJECT operation on XMLType.
Oracle RDBMS 11gR2 – goodbye Connect By or: the end of hierarchical querying as we know it
Sep 1st
Many years ago, Oracle basically set the standard in SQL. Whatever was Oracle SQL could be seen as the standard. This has never been absolutely true – ANSI SQL was different from Oracle SQL. In some respects the differences can be traced back to lack of functional richness in the standard. However, in certain areas, Oracle has walked its own path with certain functions and features with the rest of the RDBMS pack following another route. It seems to be as if starting with Oracle RDBMS 9iR2, Oracle has made several important steps towards embracing the ANSI SQL standard syntax, usually while maintaining its own specific flavor of those same functions.
Some examples of Oracle specific syntax that are also available through their ANSI SQL counterpart – that in most instances has even more functionality – and that is almost always the preferred approach going forward:
- outer join syntax: in addition to Oracle’s (+) notation, 9iR2 introduced the left outer join, right outer join and full outer join (like (+) on both ends of the join condition, something that Oracle does not support)
- the Decode function that with 9iR2 can (and should) be replace by the CASE expression
- the to_char function that in many instances can be replace by extract {year|month|day|HOUR | MINUTE | SECOND} from date-value
- the ltrim and rtrim functions that can be replaced by the trim function with leading and trailing settings
The 11gR2 release has another example of Oracle specific SQL that is now complemented with the ANSI SQL standard approach:the CONNECT BY syntax for hierarchical querying that can now be replaced by the ANSI SQL standard Recursive Subquery Factoring clause, which is an extension to the WITH clause introduced as long ago as Oracle7 and still relatively underused by Oracle SQL developers.
Reaching Milestones – some Analytical SQL-etics
Jun 28th
Last week, this weblog hit the 9M post reads mark. Since we started technology.amis.nl/blog in July 2004, it took us some 5 years to get to that point. And I started to wonder when we reached earlier milestones, like 100k, 1M and 5M. This took me to some SQL puzzles that before I started in anger might have been difficult to solve, but turned out to be rather straightforward – thanks to Analytical functions.
In this article a brief report on how to reconstruct history using Running Sums and a bit of LAG/LEAD analysis.
Seminar: SOA for Database Professionals (April 3rd, The Netherlands)
Mar 19th
On Friday April 3rd, I will be presenting a seminar titled "SOA for Database Professionals" – in De Meern, The Netherlands. For more details and registration, go to this link. This seminar explains the concepts of SOA and their relevance to Oracle Database professionals – both DBA and Developer – as well as main benefits database professionals can derive from SOA concepts and SOA technology. The seminar will host a lot of demos – introducing special SOA, XML and service related functions in the Oracle Database as well as the key components in the Oracle SOA Suite.
The main objectives are:
- Understand what the relationship between SOA and the Oracle Database is or can be.
- See what BPEL can do for you and also what you can do for BPEL.
- Start to regard SOA (BPEL & ESB) as an opportunity to benefit from rather than a threat.
- Get started in working with WebServices BPEL and ESB (where to begin).
Tag Cloud analysis in SQL and PL/SQL – Using Collections, Table Operator, Multiset and Collect operator
Mar 6th
Another world shattering topic: analyzing tag clouds. This article describes a very simple first step for some analysis at database level of tags that have been associated with database records this analysis can be the foundation for presenting the tag cloud in the user interface that enables the user to quickly filter on records or list ’similar’ records. We will use some valuable SQL and PL/SQL facilities: Collections, the Table operator and the Collect aggregator. Read the rest of this entry »
