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.

Continue Reading …

{ 2 comments }

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.

Continue Reading …

{ 0 comments }

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 →
SQL: Compare Two Tables for Differences

SQL: Compare Two Tables for Differences

In a recent project, a shadow table had to be compared periodically with its main source table to identify the differences between the two tables. The nature of differences fell into one of these buckets: Insert Required [INS]: If the source had rows not present in the shadow table Update Required [UPD]: If one or […]

Read the Full Article →
Thumbnail image for DATE Format SQL for Dates Stored as Strings

DATE Format SQL for Dates Stored as Strings

It is well-acknowledged that attributes in the database should use the correct datatypes (numbers should go into NUMBER columns, dates into DATE columns, etc). Storing date values in VARCHAR2 columns is an open invitation for bugs and issues due to date format such as the one I’m about to describe. Besides, using string-type columns to […]

Read the Full Article →
Thumbnail image for Range Interval Partitioning

Range Interval Partitioning

Oracle 11G brought with it a beautiful new twist to range partitioning – the ability to create partitions on-the-fly rather than having to pre-create all partitions. Hello interval partitioning.

Read the Full Article →