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.

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 31: How to Avoid a Salted Banana

In the February 2015 issue of the NoCOUG Journal, Lothar Flatz introduces the term “salted banana” for a certain kind of problem in SQL tuning.(read more)

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 (more...)

Share your interesting SQL or PL/SQL experience on stage, on camera, or in print

If you have had interesting experiences with SQL or PL/SQL in your career, here’s your opportunity to share them on stage, on camera, or in print. Send your stories to contact_nocoug@nocoug.org and we’ll forward them to the SQL evangelists team headed up by Steven Feuerstein. You may be selected to tell your stories on stage at the YesSQL! presentation at the winter conference next Tuesday or your stories may be recorded on camera or (more...)

STEW_COMPARE_SYNC: Introducing the package

After many blog posts about comparing and synchronizing tables, I have united all the techniques I presented in one place. The STEW_COMPARE_SYNC package 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.

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 (more...)

Compare and Sync without Primary Keys

I have written a lot about comparing and synchronizing tables. My examples always had both primary keys and non-key columns, so I could do updates along with inserts and deletes. What about the other tables? Here's a technique that works for them.

Convert to SQL Server?

I’m always amazed at the questions that pop up for me. For example, how do you convert an Oracle script that creates my Video Store model to a Microsoft SQL Server script. It’s not very hard but there’s one big caveat, and that’s the fact that system_user is a reserved word. That means you can’t create the Access Control List (ACL) table with a system_user name. The alternative, would be to convert the system_user table (more...)

How to avoid a salted banana! (SQL tuning)‏

Dear NoCOUG members and friends,

You’re going to enjoy the new issue of the NoCOUG Journal. Click here to download it.

  • Lothar Flatz talks about the biggest issue in SQL tuning: the “salted banana.” We predict that the phrase “salted banana” will become as widely known in the Oracle community as “compulsive tuning disorder.” Click here to go directly to Lothar’s article.
  • Janis Griffin—a.k.a. the Looney Tuner—explains why the Oracle (more...)

MERGE using Change Data Capture (CDC)

Use MERGE to apply "Change Data Capture" input to a target table with one SQL statement.

MySQL Locking

Have you ever wondered if MySQL does Row Level Locking? Or wondered why are you getting the error:

ERROR 1205 (HY000) : Lock wait timeout exceeded; try restarting transaction

You get the error because your allocated time to hold a DML lock in a transaction exceeds the set limit. Usually the default limit to hold a DML row lock, set by innodb_lock_wait_timeout db parameter, is 50 seconds. If your transaction doesn't commit/rollback within 50 seconds (more...)

Popular Programming Languages

First of all, Happy New Years!

IEEE Spectrum published a ranking of the most popular programming languages. Computational journalist Nick Diakopoulos wrote the article. While it may surprise some, I wasn’t surprised to find SQL in the top ten.

07dataflow-1403643424680Nick weighted and combined 12 metrics from 10 sources (including IEEE Xplore, Google, and GitHub) to rank the most popular programming languages.

  • Compiled programming languages (Java [#1], C [#2], C++ [#3], C# [#4], Objective-C [#16])
  • Interpreted (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 30: Throw Away That EXPLAIN PLAN! (Part IV)

We tend to overuse the tool—EXPLAIN PLAN in this case—that we are most familiar with. There is an expression—attributed to behavioral scientist Abraham Maslow—that “if all you have is a hammer, everything looks like a nail.” Maslow also tells the story of a drunk searching under a street lamp for his house key, which he had dropped some distance away. Asked why he didn’t look much where he had dropped it, the drunk replied, (more...)

Oracle 12c WITH inline PL/SQL

I've been having a bit of a play with the Oracle 12c database over the past few days and I thought I'd mention a gotcha I encountered.

Of course, oracle-base is a great place to start for clear & concise information on new features and I was trying out some of the WITH clause enhancements (a.k.a. subquery factoring clause). As a developer I'm pretty excited about these in particular.

Creating inline functions (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 29: Throw Away That EXPLAIN PLAN! (Part III)

We tend to overuse the tool—EXPLAIN PLAN in this case—that we are most familiar with. There is an expression—attributed to behavioral scientist Abraham Maslow—that “if all you have is a hammer, everything looks like a nail.” Maslow also tells the story of a drunk searching under a street lamp for his house key, which he had dropped some distance away. Asked why he didn’t look much where he had dropped it, the drunk replied, (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 28: Throw Away That EXPLAIN PLAN! (Part II)

We tend to overuse the tool—EXPLAIN PLAN in this case—that we are most familiar with. There is an expression—attributed to behavioral scientist Abraham Maslow—that “if all you have is a hammer, everything looks like a nail.” Maslow also tells the story of a drunk searching under a street lamp for his house key, which he had dropped some distance away. Asked why he didn’t look much where he had dropped it, the drunk replied, (more...)

Querying InnoDB Tables

Somebody ran into the following error message trying to query the innodb_sys_foreign and innodb_sys_foreign_cols tables from the information_schema database:

ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

It’s easy to fix the error, except you must grant the process privilege. It’s a global privilege and it should only be granted to super users. You grant the privilege (more...)

Boosting APEX menu SQL performance

If you're using dynamic SQL to source your menus and you're utilising the apex_application_pages dictionary view to build you list of menu options - I suggest you consider using Materialized Views.

We have a reporting application that does just this; and the query also links to an authorisation table that decides what types of users see which reports.

You can create a snapshot (materialized view) using something as simple as
create materialized view apx_application_pages
as (more...)

Approximate Count Distinct (12.1.0.2 new feature)

With the release of the Oracle Database 12.1.0.2 there was a number of new features and options. Most of the publicity has been around the in-Memory option. But there was lots of other features for the DBA and a few for the developer.

One of the new SQL functions is the APPROX_COUNT_DISTINCT(). This function is different to the tradition count distinct, COUNT(DISTINCT expression), in that is performs an approximate count distinct. (more...)

Something new in 12c: FETCH FIRST x ROWS

In this post I want to show some example of using a new feature in 12c for selecting the first X number of records from the results set of a query.

See the bottom of this post for the background and some of the reasons for this post.

Before we had the 12c Database if we only wanted to see a subset or the initial set of records from the results of a query we (more...)