Thumbnail image for Oracle Collections and Object Types: Comparing for Equality

Oracle Collections and Object Types: Comparing for Equality

Oracle collection or object type comparison – how to check if two Oracle collections or object types are equal or not.

Read the Full Article →
Thumbnail image for The Smart Way to Check if an Element Exists in a Collection

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

Oracle 10G onwards, the set operator MEMBER OF can be applied to test if an element exists in a collection or not. You do not need to loop through all elements in the collection to find a match.

Read the Full Article →
Thumbnail image for The Curious Case of The Missing ORA-00904

The Curious Case of The Missing ORA-00904

What’s worse than an error that shows up when you don’t expect it? An error that does NOT show up when you totally expect it. Witness “ORA-00904: invalid identifier” playing truant.

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 →
Thumbnail image for LISTAGG for Converting Rows to Comma-Separated String

LISTAGG for Converting Rows to Comma-Separated String

Oracle built-in function LISTAGG lets you concatenate multiple rows of data into a single delimiter-separated string. No more of those circuitous MAX(SYS_CONNECT_BY_PATH) or STRAGG solutions of the pre-Oracle 11G R2 days!

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 →
Thumbnail image for How to Cast PLSQL Function Output as View

How to Cast PLSQL Function Output as View

How to cast PLSQL function output as view, when a single SQL statement is not sufficient to get the result you want.

Read the Full Article →
Thumbnail image for Stop Overlapping Ranges in a Table

Stop Overlapping Ranges in a Table

Overlapping Ranges Blocker Toolkit: trigger-based solution to prevent overlapping ranges of data in an Oracle master table that stores range-based data, such as income tax brackets or sales commission rates.

Read the Full Article →
Thumbnail image for What is the Signature of a PLSQL Program?

What is the Signature of a PLSQL Program?

Interview FAQ: What is the signature of a PLSQL program? Read on for the answer: the definition of PLSQL signature and components that make the PLSQL signature.

Read the Full Article →
Thumbnail image for LEAD/LAG Functions to Place a Value within Extents

LEAD/LAG Functions to Place a Value within Extents

Analytic functions LEAD and LAG provide access to more than one row of a table at the same time, without a self join. This feature can be used to find the position of a value within extents, where the extents are defined by the upper bound only and the lower bound is derived via comparison to the previous/next row.

Read the Full Article →
Page 6 of 13
1
...
2
3
4
5
6
7
8
9
10
...
13