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 »

Quick examples into Oracle PL/SQL collections.

There are three types of Oracle Collections you can use in PL/SQL :

• Index-by-Tables : Unbounded, Hashes , Single Dimensional
• Nested-Tables : Unbounded, Array, unordered, Single dimension
• VARRAYS : Bounded, Single Dimension

Each one of these Types have its own features and limitations.

Most of them, share some common functions like EXTEND, FIRST, LAST, ..etc

These self-explaining examples; helps the reader get a first introduction into each collection type .

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 »

Oracle : What is APEX ?

APEX is a great Oracle product, yet some-how is under-rated.

Think of APEX for Oracle the same as Access is for Microsoft. It is light-weight-declarative tool to build database applications without much coding.

If you embrace this analogy between APEX and Access, then allow me to introduce APEX by comparing it to ACCESS.

• Read more »

Oracle Virtual machine “OTN Developer Days” appliance.

If you are a developer and need your own Oracle environment to try out and test ideas without disrupting your main development environment, then I propose using a Virtual Machine with Developer Days appliance.

Choose what type of environment you want from this link and start following the instructions. I prefer the Developer Days appliance , since it requires reasonable resources and more related to my work. This virtual Machine contains:

• Oracle Enterprise Linux 5
• Oracle Database 11g Release 2 Enterprise Edition
• Oracle TimesTen In-Memory Database Cache
• Oracle XML DB
• Oracle SQL Developer
• Oracle SQL Developer Data Modeler
• Oracle Application Express 4.0
• Oracle JDeveloper
• Hands-On-Labs (accessed via the Toolbar Menu in Firefox)

Read more »