Learning is not a spectator sport

Complete listing of posts

  • A justification for referential integrity constraints

    Yes, I know what you’re thinking. “Here we go again.  Another database dude about to get stuck into us telling us about the importance of foreign keys for data integrity” and yes, I could easily do that.  But you’ve seen that before (and by the way, it’s of course still totally valid – you should Read more

  • Countdown to Sydney

    I’m speaking at the Sydney leg on the OTN APAC tour on November 30th, 394 Lane Cove Rd, North Ryde This is a great event with local and international speakers all giving their time and knowledge for free to help you with your Oracle technology. If you’re in Sydney, then come along for some great Read more

  • CLOBS, from 11g to 12c

    If you are a regular user of LOB’s in the database, take care when you switch from 11g to 12c.  Notice the subtle difference: In 11g, you are permitted to, and recommended to, use SECUREFILE lobs, but they are not the default SQL> select name, value 2 from v$parameter 3 where name = ‘db_securefile’; NAME Read more

  • You dont need that sequence number

    I’ve lost track of the number of times I see this sequence (no pun intended) of actions in code: SELECT my_sequence.nextval INTO :some_variable FROM dual; INSERT INTO my_table VALUES (:some_variable, ….); My question is always “Why?” What was it that made you so desperate in need of that sequence value that you needed it before Read more

  • A new line on NEWLINE

    Recently I was doing a simple external table load using a CSV file, but was getting an interesting error. My file looked simple enough “ID”,”EMAIL”,”TIMESTAMP”,”SUBJECT”,”STATUS”,”STATUS_TS” “2012348048”,”john@anon.com”,”05/02/2000″,”Subject 1″,”5″,”09/04/2007″ “2412348048”,”mike@anon.com”,”05/02/2000″,”Subject 1″,”5″,”09/16/2002″ “348543169051”,”sue@anon.com”,”03/10/2001″,”Subject 1″,”5″,”03/24/2008″ “348396029762”,”mary@anon.com”,”03/10/2001″,”Subject 1″,”5″,”03/10/2001″ “1212348047”,”sam@anon.com”,”05/02/2000″,”Subject 1″,”5″,”05/02/2000″ “1612348048”,”vincent@anon.com”,”05/02/2000″,”Subject 1,”5″,”06/02/2006″ … … So it should have been a fairly straightforward external table definition to access it SQL> create Read more

  • The power of metaphor

    We had a question on AskTom the other day, asking us to explain what a “latch” was. A simple metaphor sometimes does the trick   Read more

  • Datapump cleanup

    Sometimes if you’ve been building data pump jobs via PL/SQL, you might get some part of it incorrect, and thus the job is left in the state of “DEFINING”, ie, you are building it but never managed to complete the process.  An interesting anomaly is that when this happens, your current session struggles to clean Read more

  • SQL*Plus hints and tips

    I just posted a video on how I get the most out of my SQL*Plus usage.  You can check it out here I reference various elements of my login.sql file throughout the video – so here’s login.sql file – feel free to utilise any parts of it for your benefit. col column_name format a30 col Read more

  • Existence checks

    Often, to do an existence check for data, people issue a “SELECT COUNT(…)” against the relevant table. So in pseudo-code, the logic looks like something like this: select count(*) into my_variable from MY_TABLE where COL = if my_variable > 0 then … For unique key lookups, then that’s fine, but it’s a risky strategy as Read more

  • Passing parameters

    I had an interesting question from a previous work colleague. She had a need to perform a query on attribute pairs. For the sake of discussion, we’ll assume that pairs are a shopping centre name, and a checkout aisle in that shopping centre. So for example, on screen, a user may say: Show me the Read more

  • Why I love working on AskTom

    Today a question came in, and Swapnasis provided a nice simple test case, which makes my job so much easier.  Here’s a snippet from it SQL> create table TTT( 2 testid integer not null, 3 value integer not null, 4 time timestamp not null, 5 unique(testid, time) 6 ); Table created. See line 5 ? Read more

  • My sessions at OpenWorld 2015

    OpenWorld is just around the corner, and I’m thrilled to be there again this year – it’s my first year as an Oracle employee, so it will be interesting to compare and contrast with previous years as an Ace Director.  I’ve got three conference sessions – I hope you can be there, and please feel Read more