How to simulate DML Operations on XML data using XU (XQuery Update)

The following SQL pattern can be used to simulate DML operations on XML data by utilizing XU (XQuery Update) functionalities.

This query will do the following XML data transformation by using XU insert/delete/rename/replace statements.

1) Convert all the XML Attributes to Elements.                ( XU Delete & Insert)

2) Change the  (more...)

Using SSL client certificates for authentication in UTL_HTTP

Starting from 11.2 its possible to use SSL client certificates to authenticate yourself to a remote web service using SSL client certificates. I did not find much information on it using Google or documentation, that is why I'm writing this post.

Please refer to this post by Tim Hall to get started on connecting to HTTPS service using UTL_HTTP, all of this is needed before continuing with SSL client certificate authentication.

The first thing you need is to generate user certificate request inside Oracle Wallet, sign it by CA and load the returned certificate back to Wallet. I'm not (more...)

How to validate XML data using XSD in Oracle XML DB

The following XML Schema can be used to validate the XML input data in the XML DB.

It enforces the following validation rules:

1) Parent_Tab/Parent_Row/dep_id is the (primary) key within Parent_Tab.
2) Parent_Tab/Parent_Row/dep_name should be one of the xs:enumeration list in DeptnameType and it is also unique within Parent_Tab.
3) (more...)

MS Exchange API for PL/SQL

| May 26, 2012
As mentioned in my earlier post, I've been working on a PL/SQL wrapper for the Microsoft Exchange Web Services (EWS) API. The code is now ready for an initial release!


Using this pure PL/SQL package, you will be able not just to search for and retrieve emails and download attachments, but you will also be able to create emails and upload attachments to existing emails. You can move emails between folders, and delete emails. You can read and create calendar items. You can get the email addresses of the people in a distribution (mailing) list, and more.


A friend in need: Flashback Query

| May 25, 2012

"It is sayd, that at the nede the frende is knowen."
- William Caxton: "Sonnes of Aymon" (1489)
"I say, that when you screw up, Flashback Query is a friend indeed!"
- Morten Braten: "SQLs of Oracle" (2012)

The Flashback Query feature of Oracle (10g) can be really helpful when you run that UPDATE or DELETE statement and then, after the COMMIT, you discover that you made a mistake!

Wouldn't it be nice to be able to look back and see what the data looked like before you messed things up? With a Flashback Query you (more...)

jQGrid Integration Kit for PL/SQL and Apex version 1.2 available

| May 21, 2012
It's been over two years since I released the initial version of the jQGrid Integration Kit for PL/SQL and Apex.

Since then, I've added some additional features and fixed a few bugs:

Version 1.1

  • Procedure include_static_files_once will make sure required JS and CSS files are correctly included
  • Support for "popup_lov" column edit type
  • Procedure render_grid supports p_read_only parameter that can be used to set read-only dynamically
  • Support for some jQGrid built-in formatters based on Oracle column data type
  • Lists of values (select list variety, not Popup LOV) now support bind variables and Static LOVs
  • Date parser handles dates and (more...)

Calling Java procedure from database

Oracle Database has had the possibility to run Java code inside the database for a long time. It's a very rare occasion when you need to use it but still. Here is one example I used to download content from HTTPS website that required user certificates for authentication. Please take the code below more as an example how to put simple Java code inside the database, not as a solution for user certificates authentication, because UTL_HTTP can do the same thing (although I wasn't successful in implementing it under

First, load the Java source into database. (more...)

SQL Navigator 6.7 released

| May 9, 2012
Quest Software released the 6.7 version of SQL Navigator. Mainly it corrects some bugs.

Select * from Inbox

| May 1, 2012
... or how to read your (Microsoft Exchange) email using SQL and PL/SQL.


You are probably familiar with sending email using PL/SQL; there's the UTL_SMTP package, the APEX_MAIL package, and various other utilities that do this.

But what about reading email using PL/SQL? You need to investigate the POP and IMAP protocols. A quick search on Google turns up some sample code and even a feature-rich package, although the latter requires Java in the database (which I try to avoid whenever possible, and which is not available in Oracle XE in any case).

Approaching this from a different angle, (more...)

How to do XML based data manipulation in SQL and XSLT

The following SQL pattern can be used to do XML based data manipulation and transformation by utilizing XSLT functionality.

