Learning is not a spectator sport

Complete listing of posts

  • Bulk processing with FORALL and INSERT-SELECT

    Bulk processing with FORALL and INSERT-SELECT

    I’m sure most of us have read or heard at a conference the benefits of array fetching and array binding when it comes to passing data back and forth to the database. And we’ve all seen the numerous demo scripts in PL/SQL along the lines of: FORALL i in 1 .. n INSERT … As Read more

  • Do not set DETERMINISTIC on functions unrealistically

    Do not set DETERMINISTIC on functions unrealistically

    I’m feeling very determined on this one. Yes I have a lot of determination to inform blog readers about determinism, and yes I have run out of words that sound like DETERMINISTIC. But one of the most common misconceptions I see for PL/SQL functions is that developers treat them as if they were “extending” the Read more

  • Long running scheduler jobs

    Long running scheduler jobs

    One of the nice things about the job scheduler in the Oracle database is the easily interpreted interval settings you can apply for job frequency. The days of cryptic strings like “sysdate+0.000694444” when all you really wanted to say was “Just run this job every minute” are a thing of the past. I covered how Read more

  • All of my Oracle Magazine articles

    All of my Oracle Magazine articles

    Generally my blog is just snippets of tech content that take my interest as I encounter them (most commonly when looking at AskTOM). If I think they’ll be useful, I’ll just plonk them out right there and then. If you prefer your content in longer (and more structured ) form, then also I publish longer Read more

  • External table preprocessor on Windows

    External table preprocessor on Windows

    There are plenty of blog posts about using the pre-processor facility in external tables to get OS level information available from inside the database. Here’s a simple example of getting a directory listing: run_os.bat ========== @echo off cd \oracle dir /b SQL> create table fs_size ( 2 disk varchar2(64) 3 ) 4 organization external 5 Read more

  • Connections with a wallet – redux

    Connections with a wallet – redux

    Wow…it is nearly 4 years ago now that I wrote an article on connecting to the database via a wallet to avoid having to hard code passwords into script. That article is here: https://connor-mcdonald.com/2015/09/21/connection-shortcuts-with-a-wallet/ So I went to do a similar exercise on my new 18c Windows database today, and to my surprise things went Read more

  • Disabled EZCONNECT

    Disabled EZCONNECT

    Just a normal start to the day today…I had my coffee and then started working on some AskTOM questions. Naturally pretty much the first thing I needed to do is connect to my database, and then this happened: C:\oracle\product\18\bin>sqlplus scott/tiger@//gtx:1518/pdb1 SQL*Plus: Release 18.0.0.0.0 – Production on Wed Mar 6 09:23:09 2019 Version 18.5.0.0.0 Copyright (c) Read more

  • Use V$SQLSTATS not V$SQL to find expensive SQL

    Use V$SQLSTATS not V$SQL to find expensive SQL

    It’s a holiday here today in Perth, so a very brief blog post because I’ve been busy doing other things today When I’m contacted by customers or friends and asked to help out with tuning their applications, probably the two most common issues are one or two poorly performing SQL statements, or the server being Read more

  • Worth the wait

    Worth the wait

    Yes, I know it’s been awhile Yes, I know people have been angry at the delay But, can we put that behind us, and rejoice in the fact…that YES It’s here! Yes, 18c XE for Windows is now available. https://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html Most probably, most developers in your organization are running a Windows PC. Now every single Read more

  • Statistics on Object tables

    Statistics on Object tables

    Way back in Oracle 8.0 we introduced the “Object-Relational” database, which was “the next big thing” in the database community back then. Every vendor was scrambling to show just how cool their database technology was with the object-oriented programming paradigm. Don’t get me wrong – using the Oracle database object types and features associated with Read more

  • MERGE and ORA-30926

    MERGE and ORA-30926

    Just a quick blog post on MERGE and the “unable to get a stable set of rows” error that often bamboozles people. This is actually just the script output from a pre-existing YouTube video (see below) that I’ve already done on this topic, but I had a few requests for the SQL example end-to-end, so Read more

  • The death of UTL_FILE – part 2

    The death of UTL_FILE – part 2

    I wrote a post a while back call “The Death of UTL_FILE”, and probably because of it’s click-bait title I got lots of feedback, so I’m back to flog that horse . Seriously though, I stand behind my assertion in that post, that the majority of usages of UTL_FILE I’ve seen my career are mimicking Read more