Working Notes

I recently had the misfortune of having to insert several URLs into an Oracle database. This trivial activity was made more challenging by having to deal with URL parameters, which were delimitated with an ampersand (&). Inserting URL data is usually pretty easy. But any operation I try where the URL has an ampersand, SQL Developer prompts me for an input value: That's not what I want at all! Here's a couple different ways to get around this: SET DEFINE OFF; Works if you aren't going to define any variables in the session SET ESCAPE ON;   AND  add a forward slash (\) in front of the ampersand;  String concatenation is my least fav, but it works in a pinch.  NOTE: Application Express avoids this issue completely by using colons instead of ampersands.

If you are familiar with the database platforms mostly found on Windows platforms, you're know that  you can set a default value for a column. This is commonly referred to as an Identity column. This feature was not available in Oracle, until the release of 12c. Let's see how it's done. 0) set your container (yes, I've been playing with Pluggable Databases). NOTE: before we go any farther, you will need two roles assigned to create a table with an identity column: CREATE TABLE  CREATE SEQUENCE When you thought about it, that was pretty obvious, wasn't it. 1) create table, with a column defined like so: 2) Insert some data like so: NOTE: these statements will cause errors still: The first statement fails, because the order of columns is not specified even though we have a default value specified for the identity column. Identity columns are traditionally the first column, but they don't have to be. The second and third statement fails...

The following is another example of how row-limiting clause can be applied. Row limiting clauses can be any select statements, this includes CREATE TABLE AS SELECT statements. In the following example, we are able to create a new table in our PeopleSoft database by selecting the top 10% of rows by EMPLID. You may recall from previous posts that CTAS operations in Oracle 12c include statistics. This is true for operations which include the Row-Limiting clauses as well. As you can see, we have a new table, with a brand new set of statistics to go along with it. 

I have the extreme pleasure to work with several PeopleSoft applications. Keen readers of this blog series may have noticed this already; plus 10 Internet points if you did. PeopleSoft makes create use of effective dated rows. (note to self, write a blog post on managing effective dated rows some day).  When querying a table with effective dates, we usually end up with a query like so:   In this case, we are selecting the most recent row according to the maximum effective date and the maximum effective sequence. One might expect that the explain plan for this could be terrible, but it's actually not bad. We indexed properly. Actually, this is a pretty good explain plan, so good in-fact that I wasn't sure how Oracle would improve on this, but I wouldn't have got this far if there wasn't some good pay off at the end. We can re-write this SQL to use the row-limiting clause, and order by to get the same result, like so: At the very least, this looks cle...

With Oracle 12c, you can limit the number of rows returned, not only by specifying the number of rows, but also by specifying the percent of total number of rows in the table. Before we dive into examples, let's think about that for a second. With Oracle 12c, you can manage your result set based on information the database already knows about your data. The database already makes decisions on how to best retrieve data (hello optimizer!), but now developers and DBAs can being to take direct advantage of this information. Gnarly. The syntax for row-limiting clauses by present looks a lot like row-limiting clause by row. This query will return the top 1% of rows in the PSOPRDEFN table, as ordered by the LASTSIGNONDTTM.  You can also offset by a particular number of rows, and then return a percentage, like so:  At this time you can NOT offset by a percentage. This makes pagination for percentages a bit more challenging, as you must know the previous number of r...

For several versions now, other database platforms, such as MySQL and Microsoft SQL Server have been able to select the first x  number of rows; Oracle has not, until 12c. Over the next several days, and several posts, I'm going to discuss implementation and use cases based on this particular feature. Before Oracle 12c, row limits had to be done like this:  This works, mostly, but there were some challenges, such as plan management, data set unpredictability, and generally, it's just confusing to look at. I'm not afraid to say, this seems 'hack-ish'. Let's not even talk about pagination.  Now with 12c, we can replace nested SELECT statements and rownum with this instead:  Same basic premise, return only the first three rows, based on the ORDER BY sorting. Pretty simple actually.  And if you want to page through the results? We can do that too:  In this query, we skip the first row, and return the second row only. If you are thin...

Oracle 11g introduced the invisible indexes feature. Oracle 12c now introduces DBAs and developers to use of invisible columns. To create an invisible column, define a table as you normally would, and add the INVISIBLE flag, like so: In this case, the HOME_PHONE column is invisible. But what does that mean? Can you insert into the invisible column? Yes! What happens if you try to SELECT from the table? It's not there. What happens if I try to specifically select the invisible column? Well, now it's visible. WTF? So what good is this slightly invisible column? Invisible columns are NOT a security measure. They are a means of allowing developers a means to support activities where data can be stored in the database without obvious access to the data. What? No, seriously, this is a real thing. And Oracle has a feature which uses this functionality (of course). Temporal Validity . Check it out, it's pretty sweet, if Information Life Cycles are you...

Microsoft SQL Server has a nifty function called IS_NUMERIC which will return a Boolean if the parameter passed is a numeric value. It's long been lamented that Oracle has no similar function..but it's all just a lie. While technically true that Oracle does not have an IS_NUMERIC function, it does have support for regular expressions. Oracle released regular expressions in 10g, which seems like eons ago. The following regular expression can be added to an SQL script in Oracle to test for a numeric value: REGEXP_LIKE (col1,'^[[:digit:]]+$'); where col1 is the column you wish to test. select * from tab1 where REGEXP_LIKE (col1,'^[[:digit:]]+$'); will return all the rows in table TAB1 where COL1 matches the test from the regular expression.