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.
[click to continue…]
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.
[click to continue…]
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.
[click to continue…]
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.
[click to continue…]
How will you find out the number of rows in each table in a schema in Oracle?
Here are two approaches with their pros and cons.
[click to continue…]
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.
[click to continue…]
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:
[click to continue…]
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.
[click to continue…]
The Oracle database does not have the BOOLEAN datatype. No direct way to represent that a column like IS_ACTIVE on table ITEMS is TRUE or FALSE.
The good news is that there are simple workarounds to the situation.
[click to continue…]
CASE is a smarter rewrite for IF-THEN-ELSE, we said. It is for sure, but there is a difference in the way the ELSE part of it is handled.
Compare the code units below, one using CASE WHEN the other using IF-ELSIF. Both are identical in logic – two defined conditions, no ELSE path.
[click to continue…]
