Learning is not a spectator sport

Complete listing of posts

  • FOLLOWS clause

    In early Oracle releases, you could have 1 trigger per type of trigger per table (eg if you had a before-row-insert trigger, you could have only 1). Now there is no limit. But that does raise the question – if I have “n” BEFORE ROW triggers on a table, in which order do they fire Read more

  • Is a year a leap year ?

    This post seems timely given that yesterday was Feb 29. In almost every case I can think of, you should be relying on native Oracle date functions to perform any kind of date arithmetic. This is perhaps one of the very very few exceptions SQL> set timing off SQL> create or replace 2 function is_leap_year1(y Read more

  • Loading LOB from a file

    I observed this idiosyncracy recently when loading some lob from external files using PL/SQL: First we’ll create a file to be loaded, and ‘wc’ tells us it is 75 bytes in size SQL> !echo “This is line 1” > /tmp/lobfile SQL> !echo “This is line 2” >> /tmp/lobfile SQL> !echo “This is line 3” >> Read more

  • Amped on Amper

    This “problem” rates in the top 10 all time viewed questions on AskTom, and it demonstrates the importance of reading the Concepts guide with Oracle SQL> create table CARTOONS ( name varchar2(30)); Table created. SQL> insert into CARTOONS values (‘Tom & Jerry’); Enter value for jerry: And the question comes in: “How can I insert Read more

  • How can I see my invisible columns

    A cool new feature in 12c is the ability to make a column invisible.  The concept has existed since 8i to handle things like “set unused” and function based indexes, but now it is available to developers directly. SQL> create table T ( c1 int, c2 int ); Table created. SQL> desc T Name Null? Read more

  • Equi-sized partitions on random data

    Had an interesting AskTom question today about dynamically keeping partition sizes in check. The poster had large table, already partitioned, but since there was no logical column to do a range partition on, it was partitioned on a surrogate primary key value. Due to the volatility of the data, (with a BLOB column on the Read more

  • Something new learned every day

    One of the reasons I leapt at the chance to be on the AskTom team when we resurrected the site, was that it’s like free training.  You get questions on topics you have not visited before, and you get new angles on things you thought you already knew. Just today, someone posted a question about Read more

  • On building SQL

    I had a fun question on AskTom over the weekend, that of, how to display a monthly calendar for any provided date using just SQL. You can see the question and the answer here But I thought it might be worth explaining the process.  Of course, the way I approach the problem might be totally Read more

  • DEFAULT SEQ.NEXTVAL in 12c

    One of the cool things in 12c is that (finally after all these years) a sequence can be assigned as the default value for a column. Of course, some may say “big deal” – after all, it is trivial to create a simple trigger to do the job.  And perhaps, we “crafty” folks at Oracle Read more

  • Never rely on an assumed order

    We’ve (hopefully) all had it drummed into us enough times – you cannot assume the order of results from queries unless you explicitly include an ORDER BY statement. Here’s another trivial example of this- I was doing a little demo script for an AskTom question. Here’s the script running in 11.2.0.4 SQL> create table T Read more

  • EXCHANGE PARTITION revisited

    A while back I did a blog post showing that when you have set a column to UNUSED, it still “counts” as a validation step when doing exchange partition.  So if you had a partitioned table that previously had a column set to unused, then the candidate table to be exchanged in also had to Read more

  • Public / private cursors

    As we all know (hopefully), we should always deploy named program units (procedures/functions) within packages. Its a great method of encapsulation of logic. However, not many are aware that its not just procedures and functions that can have a public (package spec) and private (package body) component. Cursors can also be defined in the same Read more