Learning is not a spectator sport

Complete listing of posts

  • India bound !

    As part of the activities organised by the Oracle Ace Program and the All India Oracle User Group, the OTN Yathra tour is coming up in a few weeks. I’ll be speaking on 12c developer features and advanced SQL techniques at the following locations: 23-Apr-16 Saturday,  Chennai 24-Apr-16 Sunday, Bangalore 26-Apr-16 Tuesday, Hyderabad 28-Apr-16 Thursday,… Read more

  • The death of the demo script

    On AskTom, one of the things we always strive to do is provide demo scripts in our answers where appropriate.  That way, people can see our line of thought, and hopefully get an understanding as to how we arrived at a solution.  And there’s two very important concepts in that last sentence: our line of… Read more

  • Views as “stored text”

    You’ll often see comments that views are not copies of the data, they are merely the stored text of a query that defines. This is by and large true, but don’t forget, this is not  the same as saying we simply take whatever text you give us, and store it. Let’s take a look at… Read more

  • Database dictionary corruption ? Maybe not.

    At first glance, this looks like a major drama. Some sort of database dictionary corruption, or internal error. But we’ll see, it’s actually fine. Let’s do a simple describe on an existing table SQL> desc MY_TABLE Name Null? Type —————————– ——– ——————– EMPNO NUMBER(38) ENAME VARCHAR2(30) HIREDATE DATE DEPTNO NUMBER(38) SAL NUMBER(38) Now of course,… Read more

  • The first matching row

    I was recently asked when presented with a query along the lines: SQL> select * from 2 ( select x 3 from t1 4 where x = :b1 5 union all 6 select x 7 from t2 8 where x = :b1 9 ) 10 where rownum = 1 11 / and asked – will… Read more

  • Tightening up your data model

    Having NOT NULL constraints declared on columns that are genuinely not nullable not only is good practice, but can yield performance gains. Here’s a little routine that lists those columns that probably need a NOT NULL constraint. It looks at all columns and check to see if a constraint with definition “COLUMN_NAME” IS NOT NULL… Read more

  • TOP-N histograms on 12c

    I had an interesting question on AskTom today, with a nicely provided test case !!!,  so I thought I’d share it here with some additional commentary. The basic premise of the TOP-N histogram in 12c is that when the number of distinct values in a column exceeded the number of available buckets for histograms, the… Read more

  • 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