keywords

Thumbnail image for 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 How to Perform 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 MULTISET Operations: Combining Nested Tables Made Easy

MULTISET Operations: Combining Nested Tables Made Easy

Set operators (UNION, INTERSECT, MINUS) have long been available in basic SQL to process data in tables, but for data in PL/SQL nested tables, we’d earlier have to go through the ritual of traversing through the collections in a loop, doing a row-by-row comparison. Oracle 10G onwards, MULTISET features have made possible single-step set operations […]

Read the Full Article →
Thumbnail image for NOCOPY Parameter Hint in PL/SQL

NOCOPY Parameter Hint in PL/SQL

Before we get to understanding the NOCOPY parameter hint in PL/SQL, it would help to first look at PL/SQL parameter modes and the ways in which they pass values in and out of a subprogram. PL/SQL subprograms have three parameter modes:

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

PIVOT in Oracle 11G to Select Rows As Columns

A frequent requirement in SQL is to "pivot" a result set – that is, display rows as columns. Before 11G, the developer had to do the equivalent of jumping through hoops to pivot the data. In 11G, with the introduction of the new PIVOT syntax, the task of transposing rows to columns has become a […]

Read the Full Article →
Thumbnail image for INSERT ALL: Insert Multiple Rows with a Single INSERT Statement

INSERT ALL: Insert Multiple Rows with a Single INSERT Statement

A quick tip to get rid of the plodding way of inserting data into a table with multiple INSERT statements. Know what I mean? Say you have a table COLORS with this structure: And you want to create this data in the table COLORS: Hands up all who write insert statements this way:

Read the Full Article →
Thumbnail image for 15 Things You Should Know about the ORDER BY Clause

15 Things You Should Know about the ORDER BY Clause

When multiple columns/expressions are specified in the ORDER BY clause, the precedence of sorting is left to right. The ORDER BY clause can order in ascending (ASC) or descending (DESC) sequence, or a mix of both. If ASC or DESC is not explicitly stated, then ASC is the default. ORDER BY ASC places NULL values […]

Read the Full Article →
Thumbnail image for MERGE: Insert New Rows, Update Existing Rows in One Shot

MERGE: Insert New Rows, Update Existing Rows in One Shot

MERGE in Oracle combines the power of INSERT and UPDATE into one power-packed statement

Read the Full Article →
Thumbnail image for How to Manage those Pesky NULLs when Sorting Data

How to Manage those Pesky NULLs when Sorting Data

How to position NULL values in the sort column – send them to the top or the bottom in the result set.

Read the Full Article →
Thumbnail image for LTRIM, TO_CHAR, And A “Strange” Query

LTRIM, TO_CHAR, And A “Strange” Query

A query using LTRIM and TO_CHAR seemed to be behaving oddly, but soon a simple explanation presented itself. Check out the query below. Can you tell why it gives the answer it does?

Read the Full Article →