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
  UNION ALL
  SELECT 'CCCBBBAAA'          FROM dual 
   UNION ALL
  SELECT  (more...)

SQL Navigator 6.7 Beta available

Uncategorized
| 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:='http://oraqa.com/2012/02/27/how-to-transform-a-csv-file-into-html-table-in-sql/'


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


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

ORA-03113 and Bulk Collect

It can be quite frustrating when you are working on a database package and your session is terminated time and time again and leaves you clueless as too why this is happening. First of all, this is reproducible on an Oracle 10g Enterprise Edition - haven't tried other versions (yet).

SQL> @../utils/rel

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2. (more...)

ROWID: "this dreadful identifier"

Uncategorized
| 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

ID,FIRSTNAME,MIDDLENAME,LASTNAME
1,Frank,,Zhou
2,Peter,B,Lee
3,John,C,Adam
4,Dave,,Washington


CREATE TABLE CSV_FILE (csv_str VARCHAR2(3000))
     ORGANIZATION EXTERNAL
     (
       TYPE  (more...)

Some words on result cache dependencies tracking in 11.2.

Uncategorized
| Feb 7, 2012

First, every result of a function or query for which result cache is enabled has its own dependencies.
Second, all dependencies are tracked while the function/query is being executed.
Third,  the PL/SQL function result cache and the SQL result cache internals are the same.
Fourth, if you have a function within a query, for which result cache hint is used, the dependencies for the query result will include the dependencies on the objects accessed by the function.

This has several consequences.

1. One function may have different dependencies for different input arguments. Let’s look at the following example:

--drop table  (more...)

Oracle Magazine January-February 2012 online versions

Uncategorized
| Feb 6, 2012
Oracle Magazine January-February 2012:
Flash version
HTML version



TIOBE Programming Community Index for February 2012

Uncategorized
| Feb 6, 2012
PL/SQL is on the 17th place TIOBE Index (February 2012).
It's was 32nd in February last year (February 2011)!
In 2008 and 2007 in February it was in 13th position in the index.

Comparing Schemas: Red Gate Schema Compare for Oracle

This week I attended a webinar by Cary Millsap, organized by Red Gate. It was called "Real Developers DO use Tools" and was focussed on, well, tools. At the end of Cary's talk, James Murtagh showed a demo of their Schema Compare tool. The demo wasn't flawless, but he recovered nicely :) (note to self: don't do live demo's). And this - the demo, not the occuring error - triggered a memory that I wanted to write a blog on the Schema Compare tool.If you missed the webinar you can find it on the website of Red Gate here: (more...)

SQL Navigator 6.6 released

Uncategorized
| Dec 15, 2011
Quest Software released the 6.6 version of SQL Navigator. Mainly it corrects some bugs.
The most usefull new feature is in the history search (where you can search the runned scripts/commands for an expression) to filter the commands by type.


Check the Release Notes and the Known Issues List.

TIOBE Programming Community Index for December 2011

Uncategorized
| Dec 15, 2011
PL/SQL is on the 14th place TIOBE Index (december 2011).
It's was 23st in November last year.
It was in 13th in 2008 and 2007 in the same month!

Oracle Magazine November-December 2011 online versions

Uncategorized
| Dec 7, 2011
Oracle Magazine November-December 2011:
Flash version
HTML version

File list from the server

I've been asked by one of my architects whether there is a quick way of see a list of file existing in the random folder on the server. The first response was - NO, because all file system communication should go via DIRECTORY object (starting 10g).

But I recalled that (more...)

TIOBE Programming Community Index for November 2011

Uncategorized
| Nov 19, 2011
PL/SQL jumped to the 12nd placeTIOBE Index (november 2011).
It's was 20st in November last year.

Logger Project Moved Temporarilly

The site samplecode.oracle.com was decommissioned recently. I was hosting a number of projects there including “Logger”, my PL/SQL instrumentation package. Until I find a new home, here’s a temporary link to the latest release (1.4) or if you just want to view the readme use this link. I’ll update this post when I decide on […]

Oracle Database 11g Release 2 Express Edition Available

Uncategorized
| Sep 5, 2011
Oracle Database 11g Release 2 Express Edition (Oracle Database XE) final version is available. It's "an entry-level, small-footprint database based on the Oracle Database 11g Release 2 code base. It's free to develop, deploy, and distribute; fast to download; and simple to administer." DownloadDocumentation Do not forget to check the Open Bugs and Known Issues section.

Oracle Magazine September – October 2011

Uncategorized
| Sep 5, 2011
HTML version
Flash version