Author: ilmarkerm
- Written by: ilmarkerm
- Category: Blog entry
- Published: March 11, 2010
An excellent joint conference for Estonian, Finnish, Latvian and Russian Oracle User Groups in Tallinn, 20. – 21. May 2010.
Speakers also include Tom Kyte, Tanel Põder, Chris J. Date and Steven Feuerstein.
Read the agenda here and register in OUGF home page (250€+VAT registration fee).
In addition, just before the conference, 17.-18. May, Chris Date will perform his “How to Write Correct SQL and Know It: A Relational Approach to SQL” seminar in Helsinki. More info here and more detailed information here.
- Written by: ilmarkerm
- Category: Blog entry
- Published: January 20, 2010
In this part I’ll look at some features of Oracle Database for getting the data out in XML format, with SQL code only. In part 1 I looked at some features for reading/parsing XML with pure SQL.
Populating the tables for examples
Loading the departments into table DEPARTMENTS.
CREATE TABLE departments AS
SELECT dep.*
FROM xml,
XMLTABLE ('/company/employees/item[1]/departments/item'
PASSING x
COLUMNS id FOR ORDINALITY,
name VARCHAR2 (100 CHAR) PATH 'name') dep;
Employee data to table EMPLOYEES.
CREATE TABLE employees AS
SELECT emp.id,
emp.first_name,
emp.last_name,
emp.country,
dep.id active_department_id
FROM xml,
departments dep,
XMLTABLE (
'/company/employees/item'
PASSING xml.x
COLUMNS id NUMBER PATH '@id',
first_name VARCHAR2 (10 CHAR) PATH 'first_name',
last_name VARCHAR2 (10 CHAR) PATH 'last_name',
country VARCHAR2 (10 CHAR) PATH 'country',
active_department VARCHAR2 (20 CHAR) PATH 'departments/item[@active="true"]/name') emp
WHERE dep.name = emp.active_department;
Automatic XML generation + XML Stylesheet
Oracle provides functions sys_xmlgen and sys_xmlagg to automatically generate XML document based on table data. sys_xmlgen adds XML tags around a single value (returns XMLType data type from a single value) and sys_xmlagg aggregates/encloses rows into one XML document. Both of these functions also accept XMLFormat formatting parameter, that can be used to override the default enclosing XML tag name.
SELECT SYS_XMLAGG (x, XMLFormat (enclTag => 'COMPANY')) company_xml
FROM (SELECT SYS_XMLAGG (emp_xml, XMLFormat (enclTag => 'EMPLOYEES')) x
FROM ( SELECT tmp_id,
SYS_XMLAGG (xml_col, XMLFormat (enclTag => 'ITEM')) emp_xml
FROM (SELECT *
FROM (SELECT e.id tmp_id,
SYS_XMLGEN (e.id) id,
SYS_XMLGEN (e.first_name) first_name,
SYS_XMLGEN (e.last_name) last_name,
SYS_XMLGEN (e.country) country,
SYS_XMLGEN (d.name, XMLFormat (enclTag => 'ACTIVE_DEPARTMENT')) active_department
FROM employees e, departments d
WHERE e.active_department_id = d.id) UNPIVOT ((xml_col)
FOR tmp_col
IN (id, first_name, last_name,
country, active_department)))
GROUP BY tmp_id)
UNION ALL
SELECT SYS_XMLGEN ('Some Comany name', XMLFormat (enclTag => 'NAME')) x
FROM DUAL)
The query is quite long, but the idea behind it is pretty simple – generate a XMLType row for every data attribute and use SYS_XMLAGG to group and aggregate the generated XMLType rows into desirable XML format.
And the result is one XML document.
<?xml version="1.0"?>
<COMPANY>
<EMPLOYEES>
<ITEM>
<ID>1</ID>
<ACTIVE_DEPARTMENT>DBA</ACTIVE_DEPARTMENT>
<COUNTRY>Estonia</COUNTRY>
<LAST_NAME>Kerm</LAST_NAME>
<FIRST_NAME>Ilmar</FIRST_NAME>
</ITEM>
<ITEM>
<ID>2</ID>
<ACTIVE_DEPARTMENT>Development</ACTIVE_DEPARTMENT>
<COUNTRY>Estonia2</COUNTRY>
<LAST_NAME>Kerm2</LAST_NAME>
<FIRST_NAME>Ilmar2</FIRST_NAME>
</ITEM>
</EMPLOYEES>
<NAME>Some Comany name</NAME>
</COMPANY>
If the XML is still not at the correct format, then XSL Transformation can be applied to convert the output to another XML format.
XSL Transformation is an XML document, that describes the rules how to convert one XML document to another XML. For this example I wrote the following XSLT document:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="COMPANY">
<company>
<name><xsl:value-of select="NAME"/></name>
</company>
<employees>
<xsl:for-each select="EMPLOYEES/ITEM">
<item>
<xsl:attribute name="id">
<xsl:value-of select="ID" />
</xsl:attribute>
<first_name><xsl:value-of select="FIRST_NAME" /></first_name>
<last_name><xsl:value-of select="LAST_NAME" /></last_name>
<country><xsl:value-of select="COUNTRY" /></country>
<departments>
<item active="true">
<name><xsl:value-of select="ACTIVE_DEPARTMENT" /></name>
</item>
</departments>
</item>
</xsl:for-each>
</employees>
</xsl:template>
</xsl:stylesheet>
Save it to XML table named comp_xml_cslt.
CREATE TABLE comp_xml_xslt of xmltype;
INSERT INTO comp_xml_xslt VALUES ('<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
...
</xsl:stylesheet>
');
COMMIT;
To apply the XSL Transformation to source XML, Oracle has a function XMLTransform(source_xml, transformation_xml). Both arguments are XML documents of XMLType and this function returns transformed XML document.
WITH company AS
(SELECT SYS_XMLAGG (x, XMLFormat (enclTag => 'COMPANY')) xml
FROM (SELECT SYS_XMLAGG (
emp_xml,
XMLFormat (enclTag => 'EMPLOYEES'))
x
FROM ( SELECT tmp_id,
SYS_XMLAGG (
xml_col,
XMLFormat (enclTag => 'ITEM'))
emp_xml
FROM (SELECT *
FROM (SELECT e.id tmp_id,
SYS_XMLGEN (e.id) id,
SYS_XMLGEN (e.first_name) first_name,
SYS_XMLGEN (e.last_name) last_name,
SYS_XMLGEN (e.country) country,
SYS_XMLGEN (d.name,
XMLFormat (enclTag => 'ACTIVE_DEPARTMENT')) active_department
FROM employees e,
departments d
WHERE e.active_department_id =
d.id) UNPIVOT ((xml_col)
FOR tmp_col
IN (id, first_name, last_name, country, active_department)))
GROUP BY tmp_id)
UNION ALL
SELECT SYS_XMLGEN ('Some Comany name',
XMLFormat (enclTag => 'NAME'))
x
FROM DUAL))
SELECT XMLTRANSFORM (company.xml, comp_xml_xslt.object_value)
FROM company, comp_xml_xslt
In this query, COMPANY inline view is the same XML generating query as in the example before, but the XMLTRANSFORM function in main query is used apply the transformation. Result is below:
<company>
<name>Some Comany name</name>
</company>
<employees>
<item id="1">
<first_name>Ilmar</first_name>
<last_name>Kerm</last_name>
<country>Estonia</country>
<departments>
<item active="true">
<name>DBA</name>
</item>
</departments>
</item>
<item id="2">
<first_name>Ilmar2</first_name>
<last_name>Kerm2</last_name>
<country>Estonia2</country>
<departments>
<item active="true">
<name>Development</name>
</item>
</departments>
</item>
</employees>
XQuery
Oracle also implements the standard XQuery language, that can be used for querying XML data and Oracle has provided some XQuery views to access relational database tables.
SELECT XMLQuery(
'for $emp in ora:view("EMPLOYEES")/ROW
return <item id="{$emp/ID}">
{$emp/FIRST_NAME}
{$emp/LAST_NAME}
{$emp/COUNTRY}
<departments>
{for $dep in ora:view("DEPARTMENTS")/ROW
return <item active="{if ($dep/ID eq $emp/ACTIVE_DEPARTMENT_ID) then "true" else ("false")}">{$dep/NAME}</item>}
</departments>
</item>'
RETURNING CONTENT) x FROM DUAL
And the XML result is:
<item id="1"><FIRST_NAME>Ilmar</FIRST_NAME><LAST_NAME>Kerm</LAST_NAME><COUNTRY>Estonia</COUNTRY><departments><item active="false"><NAME>Development</NAME></item><item active="true"><NAME>DBA</NAME></item></departments></item><item id="2"><FIRST_NAME>Ilmar2</FIRST_NAME><LAST_NAME>Kerm2</LAST_NAME><COUNTRY>Estonia2</COUNTRY><departments><item active="true"><NAME>Development</NAME></item><item active="false"><NAME>DBA</NAME></item></departments></item>
- Written by: ilmarkerm
- Category: Blog entry
- Published: January 19, 2010
I have tried to read Oracle XMLDB Developer Guide and my first reaction was that parsing XML is very complicated in Oracle… And lately I got a task to rewrite some PL/SQL code to use a different SOAP service and the existing code had a few hundred rows just to parse XML (with XML DOM API)! Actually, starting from 10.2, this job is much easier…
Here I will go over some methods for working with XML, that are usable directly from SQL. In this first part, reading and extracting data from XML.
XML for the examples
<?xml version="1.0" encoding="utf-8"?>Some Company name Ilmar Kerm Estonia Development DBA Ilmar2 Kerm2 Estonia2 Development DBA
Load the XML data to a table, to special XMLTYPE data type.
create table xml (
x xmltype
);
insert into xml values (
xmltype.createxml('<?xml version="1.0" encoding="utf-8"?>
Some Company name
...
'));
COMMIT;
Extracting a single value
extractValue can be used to extract a single value from XML using XPath expression.
SQL> SELECT extractValue(x, '/company/name') FROM xml; EXTRACTVALUE(X,'/COMPANY/NAME') ------------------------------- Some Company name
extractValue only works with single values, otherwise exception will be raised.
SQL> SELECT extractValue(x, '/company/employees/item') FROM xml; SELECT extractValue(x, '/company/employees/item') FROM xml * ERROR at line 1: ORA-19025: EXTRACTVALUE returns value of only one node
Query XML as relational object
Oracle Database, since 10g, has a very easy way to map XML to a relational object and query it with SQL, so no PL/SQL code is needed for parsing XML – the XMLTABLE function.
SQL> SELECT emp.*
FROM xml,
XMLTABLE (
'/company/employees/item'
PASSING x
COLUMNS id NUMBER PATH '@id',
first_name VARCHAR2 (10 CHAR) PATH 'first_name',
last_name VARCHAR2 (10 CHAR) PATH 'last_name',
country VARCHAR2 (10 CHAR) PATH 'country',
active_department VARCHAR2 (20 CHAR) PATH 'departments/item[@active="true"]/name'
) emp;
ID FIRST_NAME LAST_NAME COUNTRY ACTIVE_DEPARTMENT
---------- ---------- ---------- ---------- --------------------
1 Ilmar Kerm Estonia DBA
2 Ilmar2 Kerm2 Estonia2 Development
XmlTable takes the following arguments:
| ‘/company/employees/item’ | XPath expression of the “row” |
| PASSING x | Source of XML data – XMLTYPE data type |
| COLUMNS id NUMBER PATH ‘@id’, first_name VARCHAR2 (10 CHAR) PATH ‘first_name’, last_name VARCHAR2 (10 CHAR) PATH ‘last_name’, country VARCHAR2 (10 CHAR) PATH ‘country’, active_department VARCHAR2 (20 CHAR) PATH ‘departments/item[@active=”true”]/name’ |
Column definitions with the corresponding Oracle data type and XPath expression |
After XML is readable as a relational table, all the power of Oracle SQL can be used for querying.
- Written by: ilmarkerm
- Category: Blog entry
- Published: November 6, 2009
I love database performance visualization tools and now Oracle has released small desktop widgets that give a quick overview of the targets in OEM Grid Control.
The one I really like is High-Load Databases widget. That shows a quick overview of how the top databases are performing at the moment – total number of average active sessions and a graph how this load is divided between the CPU/IO/Other wait classes.
With a click of a button, the screen changes to the new rectangular style performance graph, also showing the latest ADDM findings.
One feature request to Oracle – please add more lightweight skin and allow the user to configure how many databases the widget shows. I’d like to set that widget “stay on top”, but the view with TOP 5 databases takes too much screen space.
Download the widgets here: http://www.oracle.com/technology/products/oem/widgets/index.html
Blog for these widgets: http://blogs.oracle.com/emwidgets/
Actually, this is the first application I’d like to have running on my mobile device…
- Written by: ilmarkerm
- Category: Blog entry
- Published: October 27, 2009
I needed to develop one small application that communicates with external mobile devices, being like a central server where mobile clients send their data and receive configuration parameters. Reporting will be added later using Oracle APEX.
It’s a small application for a customer demo, so it sounded like a perfect opportunity to have my first look at the Oracle XE database 🙂
Oracle XE comes with a pre-configured XDB HTTP listener for APEX.
I don’t need the full APEX engine for my simple request server, just a PL/SQL package using PL/SQL Web Toolkit will do just fine and there is no need to use PHP or some other scripting engine outside the database.
Here are the steps I used to configure Oracle XE XDB HTTP listener with additional path (DAD) to my custom procedure.
Make HTTP listener to listen on all network interfaces, not only localhost:
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
Oracle XE has pre-defined DAD for APEX – /apex/
The following code will create a new DAD /mobileapp/, associate it with schema MOBILEAPP and authorize it to run without asking the user for a password.
BEGIN
DBMS_EPG.create_dad (
dad_name => 'mobileapp',
path => '/mobileapp/*');
DBMS_EPG.set_dad_attribute (
dad_name => 'mobileapp',
attr_name => 'default-page',
attr_value => 'home');
DBMS_EPG.set_dad_attribute (
dad_name => 'mobileapp',
attr_name => 'database-username',
attr_value => 'MOBILEAPP');
DBMS_EPG.set_dad_attribute (
dad_name => 'mobileapp',
attr_name => 'nls-language',
attr_value => 'american_america.al32utf8');
DBMS_EPG.authorize_dad (
dad_name => 'mobileapp',
user => 'MOBILEAPP');
END;
/
I used the following DAD attributes:
| default-page | specifies the default procedure name, that will be used when the procedure name is not specified in a HTTP URL |
|---|---|
| database-username | associated database schema name |
| nls-language | NLS client value, for a web application the important part is the charset. al32utf8 means the result page is in UTF-8. |
| DBMS_EPG.authorize_dad | This will disable the HTTP basic authentication |
There are other interesting DAD attributes and the current XDB HTTP configuration and APEX DAD configuration can be views using this SQL:
SQL> SELECT DBMS_XDB.CFG_GET().getClobVal() FROM DUAL;
...
APEX
PL/SQL
APEX
ANONYMOUS
apex
wwv_flow_file_objects$
docs
wwv_flow_file_mgr.process_download
american_america.al32utf8
wwv_flow_epg_include_modules.authorize
...
Now all MOBILEAPP PL/SQL procedures and packages can be accessed over HTTP and the HTTP URL has the following form:
http://oraclexeserver:8080/mobileapp/procedurename
or
http://oraclexeserver:8080/mobileapp/packagename.procedurename
So, a simple hello world procedure using PL/SQL Web Toolkit could look like this:
CREATE OR REPLACE PROCEDURE mobileapp.home IS
BEGIN
HTP.htmlopen;
HTP.headopen;
HTP.title('Page title');
HTP.headclose;
HTP.bodyopen;
HTP.print('Hello world');
HTP.bodyclose;
HTP.htmlclose;
END;
/
HTTP URL for this procedure will be http://oraclexeserver:8080/mobileapp/home or just http://oraclexeserver:8080/mobileapp/ (note the default-page DAD attribute).
The proceudres like HTP.htmlopen will just generate HTML tags.
Passing HTTP GET parameters is very easy and they will be set as a procedure parameters with the same name. Parameter data type is VARCHAR2 or if HTTP request has more than one parameter with the same name, then TABLE OF VARCHAR2 data type is used.
CREATE OR REPLACE PROCEDURE mobileapp.paramtest(p1 IN VARCHAR2 DEFAULT NULL,
p2 IN VARCHAR2 DEFAULT NULL) IS
BEGIN
IF p1 IS NULL THEN
HTP.p('Hello world');
ELSE
HTP.p('Hello p1='||p1||' p2='||p2);
END IF;
END;
/
This procedure can be called with HTTP URL:
http://oraclexeserver:8080/mobileapp/paramtest?p1=value1&p2=value2
Both parameters are optional (procedure parameters have DEFAULT value set).
If the procedure raises an exception or suitable procedure name with correct argument list cannot be found, then HTTP error code 404 (page not found) is the result.