News, views, tips and tricks on Oracle and other fun stuff

The Power of SQL

Frank Zhou recently posted these entries on

Frank combines many techniques in his queries to solve problems that many of us think they can only be solved procedurally, using PL/SQL for example.

Some of the techniques/features used in his queries:

These queries are examples of the power of SQL and a demonstration of Tom’s rule:

a) if you can do it in SQL – DO IT, period.
b) if you cannot do it in SQL (and you probably can) do it in as little PL/SQL as possible.

Comments Off on The Power of SQL | Filed in Oracle | Tags:

How To Manually Install XML DB On Oracle 11g


Due to Oracle Bug 9818995, if catqm.sql is used then not all installation steps are performed by this script… This document explains the full set of steps that need to be taken in order to perform a complete and working Oracle XML DB installation.
Comments Off on How To Manually Install XML DB On Oracle 11g | Filed in Links, Oracle, Tips | Tags: ,

XML Goodness in SQL – Part 2

In part 1, we reviewed HTTPURIType and DBURIType. Let’s now go over a few examples of more advanced XML using the SQL functions XMLTable and XMLQuery, Oracle’s native support for XQuery.

SQL functions XMLQuery and XMLTable are defined by the SQL/XML standard as a general interface between the SQL and XQuery languages. XMLQuery and XMLTable let you take advantage of the power and flexibility of both SQL and XML. Using these functions, you can construct XML data using relational data, query relational data as if it were XML, and construct relational data from XML data.

Let’s query blog titles from the RSS feed without using XQuery:

SQL> SELECT value(i).extract('/item/title/text()'
  2         ,'xmlns:dc=""' ).getstringval() title
  3  FROM   TABLE (XMLSequence(
  4         HTTPURITYPE('').getxml().extract('//item'))) i;

Oracle ANZ Blogosphere Highlights
Taking the Plunge: Part 2
8 Things about Louise
Working with PHP and Oracle Presentation
Removing Oracle Database XE from Linux

Starting with Oracle database 10gR2 you have the option to use the XQuery SQL function XMLTable. You use XMLTable to shred the result of an XQuery-expression evaluation into the relational rows and columns of a new, virtual table. You can then insert the virtual table into a pre-existing database table, or you can query it using SQL. You use XMLTable in a SQL FROM clause.

Let’s change the query above to use XMLTable and return the blog author as well:

  2      FROM XMLTable (
  3        '//item'
  4        passing HTTPURITYPE('').getXML()
  5        COLUMNS title   varchar2(4000) path '/item/title/text()',
  6                creator varchar2(4000) path
  7                        '/item/*[namespace-uri()=""
  8                                and local-name()="creator"]/text()'
  9     );

TITLE                                    CREATOR
---------------------------------------- --------------------
Oracle ANZ Blogosphere Highlights        Carl Terrantroy
Taking the Plunge: Part 2                Jake
8 Things about Louise                    Louise Barnfield
Working with PHP and Oracle Presentation alison.holloway
Removing Oracle Database XE from Linux   alison.holloway

And here is another example that selects the entries from the Oracle room on FriendFeed:

  2    FROM XMLTable (
  3        '/feed/entry'
  4        passing HTTPURIType ('').getXML()
  5        columns title varchar2(4000) path 'title/text()'
  6   );

Oracle ECM and Skywire Database modeling support in SQL Developer
Oracle Fusion Middleware Strategy Webcast Replay

