Thumbnail image for Replace Duplicates in ID Column with Unique Values

Replace Duplicates in ID Column with Unique Values

A solution to the requirement of replacing only the duplicate IDs with unique values in a database table, and letting everything else stay untouched.

Read the Full Article →
Oracle Collections and Object Types: Comparing for Equality

Oracle Collections and Object Types: Comparing for Equality

Oracle can easily compare data items of scalar data types (those that hold a single data value with no internal components – e.g. NUMBER, DATE or VARCHAR2). So, if a and b are two NUMBER variables, all you need to do to check whether they are the same or not, is test "if (a=b)". Can […]

Read the Full Article →
Check if an Element Exists in a Collection

The Smart Way to Check if an Element Exists in a Collection

When you need to find out if a specific element exists in a collection or not, how do you go about it? Do you loop through the list till you find the value? If that’s what you do, this post is for you.

Read the Full Article →
Why no ORA-00904: invalid identifier

The Curious Case of The Missing ORA-00904

Here’s a puzzle for you to solve. You’re given these two tables – empl and dept – in which column emp_dept_id of table empl references dept_id of table dept. Note that the foreign key column names in the two tables is not identical. It’s called dept_id in table dept, emp_dept_id in table empl.

Read the Full Article →
Hierarchical Data in a Relational Table

Flatten Hierarchical Data using a Single SQL

Single Oracle SQL to find and flatten hierarchical data stored in a table, using a combination of SYS_CONNECT_BY_PATH and regular expressions.

Read the Full Article →
LISTAGG for Converting Rows to Comma-Separated String

LISTAGG for Converting Rows to Comma-Separated String

In an earlier post, we saw how Oracle SQL can be used to transpose data from rows to columns. It is a similar, though simpler, exercise to transpose data from rows to a comma-seperated list.

Read the Full Article →
Thumbnail image for Referencing User Defined Types over DBLink: Problem and Alternatives

Referencing User Defined Types over DBLink: Problem and Alternatives

Understanding PLS-00453, the error that shows up when a UDT is used as a procedure parameter across DBLink, Plus a few ways to work around it.

Read the Full Article →
Cast PLSQL Function Output as View

How to Cast PLSQL Function Output as View

An Oracle view is typically like a window to the output of a SQL statement, but sometimes, a single SQL isn’t sufficient to get you the result you want. You may need to do some procedural data manipulation before you get the output you want. Does this mean, you cannot use an Oracle view if […]

Read the Full Article →
Overlapping Ranges in a Table

Stop Overlapping Ranges in a Table

Range-based definition of values is a common business scenario, as in the case of mortgage pre-payment penalty charges depending on "pre-payment amount" slabs, or incentives for a dealer depending on "business generated" slabs. A necessary validation is such cases is that the slabs should have no overlaps: each discrete value should match no more than […]

Read the Full Article →
PL/SQL Signature

What is the Signature of a PLSQL Program?

In interviews for PLSQL developers, I usually ask a question of the form: If you write a procedure to do <specific operation>, what will be the signature of such a procedure? In response, the developer often veers off into describing the logic/body of the procedure, which is not what was asked. If you’re among those […]

Read the Full Article →