datatypes

Thumbnail image for 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 11G 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

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 […]

Read the Full Article →
Thumbnail image for The BOOLEAN Datatype

The BOOLEAN Datatype

The Oracle database does not have the BOOLEAN datatype. No direct way to represent that a column like IS_ACTIVE on table ITEMS is TRUE or FALSE. The good news is that there are simple workarounds to the situation.

Read the Full Article →
Thumbnail image for The Difference between CHAR, VARCHAR and VARCHAR2

The Difference between CHAR, VARCHAR and VARCHAR2

Oracle has three datatypes to store strings. – CHAR, VARCHAR and VARCHAR2. How do they differ from each other? When should you use which? Let’s find out the answers in this article.

Read the Full Article →