You can also use the XQuery function ora:view within an XQuery expression to query a relational table or view as if it were XML. Here is a simple example using the table we created in part 1:

  2    FROM XMLTable(
  3           'for $i in ora:view("t")
  4                   return $i');



The other XQuery function is XMLQuery. You use it to construct or query XML data. This function takes as arguments an XQuery expression, and an optional XQuery context item. The context item establishes the XPath context in which the XQuery expression is evaluated. Additionally, XMLQuery accepts as arguments any number of SQL expressions whose values are bound to XQuery variables during the XQuery expression evaluation. The function returns the result of evaluating the XQuery expression, as an XMLType instance.

Here are a couple of simple examples:

SQL> SELECT XMLQuery('(100 to 105)'
  2                  RETURNING CONTENT) AS output
  3    FROM DUAL;


100 101 102 103 104 105

SQL> SELECT XMLQuery('for $i in ora:view("t")
  2                   return <ROWS>{$i}</ROWS>'
  3                  RETURNING CONTENT) AS col
  4    FROM DUAL;


The above examples give you a “taste” of the capabilities of XQuery in Oracle. For more detailed information please refer to the following list of resources:

1 Comment | Filed in Oracle | Tags: , ,

XML Goodness in SQL – Part 1

Here are some cool and useful things you can do with XML using pure SQL in an Oracle database.


The HTTPURIType provides support for the HTTP protocol. It uses the UTL_HTTP package underneath to access the HTTP URLs. With HTTPURIType, you can create objects that represent links to remote Web pages (or files) and retrieve those Web pages by calling HTTPURIType member methods.

The getXML() member function returns the XMLType located at the address specified by the URL. An error is thrown if the address does not point to a valid XML document.

SQL>  SELECT HTTPURITYPE('').getxml() orana_feed
  2     FROM dual;


<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet href="http://feeds.feed

The getClob() member function returns the CLOB located by the HTTP URL address.

SQL>  SELECT HTTPURITYPE('').getClob() goog_html
  2     FROM dual;


<html><head><meta http-equiv="content-type" content="text/html; charset=ISO-8859


The DBURIType provides support for DBUri-refs. A DBUri-ref is an intra-database URL that can be used to reference any row or row-column data in the database. With DBURIType, you can create objects that represent links to database data, and retrieve such data as XML by calling DBURIType member methods.


Table created.

SQL> INSERT INTO  t (a,b) VALUES (1, 'Eddie');

1 row created.

SQL> INSERT INTO  t (a,b) VALUES (2, 'John');

1 row created.

SQL> INSERT INTO  t (a,b) VALUES (3, 'Pat');

1 row created.

SQL> commit;

Commit complete.

The member function getXML() returns the XMLType located at the address specified by the URL.

SQL> SELECT DBURIType('/SCOTT/T').getxml()
  2  from dual;


<?xml version="1.0"?>

The above was tested on Oracle Database 10g Enterprise Edition Release

In part 2 we’ll explore more XML goodness…

Sources and resources

2 Comments | Filed in Oracle | Tags: ,

An Easy Way to Convert a Hierarchical Query Result to XML

Consider the following classic employees table:

SQL> SELECT employee_id,
  2    manager_id,
  3    first_name,
  4    salary,
  5    hire_date
  6  FROM employees;

----------- ---------- -------------------- ---------- ---------
        100            Steven                    24000 17-JUN-87
        101        100 Neena                     17000 21-SEP-89
        102        100 Lex                       17000 13-JAN-93
        103        102 Alexander                  9000 03-JAN-90
        104        103 Bruce                      6000 21-MAY-91
        105        103 David                      4800 25-JUN-97
        106        103 Valli                      4800 05-FEB-98
        107        103 Diana                      4200 07-FEB-99
        108        101 Nancy                     12000 17-AUG-94
        109        108 Daniel                     9000 16-AUG-94
        110        108 John                       8200 28-SEP-97

The manager_id is also an employee_id. So, we can build the following hierarchical query:

Continue reading…

7 Comments | Filed in Oracle, Tips | Tags:

Producing XML from SQL using cursor expressions

In this post I will show an example of how you can transform a query result into an XML document. I will also show how you can write a query that produces nested or multi-leveled XML document using cursor expressions. Continue reading…

6 Comments | Filed in Oracle, Tips | Tags: , ,

HTTP POST from inside Oracle

A while ago Robert and Pete blogged about Oracle’s UTL_HTTP package. Robert gave an example of how to use this package to request a web page from the Internet into Oracle and Pete mentioned that you could do the opposite, i.e. post data from Oracle to the Internet. Recently I had a requirement to do just that, HTTP post data from the Oracle database to another web server. So, I wrote the following generic procedure util_pkg.http_post. Here it is followed by an example of how to use it (tested on 9.2): Continue reading…

23 Comments | Filed in ColdFusion, Oracle | Tags: , , ,

XML configuration files used in ColdFusion MX

If you ever wondered what the purpose and location of the ColdFusion XML configuration files are, you have to read this ColdFusion TechNote. It describes the J2EE-standard XML files used by ColdFusion MX, the ColdFusion MX-specific XML server configuration files, and the XML files used to store ColdFusion MX Administrator settings. Very informative and useful.

Comments Off on XML configuration files used in ColdFusion MX | Filed in ColdFusion | Tags: ,

Read XML from an HTTP POST

First, let’s see how you can post XML over HTTP to another page. This is very simple in ColdFusion:

<cfhttp url="url_to_post_to" method="post">

Now, suppose you are posting the XML to a ColdFusion page. How would you read the XML from the HTTP POST body? Again, this is very simple in ColdFusion:

<cfset docContent = GetHTTPRequestData().content>
<cfset myDOM = XmlParse(docContent)>

and now you can manipulate the XML DOM, stored in myDOM, in any way you like.

<cfhttp> documentation
GetHTTPRequestData documentation
ColdFusion XML documentation

Comments Off on Read XML from an HTTP POST | Filed in ColdFusion, Tips | Tags: ,