This query will do the following data manipulation and transformation:

1) Rename the xml tag <FirstName> to <FN> when the position of the element node is
either the last one or (more...)

Interesting PL/SQL and Apex links, April 2012

| Apr 21, 2012
Here's a bunch of interesting stuff related to PL/SQL and Apex that I've come across lately, I'm posting it both as a reminder to myself, and to help spread the word:

  • First mention of PL/SQL enhancements for Oracle Database 12c: In his PL/SQL newsletter for March 2012, Steven Feuerstein revealed that "I've also started beta testing Oracle Database 12c PL/SQL. I can't yet share with you any of the new features, but I can tell you that PL/SQL will certainly be a richer, more usable language when 12c goes production!".
  • On that topic, a couple of things I (more...)

Parsing a CSV file in PL/SQL

The ability to parse a CSV file in PL/SQL seems like a simple requirement and one would think that you could either a) easily implement it yourself, or b) find some examples of it on the web.   Well if you have tried option A, you probably realized it gets real tricky when you have commas and double quotes in your actual data as well as having them as your deliminators and optionally enclosed by characters as well.  Plus all that substr‘ing and instr‘ing can really hurt your head after a while.  If you tried option B, then (more...)

How to do table column based data manipulation in SQL and XQuery

The following SQL pattern can be used to stragg all the table column data together
The STRAGG(column data) is grouped together based on the table column names.
This SQL also manipulates comma-delimited strings using XQuery functionalities.

(ID            NUMBER,

How to do character based data manipulation in SQL and XQuery

The following SQL pattern can be used to do character based data manipulation
by utilizing the XQuery functionalities.

COLUMN old_str  FORMAT  A28
COLUMN ordered_no_dup_chars FORMAT A23
COLUMN Reversed_chars FORMAT  A23

--------------------SQL Query-------------------

WITH data AS
(SELECT '3332221118888'   AS  old_str  FROM dual
  SELECT 'CCCBBBAAA'          FROM dual 
  SELECT  (more...)

SQL Navigator 6.7 Beta available

| Mar 28, 2012
Quest Siftware released to the community a beta version for SQL Navigator 6.7. If you have a previous beta version installed, you can upgrade it with this build. You can (and should) install this side by side with your current 6.6 (or previous) version. Read all about it

How to convert an Internet HTML Table to CSV format in SQL

The following SQL pattern can be used to transform an HTML Table on the web to CSV format.

set long 10000;
variable url_input varchar2(500)
exec :url_input:=''

-------------------------------------------------------------------SQL QUERY---------------------------------------------------------------------

(SELECT CAST(REGEXP_REPLACE(SUBSTR(STR, 0, REGEXP_INSTR(str,'</table>', 1,1,1, 'i') ), '( ){2,}', ' ') 
         AS VARCHAR2(4000)) AS html_str
 (SELECT SUBSTR(str,  (more...)

ROWID: "this dreadful identifier"

| Mar 7, 2012
Today I'm taking a look at the core packages of the PL/SQL language, namely the STANDARD and DBMS_STANDARD packages in the SYS schema. These packages contain a lot of the language features we use every day in PL/SQL programming.

These two packages are special in that you do not need to qualify the name of functions, procedures and constants in these packages with the package name. For example, SYSDATE and USER and DECODE and so on are declared in the STANDARD package, but you can use it without having to specify STANDARD.SYSDATE or STANDARD.USER or STANDARD.DECODE (which (more...)

How to transform a RefCursor query resultset into HTML table in SQL

The following SQL pattern can be used to tranform a RefCursor query resultset into data in HTML table format by utilizing the XSLT functionality. The query can handle NULL in the data columns. The SQL input string should be based on the following format:

SELECT Col AS name_x, Function() AS (more...)

How to convert a RefCursor query resultset to CSV format in SQL

The following SQL pattern can be used to convert a RefCursor query result set into a CSV format. This query can handle NULL in the data columns. The SQL input string should be based on the following format:

SELECT Col AS name_x, Function() AS name_y, Analytical_function( ) AS name_z FROM (more...)

How to transform a CSV file into HTML table in SQL

The following SQL pattern can be used to tranform the CSV file into a HTML table by utilizing the XQUERY functionality.

create or replace directory tmp as '/tmp';

create a word.csv file.

C:\tmp>more word.csv


       TYPE  (more...)