Oracle VM Site Review - Oracle VM Health Check
view counter

PL/SQL Feed

Oracle PL/SQL (Procedural Language/Structured Query Language) resources, news, and support articles.

Easy quiz: rownum < NaN

As you know, NaN is a “Not a Number”.
How do you think, what would be the result of the following query? (0f/0 == NaN)

select count(*) cnt from dual where rownum < 0f/0;

select * from table where rownum=1

I never thought I would have to optimize so simple query as

Way Too Invisible Columns

Oracle Database 12c introduced "invisible columns": they are only visible when you name them explicitly in the SELECT list. Unfortunately, they seem to be even more invisible when you access them through a database link! Here are some surprising results from SELECT and MERGE statements.

INDEX FULL SCAN (MIN/MAX) with two identical MIN()

I’ve just noticed an interesting thing:
Assume, that we have a simple query with “MIN(ID)” that works through “Index full scan(MIN/MAX)”:

Filtering String Dates

A question came up about how to verify dates from a string without throwing a casting error because of a non-conforming date. You can throw a number of exceptions, and I wrote a function to filter bad string formats like the DD-MON-RR or DD-MON-YYYY.

The first one is for a day between 1 and the last day of month, which is:

MERGE and invisible columns = invisible documentation?

Oracle 12c introduced "invisible columns" to help us add columns to tables without breaking existing applications. The documentation explains how they work with SELECT and INSERT, but not MERGE. Here's what happened when I tried MERGE.

A PL/pgSQL Function

Somebody wanted to know how to write a basic PostgreSQL PL/pgSQL function that returned a full name whether or not the middle name was provided. That’s pretty simple. There are principally two ways to write that type of concatenation function. One uses formal parameter names and the other uses positional values in lieu of the formal parameter names.

Since you’ll probably test the two approaches, I’ve also provided a conditional drop statement for the full_name function. The code is for named notation is:

COMPARE_SYNC: Introducing the package

This helper tool generates SQL for

  • Comparing tables, views and queries, both local and remote.
  • Synchronizing, or applying changes to target tables from either source tables or "Change Data Capture" input.

Participants and Dates set for (four!) 2014 Annual Championships

2014 is a thing of the past, but there are at least four more 2014-related activities to do: the annual championships.

With the expanding selection of weekly quizzes come an expanded set of annual championships.

This year we have four, and here are the dates and times:

PL/SQL Fall Through?

Somebody wants to know how you can write a PL/SQL solution that mimics the fall through of a switch statement because PL/SQL doesn’t support a switch statement. It’s a question that I found interesting because there wasn’t a need for it when I figured out what he wanted to accomplish. Essentially, he wanted to know how to implement a nested loop where the first loop runs in ascending order and the nested loop runs in descending order based on the value of the outer loop.

view counter