Learning is not a spectator sport

Complete listing of posts

  • Silent data corruption – constraints spanning multiple rows

    Silent data corruption – constraints spanning multiple rows

    One of the toughest problems in database applications is validation of data that extends “across” rows. We have declarative constraints for many of the validations we need to do for a single row, eg “I must be unique” “I must have a non-null value in this column” “I must be a valid member of the… Read more

  • New Podcast Episode – The Optimizer with Nigel Bayliss

    New Podcast Episode – The Optimizer with Nigel Bayliss

    No topic quite gets emotions rolling as much as the Oracle Query Optimizer. The fact that a piece of software can determine the best way to run the myriad of potential SQL statements, no matter how poorly they sometimes might be written, never ceases to amaze me. But conversely, the moment the optimizer does not… Read more

  • Careful with SQL*Plus and SQL_ID feedback

    Careful with SQL*Plus and SQL_ID feedback

    Just a quick word of warning on a cool feature of SQL*Plus that came along from 18c onwards. I’ve posted before about using supplied database PL/SQL routines to derive the SQL_ID from a SQL statement. In SQL*Plus, that feature is built into the software itself, using an extension to SET FEEDBACK. Here’s an example of… Read more

  • UTL_HTTP, secure hosts and ORA-24263 certificate errors

    UTL_HTTP, secure hosts and ORA-24263 certificate errors

    Security eh? It is just one of those little things that is good to have in a database. The “problem” with security is that it often adds complexity to your environment, but I’d rather have a little extra complexity than have my personal details splattered all over the dark web in some database of the… Read more

  • New Podcast Episode! Why every DBA should learn APEX

    New Podcast Episode! Why every DBA should learn APEX

    The very name of the product – Application Express (APEX) – suggests that this is a tool solely for building applications, and thus it should belong solely in the realm of developers. But that is underselling the ability of APEX to be a perfect use case for a different IT demographic – the Database Administrator.… Read more

  • Wrapping up 2021

    Wrapping up 2021

    Last year I started my 2020 wrap up with “With all the challenges of 2020…” with a perhaps overly optimistic view that 2021 would be a return to normal. Silly me .  Thus I’ll begin my 2021 wrap up with… With all the challenges of 2021, I hope you have still got value out of… Read more

  • Twas the night before … log4j

    Twas the night before … log4j

    ‘Twas the week before Christmas, and all through the world, Many developers were stirring, as a nightmare unfurled. The log4j library, a simple tool just for logging, Could suddenly be used to give your servers a flogging This wasn’t like the movies, where super machines were needed, and the hackers wore hoodies, and warnings had… Read more

  • Christmas SQL

    Christmas SQL

    Just a re-post of something I put on StackOverflow last year so I can refer back to it easily. What better way to celebrate Christmas than with SQL select replace(replace(replace(r,’X’,chr(27)||'[42m’||chr(27)||'[1;’||to_char(32)||’m’||’X’||chr(27)||'[0m’), ‘T’,chr(27)||'[43m’||chr(27)||'[1;’||to_char(33)||’m’||’T’||chr(27)||'[0m’), ‘@’,chr(27)||'[33m’||chr(27)||'[1;’||to_char(31)||’m’||’@’||chr(27)||'[0m’) from ( select lpad(‘ ‘,20-e-i)|| case when dbms_random.value < 0.3 then substr(s,1,e*2-3+i*2) else substr(substr(s,1,dbms_random.value(1,e*2-3+i*2-1))||’@’||s,1,e*2-3+i*2) end r from ( select rpad(‘X’,40,’X’) s,rpad(‘T’,40,’T’) t from… Read more

  • Detecting if your database is multitenant

    Detecting if your database is multitenant

    Just a quick tip for this post today. I had a question come in asking how to detect whether a database was running as a container database.  My quick response was to use SYS_CONTEXT(‘USERENV’,’CON_ID’) for which the values will be 0 – this database is not configured as a container database at all (tisk tisk… Read more

  • SQL*Plus shortcut for fast data unload

    SQL*Plus shortcut for fast data unload

    I published a video a few days ago on the various tools you can use to unload data out of the Oracle Database to a flat file format (CSV in this particular case). I compared SQLcl, SQL*Plus, UTL_FILE and even Pro*C to look at getting the data spooled out as efficiently as possible. You can… Read more

  • The Importance Of Testing

    The Importance Of Testing

    In last weeks’ Office Hours session, I posted an “impossible” challenge to two of the most well credentialed Oracle community members on the subject of Testing. Samuel Nitsche and Jacek Gebal bravely undertook a live session of building unit tests for a sight-unseen application. I provided a data model, some DDL and a PL/SQL package… Read more

  • Testing versus the Database

    Testing versus the Database

    A phrase I’ve often used when discussing database design is “insurance policy”, namely, when I talk about adding CHECK constraints, FOREIGN KEY constraints, UNIQUE constraints, stringent data types, the list goes on… there is sometimes push back from application development teams along the lines of “We already do these checks in the application code, so… Read more