Learning is not a spectator sport

Complete listing of posts

  • Kris Kringle the Database – The Christmas MYSTERY

    Kris Kringle the Database – The Christmas MYSTERY

    Here’s a strange but true puzzle for you. See if you can work out the solution… Riddle me this – I created a brand new schema, and in that schema I created a single table called T with a single column called DATA. With some simple PL/SQL I inserted a 40byte string into that table… Read more

  • Kris Kringle the Database – All gather ROUND

    Kris Kringle the Database – All gather ROUND

    Ever thought something should be pretty simple, but turns out it isn’t? A perfect example of this is the topic of todays post… Rounding! A quick look at the Wiki for Rounding discusses (I kid you not) THIRTEEN 🤣 methods of rounding, and that doesn’t include some of the other sub-topics on rounding with different… Read more

  • Kris Kringle the Database – What a Great GROUP

    Kris Kringle the Database – What a Great GROUP

    Often what starts off as a simple aggregation… SQL> select channel_id, promo_id, sum(amount_sold) tot 2 from sh.sales 3 group by channel_id, promo_id 4 order by 1,2; CHANNEL_ID PROMO_ID TOT ———- ———- ———- 2 350 548779.15 2 999 25797563.2 3 350 1329476.49 3 351 1208839.02 3 999 55336945.1 4 350 321125.26 4 351 15664.24 4 999… Read more

  • Kris Kringle the Database! What DAY is it?

    Kris Kringle the Database! What DAY is it?

    This one catches me out more often than I’d like to admit. It all starts off easy – I take a look at what day of the week it is. SQL> select to_char(sysdate,’DAY’) from dual; TO_CHAR(SYSDATE,’DAY’) ———————————— WEDNESDAY No problem. Now I’ll build some code logic around that query in order to run certain tasks… Read more

  • Kris Kringle the Database – Friday RANT

    Kris Kringle the Database – Friday RANT

    Ever seen code like this? select * from EMP where 1=1 and sal > 10 and dept_no = 20 You would probably wonder: Why is there a “WHERE 1=1” predicate? Surely that does nothing? And you would be absolutely right…IT DOES NOTHING. I should clarify – it does nothing, if you were referring to the… Read more

  • Kris Kringle the Database – Taking the LEAP

    Kris Kringle the Database – Taking the LEAP

    What is a quick way of working out if a year happens to be a leap year? Here’s my first attempt SQL> create or replace 2 function f1(y number) return boolean is 3 x date; 4 begin 5 x := to_date(‘2902’||y,’ddmmyyyy’); 6 return true; 7 exception 8 when others then return false; 9 end; 10… Read more

  • Kris Kringle the Database – No Fault in DEFAULT

    Kris Kringle the Database – No Fault in DEFAULT

    A lot of people are unaware that when you have a column with a DEFAULT attached to it, you do not need to go hunting around in the data dictionary to get access to it. For example, let me start with this simple people table where the default for ACTIVE is “YES” SQL> create table… Read more

  • Kris Kringle the Database – Know your ABC

    Kris Kringle the Database – Know your ABC

    If you open up the SQL reference manual and scroll down through the commands… …you will start at ADMINISTER and eventually you will land on UPDATE, the last SQL statement in the documentation. But here’s a pop quiz for you. Is there a command that comes after UPDATE that didn’t make it into the docs?… Read more

  • Kris Kringle the Database – Get the HINT

    Kris Kringle the Database – Get the HINT

    One of my favourite quotes from Maria Colgan is: “Optimizer hints should only be used with extreme care” Let me give you an example of why that is the case. As anyone would know, one of the key aims of anyone building an application running on a database is the minimize risk. We have things… Read more

  • Kris Kringle the Database – Getting the BAND together

    Kris Kringle the Database – Getting the BAND together

    Ever heard of a BAND join? It’s not actually new, it has been around since 12.2. Jonathan Lewis wrote up a nice post on it was back in 2017 but in a nutshell MERGE join done without a band join SQL> select 2 e1.last_name|| ‘ has salary roughly between ‘|| e2.last_name comparison 3 from 4… Read more

  • Kris Kringle the Database – Sequence Values

    Kris Kringle the Database – Sequence Values

    It is commonplace to use a sequence to populate a surrogate key in your table. Whether its a manually created sequence or an IDENTITY column (which is still backed by a sequence), most of us know that we can use the RETURNING clause to collect the value that was used on INSERT. SQL> insert into… Read more

  • Kris Kringle the Database – Why NVL is smart

    Kris Kringle the Database – Why NVL is smart

    NVL smarts A common issue for reports and any query where users can pass parameters is how to handle the “optional” parameter. Here’s a typical example: “Table CP can be queried where column X is equal to optional parameter P.” Should we code: select * from CP where ( X = 😛 or 😛 is… Read more