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 →
INNER JOIN and OUTER JOIN

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

When the need is to transpose columns as rows, Oracle 11G+ enables you to easily implement this using the UNPIVOT clause is SQL. 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 →