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...)
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 firstname.lastname@example.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...)
- 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.
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...)
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...)
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...)
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...)
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.
- Compiled programming languages (Java [#1], C [#2], C++ [#3], C# [#4], Objective-C [#16])
- Interpreted (more...)
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...)
Somebody ran into the following error message trying to query the
innodb_sys_foreign_cols tables from the
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...)
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
With the release of the Oracle Database 220.127.116.11 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...)
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...)