Yet another Oracle XML challenge

If you have read my previous tip about Oracle XML API

You would learn to use XML API as nice quick’n’dirty replacement to tons of decode functions.

In this challenge I got on Oracle 9i (yes they still exist in production 2013).

• We have a string such as ‘123#124#122’, we need to split the different values into record , sort them and map them from a Value-key pair table in an Oralce 9i database.
• And for reasons of implementation it has to be done in pure SQL, not PL/SQL.
• and it is best to avoid creating any stored procedures or functions.

A more elaborate description of the problem : Splitting a delimited text with Oracle SQL into records, and sorting them out.

This is a real challenge in the production environment I’m working in.

Read more »

Some quick and easy examples into oracle XML DB.

Oracle XML DB is the API of SQL functions inside the oracle DBMS which makes oracle XML enabled.

If you don’t know much about XML, please refer to this quick tutorial :  especially the starting slide 74 discussing Oracle and XML.

XML DB is a series of SQL functions ,PL/SQL packages and DBMS features for processing XML data In and out of Oracle.

The most important feature; is adding the new column type “XMLType”; which enable storing structured and unstructured XML documents into oracle. A considerable step ahead from CLOB.

There are two sets of features :

Read more »

SOAP in 100 minutes

If you are an enterprise developer, then you must have came across SOAP (Simple Object Access Protocol).

When you are first introduced to it, you drown in a ocean of terms and jargon that eventually kills the purpose of it (Simple).

In this this 100 minutes tutorial I try to introduce the reader to SOAP jargon without being stuck in the Jargon Quick sand. I try to introduce terms naturally and gradually as they are needed and as much as possible try to provide visual exhibits to reduce the number of words.

Read more »

XML tutorial in 100 minutes

Time for another tutorial: XML in 100 minutes.

XML today is a vital technology in contemporary organizations, it is widely used to transfer data between applications and other uses.

Knowing XML for today’s IT Gurus is as important as Unix and SQL skills. Many Applications requires XML know-how.

This short guide to XML is only an fast introduction to XML, later tutorials will follow about the individual subjects in XML, like XSLT, SOAP …etc.

The tutorial is available here in PDF, HTML and Adobe-Flash Formats

One of the slides has an overview of XML technologies:

XML at a glance

Read more »

Visualize your Data with GraphViz

Ever wanted to visualize your  database information ?

here is a quick answer:

SQL –> XML –> XSL trasnformation–> DOT –> GraphViz –> JPEG/PDF

I work theses days with Siebel CRM system.  It is very configurable in how you want to store your data and display them.

One thing about Siebel Configuration, is that you can overload one table with multiple entities.  Each Siebel customization could be different in how you organize your entities into tables. So I had a problem in really understanding how the system was developed. Cases like which type of records link to which entities in the same table or other tables.

I was looking for someway to visualize Entity relation based on the data that is there in the Database -I’m not talking about Entity Relationship Diagram (ERD)- I’m talking about how certain customers relate to each others and to their addresses.

Read more »