Learning is not a spectator sport

Complete listing of posts

  • Raw partitions?

    Raw partitions?

    Here’s a quirky one for you. It can happen when you are dealing with a partitioned table where the partition key is defined as RAW. To be honest, I really can’t think of a reason why you ever want a table with a raw partition key. (If you have one, please let me know in… Read more

  • Take care with automatic indexes

    Take care with automatic indexes

    This one came in via an AskTOM question. You need to be careful when disabling constraints that are underpinned by an index, especially when it comes to the storage for that index. Let’s take a look at a simple example. I’ll explicitly nominate the LARGETS tablespace for my index that will support the primary key.… Read more

  • Capture all DDL run on a database instance

    Capture all DDL run on a database instance

    I posted a video a couple of days ago showing a trigger mechanism to customize the capture of DDL that is issued on your database. The aim here is to be more generous with letting developers execute DDL on their Development databases, whilst still having a thorough record of the changes that are occurring. Of… Read more

  • PL/SQL – Don’t mix and match scope

    PL/SQL – Don’t mix and match scope

    Here’s a simple little PL/SQL block where we call an inner procedure PARAMETER_TESTER from its parent block. Pay particular attention to the parameter we pass to the procedure, and it’s value throughout the execution of that procedure. SQL> set serverout on SQL> declare 2 3 glob_var int := 0; 4 local_var int; 5 6 procedure… Read more

  • Quick and easy masking

    Quick and easy masking

    I had a request from a client a while back regarding masking of data. They had an application with sensitive data in the Production environment (where access and audit were very tightly controlled) but the issue was how to respect that sensitivity in non-Production environments whilst still preserving full size data sizes for application testing.… Read more

  • APEX Upgrade redux

    APEX Upgrade redux

    I posted about my APEX upgrade to 19 yesterday, and someone was quick to point out to me that they believed I hadn’t covered all of the steps. “What if your APEX instance needs to call web services?” they said. “You need to update your Access Control Lists.” I hadn’t thought of that, so I… Read more

  • Application Express 19.1

    Application Express 19.1

    AskTOM moved to Application Express 19.1 without any major issues last weekend. That in itself is a nice endorsement for APEX, given that the AskTOM application dates back nearly 20 years to 2001, and predates even the existence of APEX. The only fix that we had to make was that AskTOM uses the static CDN… Read more

  • 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