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.

Thumbnail image for Extract Very Long String or CLOB from JSON [pre 12.2]

Extract Very Long String or CLOB from JSON [pre 12.2]

How to extract a very long string or CLOB from JSON CLOB data , where the size to be extracted exceeds MAX_STRING_SIZE (32767), in a pre-12.2 Oracle database.

Read the Full Article →
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 →
Thumbnail image for Oracle Binary XML Storage, With or Without XML Schema [EXAMPLE]

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

Working examples of Oracle binary XML storage, with or without XSD, along with steps needed to configure and register the XSD in Oracle.

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

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

Binary XML, object-relational, or CLOB? An overview of approaches for storing XML data in Oracle to best fit the nature of the data and its expected use.

Read the Full Article →
Fuzzy Matching with UTL_MATCH

Fuzzy Matching with UTL_MATCH

Oracle supports fuzzy matching with UTL_MATCH functions based on Levenshtein Distance and Jaro-Winkler Distance. A look at UTL_MATCH functions 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

Convert VARCHAR2 to DATE with appropriate format masks, for comparing two dates stored as strings with different DATE formats in the database.

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 →
Thumbnail image for 10 Important Lessons in Data Modeling

10 Important Lessons in Data Modeling

Follow these ten data modeling guidelines to make sure that your application database design is accurate, usable and scalable.

Read the Full Article →