XMLTABLE to Convert XML To Relational Data

Here’s a prototype for using the SQL/XML function XMLTABLE to map XML data into relational rows and columns.

This solution uses the standard EMP table – the same can be extended to work with any XMLTYPE-relational mapping.

Continue Reading …

{ 0 comments }

Dynamic SQL

A comparison between static SQL vs dynamic SQL shows us pretty clearly that, if there exists a choice, we are better off choosing static SQL.

So, when should we use dynamic SQL?

Continue Reading …

{ 0 comments }

Static SQL vs Dynamic SQL

Static SQL vs Dynamic SQL: Which to use?

Some questions do not have definitive answers. "Is a full table scan bad? Should this design be denormalized? Will partitioning this table help?" The answers vary widely depending on the specifics on the problem. Fortunately, "Should I use static SQL or dynamic SQL?" is not one of those questions.

Read the Full Article →
SQLCODE and SQLERRM

What are SQLCODE and SQLERRM?

SQLCODE and SQLERRM are Oracle’s built-in error reporting functions in PL/SQL. When an error occurs in PL/SQL at runtime: SQLCODE returns the number of the last encountered error. SQLERRM returns the  message associated with its error-number argument. The error-number argument is optional: if omitted, SQLERRM returns the message associated with the current value of SQLCODE.

Read the Full Article →
Check if JVM is Installed in Oracle

How to Check if JVM is Installed in Oracle

I recently thought of using Java’s built-in BigInteger.gcd() method inside a PL/SQL function wrapper to calculate GCD in SQLs. And so I wrote the Java code and ran it on Oracle XE. Things didn’t go as planned.

Read the Full Article →
Oracle XML Object-Relational Storage

Oracle XML Object-Relational Storage [EXAMPLE]

After the post on models for storing XML data in Oracle and examples of binary XML storage, here’s a detailed look at the rigorous and performant structured XML storage or object-relational storage.

Read the Full Article →
Oracle Binary XML Storage

Oracle Binary XML Storage, With or Without XML Schema [EXAMPLE]

The previous post on storing XML data in Oracle gave an overview of binary XML storage: compact, flexible, compatible with XML data with or without associated XML schema. This post shows working examples of binary XML storage 1.1. without XML Schema 1.2 with XML Schema

Read the Full Article →
Storing XML Data in Oracle: Binary XML, Object-Relational, CLOB

Storing XML Data in Oracle: Binary XML, Object-Relational, CLOB

Oracle provides an abstract SQL data type called XMLType for storing XML data in the database. You can create an XMLType table,  or an XMLType column in a relational table, to persist XML data. Different storage models are available in Oracle to best fit the nature of the XML data and its expected use. These […]

Read the Full Article →
SQL XML in Oracle: Generating XML from Relational Data

Generating XML from Relational Data using Oracle SQL/XML

Oracle has built-in functions to convert relational data into XML format easily. These functions comes under the umbrella of SQL/XML, a specification that supports the mapping and manipulation of XML from SQL. This post shows you how to generate XML from relational data using Oracle SQL/XML functions as building blocks.

Read the Full Article →
Fuzzy Matching with UTL_MATCH

Fuzzy Matching with UTL_MATCH

Oracle’s UTL_MATCH package contains functions to perform fuzzy matching between two strings based on one of these algorithms: Levenshtein Distance Jaro-Winkler Distance Let’s understand the algorithms and see UTL_MATCH subprograms in action.

Read the Full Article →