Datatypes

Each value in the database has a datatype, which associates a set of properties with it – for example, a DATE column can accept dates only, not strings or numbers. Datatypes may be built-in or user-defined.

JSON in the database

Storing JSON Data in Oracle: Why and How

Storing JSON data in Oracle has been made easy in version 12c. A demo of how JSON can be stored in Oracle and validated using the IS JSON check.

Read the Full Article →
Oracle XML Object-Relational Storage

Oracle XML Object-Relational Storage [EXAMPLE]

Object-relational storage of XML data in Oracle is based on shredding the XML data into SQL objects. Scripts and stepwise execution for object-relational XML 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 →
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 →
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 →
Oracle Total Recall

Oracle Total Recall for Tracking History

Oracle 11g Flashback Data Archive (Oracle Total Recall), essentially meant as a DBA tool to recover from logical corruptions and human errors, can be put to good use for another standard requirement in applications. Most applications need an audit trail or history of changes for important entities – say, the trail of changes to customer’s […]

Read the Full Article →
MULTISET Operations on Nested Table of Objects

How to Perform MULTISET Operations on Nested Table of Objects

We’ve seen the easy working of MULTISET operations on nested tables of simple types (i.e. collections of text, numbers). And we’ve seen the problem with extending MULTISET operations to nested tables of complex types (i.e. those based on OBJECTs with multiple attributes). PL/SQL code when run with MULTISET operations on complex collections throws up the […]

Read the Full Article →
Important Lessons in Data Modeling

10 Important Lessons in Data Modeling

Are you building the database for a custom OLTP application starting from scratch, with the business requirements as only initial input? No matter what the size, domain or location of your project, the following lessons will help to ensure that your physical data modeling process is smooth and that you end up with a database […]

Read the Full Article →
Thumbnail image for An Easy Guide to PL/SQL Collections

An Easy Guide to PL/SQL Collections

A collection is a list of elements of the same type. Each element in the list has a unique position number or label, called the "subscript". To give a logical analogy, consider a list of colors = {red, blue, green, yellow}. This list has four elements, all names of colors. There is a unique position […]

Read the Full Article →