My students asked if you could embed an OFFSET x ROWS FETCH NEXT y ROWS ONLY clause in a SQL Server T-SQL user-defined function. The answer is no, it isn’t Oracle (yes, you can do that in Oracle Database 12c with an NDS statement). There’s an example in Chapter 2 of my Oracle Database 12c PL/SQL Programming book if you’re interested.
All, Its time to be at AIOUG stage once again and the event would be the OTNYathra 2015. The event has been a great success in the past and has been able to generate/receive wide recognition and appreciation. OTNYathra focuses to evangelize the Oracle technologies to a broader and passionate audience. OTNYathra is a grand tour which is conducted … Continue reading →
There’s a problem most people who’ve had to do production datafixes have encountered at some point: you need to update a dataset. Unfortunately there’s a trigger on the target table. The trigger either prevents the update outright or just has some unwanted side effects (firing off a business process, etc.). The trigger is necessary for the application to function correctly, so you can’t just drop it.
How do you apply the changes?
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.
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:
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.
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: