Learning is not a spectator sport

Complete listing of posts

  • Avoiding public embarrassment with triggers

    If you create a trigger that does not compile, any part of your application that refers to the table with the invalid trigger is basically dead until that trigger becomes valid. Even if a DML on that table is not going affect any rows, you still wont be able to issue it. For example, SQL> Read more

  • Where is my tracefile

    As a developer, you might have have been reading about SQL trace. After all, we all want to be able to diagnose and improve the performance of the code we write. So if you’ve googled on Oracle performance, you will have no doubt stumbled upon SQL trace. So if you’ve run “alter session set sql_trace Read more

  • Loading file data … easier than you think

    So a request comes in from a colleague… ”Hi Connor.  I’ve created this new table, and I need to populate it with some data.  I’ve got it in Excel – can you help me load it” Now, at this point, my heart normally sinks, because whilst the Oracle tools to load flat file data are Read more

  • Mr DISTINCT might not be your friend

    Whenever you have the need to use the DISTINCT keyword, its worth just pausing for a second, and making sure that you are not hiding just a larger issue. It actually might represent either incorrect use of SQL or incorrect assumptions from the data model. Consider the following example SELECT DISTINCT d.dname FROM emp e, Read more

  • Better SQL via query block names

    Can you make your SQL code easier to understand without adding comments. Can it be self-documenting ? Can you do your bit to make sure your code is easily comprehended by the next person who has to maintain your code ? Yes you can.  Learn how at my next quick tip at https://youtu.be/bfaFT9doqCg Is this Read more

  • Index compression–working out the compression number

    Richard Foote did a series of informative posts on Index Compression which concludes that there is whole lot of positives about index compression, and very little negatives.  But obviously one critical thing is choosing the right number of leading columns to compress. Is it just “take a guess?” .  Luckily, Oracle has taken the guesswork Read more

  • Navigating the world of Oracle database documentation

    If you head on over to http://docs.oracle.com/en/database/ you’ll be both amazed and dismayed by the volume of documentation you can find about the database.  If you’re a seasoned Oracle professional, then you probably dont think twice about finding PIVOT examples in Chapter 18 of the Data Warehousing guide But for the novice, whether it be Read more

  • Joining Oracle

    Last month I joined Oracle after nearly 20 years of working with their technology.  Some people congratulated me on my new role, others told me that going from working solo to working for a massive organisation would be disaster.  In the latter case, this was often associated with an impassioned “But why?” To be honest, Read more

  • Things that are there but you cannot use

    I did a “desc” command on the STANDARD package today, the package that helps define PL/SQL, and saw the XOR function! SQL> declare 2 x boolean; 3 begin 4 x := XOR(true,true); 5 dbms_output.put_line(case when x then ‘TRUE’ else ‘FALSE’ end); 6 7 x := XOR(true,false); 8 dbms_output.put_line(case when x then ‘TRUE’ else ‘FALSE’ end); Read more

  • Continuous Delivery – Moving to SECUREFILE

    You’ve been google-ing and you’ve seen articles (for example) like http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html and you’re pretty pumped about using SECUREFILE features. You’ve got lots of existing CLOB data and moving to SECUREFILE is going to make your life much easier. You’re also excited about the fact that none of your code has to change – you just Read more

  • Continuous delivery…

    “Continuous Delivery (CD) is a software engineering approach in which teams keep producing valuable software in short cycles and ensure that the software can be reliably released at any time” (Source: https://en.wikipedia.org/wiki/Continuous_delivery) Perhaps a simpler definition is “CD is the currently the cool thing to do” Sarcasm aside, there’s a lot of common sense in Read more

  • Partial uniqueness

    I had an interesting request recently from a developer. “ I have a table created as per below create table C_TEST ( col_1 varchar2(3), col_2 varchar2(3), col_3 number ); The rows defined by col_1, col_2, col_3 must be unique but only when col_3 is present. If col_3 is not present, then we allow anything. Hence Read more