Learning is not a spectator sport

Complete listing of posts

  • 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

  • SQL Macros – complex SQL made easy. Elapsed time calculation

    SQL Macros – complex SQL made easy. Elapsed time calculation

    The more you play with SQL macros, the more you find simple but powerful use cases which will make life so much easier as a developer. In the “good ol’ days” we only had the DATE data type in the Oracle database, and finding the duration in seconds between two dates is a breeze, because Read more

  • The big table dilemma – what about transaction rates?

    The big table dilemma – what about transaction rates?

    Yesterday I posted some demonstrations of the impressive power of indexes in relational databases for data retrieval even when those tables get massive in size. That resulted in a few comments on Twitter and on the post itself regarding the other half of the “equation”, namely, that my test was potentially biased in favour of Read more

  • When does a table become too big?

    When does a table become too big?

    I happened across an interesting thread on Twitter today which posed the question about when you should intervene with alternative solutions as a table gets larger over time: It is always good practice to be proactive on thinking about potential future problems before they occur because obviously it is better to solve them before they Read more

  • Formula 1 data–staying up to date

    Formula 1 data–staying up to date

    Just an addenda to what I posted last week about downloading the Formula 1 motor racing data where I provided a script that would download the ANSI SQL compressed script decompress it and convert the ANSI SQL population script to an Oracle version drop the existing tables in your schema build and populate new versions Read more

  • Database World 2021 – now it is your turn

    Database World 2021 – now it is your turn

    Thanks for attending my talk on Futuristic SQL during Database World.If you missed it, or want to rewatch it at your own pace and run the demos alongside whilst you watch, you can view it here You can run all the demos I presented in your own database simply by downloading my demo script from Read more

  • APEX demos – Formula 1 Racing Data ?

    APEX demos – Formula 1 Racing Data ?

    Naturally there was an overwhelming urge to title this post “The Need for Speed” and sprinkle Maverick and Goose references throughout it, but since Top Gun is about fighter jets and not about motor racing I resisted that urge. If you have been attending any of the APEX Office Hours sessions of late, you will Read more

  • Quick and easy sample data

    Quick and easy sample data

    If you head over to the official database sample schema github repo you’ll find all the standard schemas that you’ll typically see features demonstrations on. There are seven schemas in total, namely HR OE PM IX SH BI CO If you’re are dinosaur like myself you’ll recall that the sample schemas used to be distributed Read more