Keywords Archives

DETERMINISTIC Functions in Oracle

A PL/SQL function in Oracle can be tagged with a DETERMINISTIC clause, to indicate that the function will always produce the same output for a given input and will have no side effects.

A little elaboration is in order here.

1. …will always produces the same output for a given input

Let’s see this with an example: a function get_primary_phone takes as input customer_id and returns the customer’s primary phone. Internally, the function executes SQL on a customer contact table, ranks and filters the result to get the customer’s primary phone number.

Pagination Queries in Oracle OFFSET FETCH

Pagination is the process of dividing query results (typically on a user interface) into discrete pages, where each page contains a smaller/more manageable number of rows. In classic offset pagination, each page shows a fixed count of rows (say N): if N is 20, then the first page shows 1-20 rows, a “Next” navigation moves control to the second page with 21-40 rows, and so on.

This article demonstrates how to write queries to achieve this style of pagination in Oracle.

When you have an enormous load of data to copy/merge from one table to another, you are probably concerned about:

Performance: How fast can the program copy/merge all the data?
Exception handling: How well does the program deal with errors in the data? Can it highlight bad data if present in the lot, at the same time allow the good data to pass successfully?

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.

LISTAGG for Converting Rows to Comma-Separated String in Oracle

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 comma-separated list, using Oracle’s built-in function LISTAGG.

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 error:

PLS-00306: wrong number or types of arguments in call to 'MULTISET_<operation type>'

There is a workaround though, which lets us use MULTISET operations successfully with complex types. Here’s how.

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 on nested tables.

Here is a demo with scripts for performing MULTISET operations on nested tables of strings.

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:

PIVOT in Oracle 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 lot more intuitive.

This post shows the use of PIVOT with an example and sample scripts.

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:

 Name             Type
 ---------------- ------------
 NAME             VARCHAR2(30)
 CATEGORY         VARCHAR2(10)

And you want to create this data in the table COLORS:

NAME                           CATEGORY
------------------------------ --------
yellow                         1
red                            1
blue                           1
yellow                         2
blue                           2

Hands up all who write insert statements this way:

insert into colors (name, category)
values ('yellow', 1);

insert into colors (name, category)
values ('red', 1);

insert into colors (name, category)
values ('blue', 1);

insert into colors (name, category)
values ('yellow', 2);

insert into colors (name, category)
values ('blue', 2);

DETERMINISTIC Functions in Oracle

1. …will always produces the same output for a given input

Pagination Queries in Oracle: Analytics, Row Limiting Clause

DML Error Logging: Supersized DML Operations Made Easy

INNER JOIN and OUTER JOIN Explained

LISTAGG for Converting Rows to Comma-Separated String

How to Perform MULTISET Operations on Nested Table of Objects

MULTISET Operations: Combining Nested Tables Made Easy

NOCOPY Parameter Hint in PL/SQL

PIVOT in Oracle 11G to Select Rows As Columns

INSERT ALL: Insert Multiple Rows with a Single INSERT Statement