Learning is not a spectator sport

Complete listing of posts

  • Kris Kringle the Database – A Foreign Foreign Key Option

    Kris Kringle the Database – A Foreign Foreign Key Option

    You’re probably familiar with the ON DELETE CASCADE option in a foreign key. For example, if I have DEPT and EMP as per below SQL> select * from dept; DEPTNO DNAME LOC ———- ————– ————- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from emp order by… Read more

  • Kris Kringle the Database – The BEST error

    Kris Kringle the Database – The BEST error

    Normally, whenever you get an error it can be cause for concern. But not this one. Let me present to you – the best error message in the entire Oracle documentation library! Tragically, someone evidently didn’t like this little bit of humour because it is now missing from the more recent documentation references Bah humbug… Read more

  • Kris Kringle the Database – The Lazy Date

    Kris Kringle the Database – The Lazy Date

    In the life of a developer, keystrokes matter, and “he/she who dies with the least keystrokes wins”.😃 So it can be frustrating when every time you want to enter a date and you want to make sure you are not beholden to the whim of the session NLS settings , then you need to specify… Read more

  • Kris Kringle the Database – Magic Dates

    Kris Kringle the Database – Magic Dates

    As we all know, when using TO_DATE, you need to make sure that your literal string matches the format mask, otherwise you get an error. For example SQL> select to_date(’01/01/2000′) from dual; select to_date(’01/01/2000′) from dual * ERROR at line 1: ORA-01843: not a valid month SQL> select to_date(‘January 1st 2020′,’DD-MON-YY’) from dual; select to_date(‘January… Read more

  • Kris Kringle the Database – GROUP BY out of sorts?

    Kris Kringle the Database – GROUP BY out of sorts?

    Work colleague Chris Saxon published a post about GROUP BY not guaranteeing that a sort would occur, and concluded (correctly) that the only way to ensure that a query result will be sorted is to have an ORDER BY. Modern versions of the database will use a hashing mechanism to perform a GROUP BY. If… Read more

  • Kris Kringle the Database – Go higher with hierarchies

    Kris Kringle the Database – Go higher with hierarchies

    Most of us are familiar with the CONNECT BY syntax to navigate a hierarchy and the SYS_CONNECT_BY_PATH to see all of the nodes from root to leaf. SQL> select SYS_CONNECT_BY_PATH(ename,’-‘) full_tree 2 from emp 3 start with mgr is null 4 connect by prior empno = mgr FULL_TREE ————————— -KING -KING-BLAKE -KING-BLAKE-JAMES -KING-BLAKE-ALLEN -KING-BLAKE-WARD -KING-CLARK… Read more

  • Kris Kringle the Database – Index Compression

    Kris Kringle the Database – Index Compression

    There is an assortment of compression options available with the Advanced Compression option, but perhaps you are not in the position to invest in that additional license. However, did you know that you can use basic index compression in any version of the database, create index emp_ix on emp ( deptno, empno) compress 1; would… Read more

  • Kris Kringle the Database – WIDTH_BUCKET

    Kris Kringle the Database – WIDTH_BUCKET

    Whether you call it “Kris Kringle” or “Secret Santa“, there’s much pleasure in giving small gifts to people over the Christmas period. So rather than fully fledged blog posts, I’ll wrap up this year with a series of daily quick tips on the Oracle Database. Ho Ho Ho ! If you are ever looking for… Read more

  • Kris Kringle the Database! – Truncate and Indexes

    Kris Kringle the Database! – Truncate and Indexes

    Whether you call it “Kris Kringle” or “Secret Santa“, there’s much pleasure in giving small gifts to people over the Christmas period. So rather than fully fledged blog posts, I’ll wrap up this year with a series of daily quick tips on the Oracle Database. Ho Ho Ho! If you are loading a table, often… Read more

  • UPDATE SET ROW – Convenience Might Be Costly

    UPDATE SET ROW – Convenience Might Be Costly

    A cool feature in PL/SQL is the ability to perform common DML operations without needing to reference the individual columns of a table. The ability to do a “SELECT *” into a ROWTYPE variable dates as far back as Oracle 7, but the benefits of that were limited because the moment the needed to issue… Read more

  • Highlights from the EMEA tour

    Highlights from the EMEA tour

    Just a quick snapshot of the EMEA tour, which I hope inspires you to get involved next year as either a speaker, user group volunteer or even as simply as attending and supporting your local user group. Poland I couldn’t quite understand why my flight to Poland left on time, but still landed 90 mins… Read more

  • SQL*Plus access without an OS login

    SQL*Plus access without an OS login

    When it comes to unloading data, often we would like to run SQL*Plus directly on the database server to avoid the network latency cost of dragging the resultant data down to your client. But (quite correctly) you probably do not have permissions to login to the server, because that opens up a giant security issue.… Read more