PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle’s procedural extension for SQL. PL/SQL includes features such as conditional statements, loop processing and exception handling.

Referencing User Defined Types over DBLink: Problem and Alternatives

Referencing User Defined Types over DBLink
Consider a PL/SQL call to a function which returns a user-defined type (UDT) – say, a SQL Type object. This works well as long as the called function resides in the same database as the calling PL/SQL, but if the called function is in a remote database, the PL/SQL call via DBLink end ups with the following error:

Error: PLS-00453: remote operations not permitted on object tables or user-defined type columns

This has often led to issues discovered too late for developers, who successfully tested function calls over multiple schemas simulating remote calls, but when the function databases were separated in later stages of testing, the PL/SQL call no longer worked.

Read more

How to Cast PLSQL Function Output as View

Cast PLSQL Function Output as View

An Oracle view is like a window to the output of a SQL statement, but sometimes, a single SQL isn’t sufficient – you may need to do some procedural data manipulation before you get the output you want. Does this mean an Oracle view cannot be used if the underlying code cannot be framed as a single SQL? Not at all! This article shows you how to cast PLSQL function output as a view.

Read more

Stop Overlapping Ranges in a Table

SQL to Stop Overlapping Ranges in Oracle

Income tax brackets. Sales commission rates. Loan pre-payment penalty charges. Those are types of master data that get defined as range-based matrices. An important validation for such data is that the slabs should have no overlapping ranges: any input value should match only a single row in the master range definition.

When range-based matrices are stored in a database table, how can overlapping ranges be prevented? Read on to find out the solution for an Oracle database.

Read more

What is the Signature of a PLSQL Program?

PL/SQL Signature

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 likely to answer that question similarly, this article will clarify for you the concept of "signature" of a PLSQL stored procedure.

Read more

MULTISET Operations: Combining Nested Tables Made Easy

MULTISET operations in Oracle

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.

Read more

An Easy Guide to PL/SQL Collections

PL/SQL Collections

A collection is a list of elements of the same type. Each element in the list has a unique position number or label, called the "subscript".

To give a logical analogy, consider a list of colors = {red, blue, green, yellow}. This list has four elements, all names of colors. There is a unique position of each element in the list. If we number the positions sequentially starting from one, we could say:

color[1] = red, color[2] = blue, and so on.

Here, color is the name of the collection, and the numbers within [] are the subscripts.

PL/SQL has three collection types. In this article, we’ll look at a chart for comparing the three, their advantages and limitations, and which one to use for your needs.

Read more