Fuzzy Match using 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.

Continue Reading …

{ 0 comments }

SQL Find Differences between Two Tables

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:

  1. Insert Required [INS]: If the source had rows not present in the shadow table
  2. Update Required [UPD]: If one or more columns in a row (identified by a primary key) had changed values
  3. Delete Required [DEL]: If the shadow table had rows not present in the source table

Comparing two tables for differences (INS/UPD/DEL) needed a different solution from a simple MERGE SQL statement. Here an upsert was not to be executed, only the nature of differences to be identified.

Continue Reading …

{ 0 comments }

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 →
Thumbnail image for INNER JOIN and OUTER JOIN Explained

INNER JOIN and OUTER JOIN Explained

For those new to SQL, terms like INNER JOIN and OUTER JOIN can seem like fearsome foes. As the wise say, understanding conquers fear. Behind those geeky terms lie concepts rooted in simple real-world knowledge. Here’s a quickstart guide to these two basic joins in SQL: INNER JOIN and OUTER JOIN.

Read the Full Article →
Thumbnail image for Grant SELECT on V$ Views

Grant SELECT on V$ Views

When querying v$instance or v$session[1] as a non-admin user, you might come across this error: The error suggests that the non-admin user does not have the SELECT privilege on the sys-owned v$ view. On the face of it, the fix appears as simple as logging in as sys and granting select on the v$ view […]

Read the Full Article →
Thumbnail image for How to Find out Your Oracle Database Version

How to Find out Your Oracle Database Version

Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your database version. Here’s a rundown of ways to identify the Oracle database version using SQL and PL/SQL.

Read the Full Article →
Thumbnail image for Access Control List (ACL) in Oracle 11G

Access Control List (ACL) in Oracle 11G

I recently upgraded Oracle XE from 10G to 11G, and found that none of the PL/SQL code using UTL_HTTP was working after upgrade. The code failed with the error: Oracle 10G used to be happy as long as the user running network packages like UTL_HTTP had execute permission on the package. Oracle 11G and above […]

Read the Full Article →
Thumbnail image for UNPIVOT in Oracle 11G to Select Columns As Rows

UNPIVOT in Oracle 11G to Select Columns As Rows

Sometimes we need to transpose columns as rows in SQL. Oracle 11G onwards, this requirement can be easily implemented using the UNPIVOT clause. A typical scenario:

Read the Full Article →
SQL to Select Rows Conditionally Based On Column Value

SQL to Select Rows Conditionally Based On Column Value

Let’s say a table contains multiple rows for an id. The requirement is to select only one of those rows, based on the value in a "type" column which determines the row’s priority. A typical example is selecting one contact number for a customer, based on contact types.

Read the Full Article →