The Girl With The ANSI Tattoo

I enjoyed the David Fincher remake of The Girl With The Dragon Tattoo more than I thought I would. Rather than a shallow and cynical Hollywood cash-in, it's actually a tense, atmospheric, only slightly voyeuristic crime thriller. My favourite part, though, was when Lisbeth Salander begins to solve a 40 year old murder cold case using SQL.

We see her tapping at her laptop as she hacks effortlessly into the Swedish police database, interspersed with green-tinted tracking shots of scrolling text as she types in keywords like 'unsolved' and 'decapitation', though never quite the whole query:

Naturally I couldn't help stitching a few screenshots together in Photoshop, and this is what I got:

Immediately moviegoers will notice that this can't be Oracle SQL - obviously the AS keyword is not valid for table aliases. In fact as we pull back for a thrilling query results listing we see the mysql prompt and giveaway use [dbname] connect syntax and over-elaborate box drawing.

Notice we can just make out the 'FT' of an ANSI left join to the Keyword table.

Finally we get a full-screen shot of the results listing for Västra Götaland:

Here's what we were able to reconstruct in the Oracle WTF Forensics department:

SELECT DISTINCT v.fname, v.lname, i.year, i.location, i.report_file
FROM   Incident AS i
       LEFT JOIN Victim AS v on v.incident_id = i.id
       LEFT JOIN Keyword AS k ON k.incident_id = i.id
WHERE  i.year BETWEEN 1947 AND 1966
AND    i.type = 'HOMICIDE'
AND    v.sex = 'F'
AND    i.status = 'UNSOLVED'
AND    ...
       OR v.fname IN ('Mari', 'Magda')
       OR SUBSTR ...
AND    (k.keyword IN ('rape', 'decapitation', 'dismemberment', 'fire', 'altar', 'priest', 'prostitute')
       ...
       AND SUBSTR(v.fname, 1, 1) = 'R' AND SUBSTR(v.lname, 1, 1) = 'L');

+--------+---------+------+-----------+----------------------------------+
| fname  | lname   | year | location  | report_file                      |
+--------+---------+------+-----------+----------------------------------+
| Anna   | Wedin   | 1956 | Mark      | FULL POLICE REPORT NOT DIGITIZED |
| Linda  | Janson  | 1955 | Mariestad | FULL POLICE REPORT NOT DIGITIZED |
| Simone | Grau    | 1958 | Goteborg  | FULL POLICE REPORT NOT DIGITIZED |
| Lea    | Persson | 1962 | Uddevalla | FULL POLICE REPORT NOT DIGITIZED |
| Kajsa  | Severin | 1962 | Dals-Ed   | FULL POLICE REPORT NOT DIGITIZED |
+--------+---------+------+-----------+----------------------------------+

Shocked moviegoers will have been left wondering why a genius-level hacker would outer-join to the Victims and Keywords tables only to use literal-text filter predicates that defeat the outer joins, whether MySQL has a LIKE operator, and why none of the victims' initials are 'R L'.

How to Merge a Row

The tough challenge that seems to have been faced by this developer was that the ID, name and value passed into the procedure needed to be either applied as an update if the name existed, or else inserted as a new row. You might think you could just use MERGE, or maybe attempt the update, capturing the ID value with a RETURNING clause, then if that found no rows insert a new row using seq_somethings.NEXTVAL for the ID. But wait, that wouldn't be complicated enough, would it?

Here's the table:

create table something
( id               integer  not null constraint pk_something primary key
, name             varchar2(100)
, publicsomething  number   default 0  not null );

Here's what they came up with:

PROCEDURE SaveSomething(pId              IN OUT something.id%TYPE,
                        pName            IN something.name%TYPE,
                        pPublicSomething IN something.publicsomething%TYPE) IS
     counter NUMBER;
BEGIN
     SELECT COUNT(rowid)
     INTO   counter
     FROM   something c
     WHERE  LOWER(c.name) = LOWER(pName);

     IF counter > 0 THEN
          SELECT id
          INTO   pId
          FROM   something c
          WHERE  LOWER(c.name) = LOWER(pName);
     END IF;

     IF (pId IS NOT NULL AND pId > 0) THEN
          UPDATE something
          SET    id              = pId,
                 name            = pName,
                 publicsomething = pPublicsomething
          WHERE  id = pId;

     ELSE
          SELECT seq_somethings.NEXTVAL
          INTO   pId
          FROM   dual;

          INSERT INTO something
               (id, name, publicsomething)
          VALUES
               (pid, pname, ppublicsomething);
     END IF;

EXCEPTION
     WHEN OTHERS THEN
          -- log the details then throw the exception so the calling code can perform its own logging if required.
          log_error('PK_ADMIN.SaveSomething',
                    USER,
                    SQLCODE || ': ' || SQLERRM);
          RAISE;
END SaveSomething;

Thanks Boneist for this. By the way she mentioned she counted 6 WTFs, "some more subtle than others". I'm not sure whether we're counting the stupid redundant brackets around the IF condition (drives me crazy), the novel 5-character indent or the design WTF in which the "name" column is expected to be unique but has no constraint or indeed index. I'm definitely counting SQLCODE || ': ' || SQLERRM though.

Concatenation, Concatenation, Concatenation

I'm still not sure what this one does, but you have to be impressed by 11 nested CONCATs.

(And by the way, you also have to be impressed by the inventor of the CONCAT function who evidently considered two arguments sufficient, unlike, say LEAST, GREATEST, DECODE, COALESCE and BIN_TO_NUM. But not NVL. Who knows what goes through these people's heads.)

PROCEDURE ins_xyz
   ( p_xyz_id_out OUT NUMBER,
     p_input_array IN myarrayrectype )
IS
BEGIN
   p_xyz_id_out := NULL;

   BEGIN
      INSERT INTO xyztab
         (
            xyz_id,
            xyz_11,
            xyz_12,
            xyz_13,
            xyz_21,
            xyz_22,
            xyz_23,
            xyz_31,
            xyz_32,
            xyz_33,
            xyz_41,
            xyz_42,
            xyz_43,
            xyz_43_concatenated
         )
      VALUES
         (
            xyz_seq.NEXTVAL,
            p_input_array.xyz_11,
            p_input_array.xyz_12,
            p_input_array.xyz_13,
            p_input_array.xyz_21,
            p_input_array.xyz_22,
            p_input_array.xyz_23,
            p_input_array.xyz_31,
            p_input_array.xyz_32,
            p_input_array.xyz_33,
            p_input_array.xyz_41,
            p_input_array.xyz_42,
            p_input_array.xyz_43,
            SUBSTR(
              CONCAT(
                CONCAT(
                  CONCAT(
                    CONCAT(
                      CONCAT(
                        CONCAT(
                          CONCAT(
                            CONCAT(
                              CONCAT(
                                CONCAT(
                                  CONCAT(
                                    p_input_array.xyz_11 || ' ',
                                    p_input_array.xyz_12 || ' '),
                                  p_input_array.xyz_13 || ' ' ),
                                p_input_array.xyz_21 || ' ' ),
                              p_input_array.xyz_22 || ' ' ),
                            p_input_array.xyz_23 || ' ' ),
                          p_input_array.xyz_31 || ' ' ),
                        p_input_array.xyz_32 || ' ' ),
                      p_input_array.xyz_33 || ' ' ),
                    p_input_array.xyz_41 || ' ' ),
                  p_input_array.xyz_42 || ' ' ),
                p_input_array.xyz_43 ),
            1, 512 )
         )
      RETURNING xyz_id INTO p_xyz_id_out;
   EXCEPTION
      WHEN OTHERS THEN NULL;
   END;
END ins_xyz;

Thanks BB for this one, which she or he (I can't say more for witness protection reasons) sent me a while ago and I almost forgot about.

I didn't post it at the time because I couldn't understand what it did. Looking at it again though, that's all part of the fun. Here's part of the conversation we had about it:

Me: Thanks BB - love it. I'm slightly puzzled by p_input_array though. Is it an array?

BB: An array of records.

Me: Yikes. So what does the target table look like? I suppose each 'xyz_nn_' column must be a nested table.

BB: In the actual system they're parts of node tuples. xyz_11, xyz_12, xyz_13, all indicate "scores" for pairings of the first node with 1, 2, 3, respectively. Hard to explain without giving away too much about the system. However, they're scalars.

Me: Glad we got that cleared up. Can I say parts of node tuples without endangering your job at NASA?

Explain this

On the subject of cryptic OTN posts, this one has to get an honorary mention as well:

explain this

hi,

write query to find out order detail of oder_date 2 year before (sorry i forget exact question)

No solutions so far.

Make Me One With Everything

Seen on OTN Forums recently (part of a question entitled "HTML not working in PL/SQL block", so I suppose we were warned):

l_col VARCHAR2(30) := to_number(to_char(to_date('01-feb-2011','dd-mon-yyyy'),'dd'));

So the string '01-feb-2011' becomes first a date, then a string again, then a number, before being assigned to a string variable. Much more interesting than boring old

l_col VARCHAR2(30) := extract (day from date '2011-02-01');

Or even,

l_col VARCHAR2(30) := '1';

Interview questions

A friend recently had a telephone interview for an Oracle technical contract role. Here are the questions he was asked:

1. What is the command to edit a crontab?
2. What are the first and fourth parameters on the crontab?
3. What is the command to email the list of files that are too big and need to be deleted to prevent a tablespace getting too big?
4. Have you used the OLAP command? and who invented it?
5. When do you set PCTFREE?
6. When is the PGA in the SGA?
7. Where is the Java pool?
8. How do I stop a checkpoint when I commit?

The £10 UKOUG Weak Joke Challenge

Oracle-WTF will pay the sum of £10 to the first person who makes the following weak Brummie joke to a conference audience at UKOUG:

Are there any Brummies here today?

Is it true that Ozzy Osbourne thought the Spice Girls were astronauts?

(Note for visitors to England: it's about the accent. And The Spice Girls used to be a pop group. And Ozzy Osbourne, oh never mind.)

Now where are those user accounts?

The IM conversation below is part of a much longer one (notice the date stamps) between a friend who we'll just call 'TR' and a developer.

Developer (11 Oct 2009 14:39:51): I created some users and now they are gone?
TR (11 Oct 2009 14:40:01): We have implemented a daily flashback to the data baseline so that repeatable tests can run every day in that database.
TR (11 Oct 2009 14:40:03): You need to notify us (as per the mail I sent out) when you make data changes that you want to keep from day to day.
TR (11 Oct 2009 14:40:06): Ok, so could you please create those users again and let me know? I'll create a new baseline for the refresh....
Developer (11 Oct 2009 14:45:51): i wonder if i ll be able this afternoon
Developer (11 Oct 2009 14:46:12): so i can do it tomorrow and send you the list
Developer (11 Oct 2009 14:46:25): You can go ahead wit the refreh of today without my users
TR (11 Oct 2009 14:48:29): Ok, I don't need the list, just to know once you have created them.
Developer (11 Oct 2009 14:50:18): ok

Developer (20 Oct 2009 16:57:53): hi TR
TR (20 Oct 2009 16:57:59): Hi
Developer (20 Oct 2009 16:58:06): Are you still doing the DB refresh on daily basis?
TR (20 Oct 2009 16:58:20): Yes. It's automatic, I don't actually *do* anything.
Developer (20 Oct 2009 16:58:24):
Developer (20 Oct 2009 16:58:27): ok
Developer (20 Oct 2009 16:58:33): then
Developer (20 Oct 2009 16:59:22): i see
Developer (20 Oct 2009 17:01:42): actually i m looking for this user on alpha qa2_PN3D8J20aa
Developer (20 Oct 2009 17:01:52): i can't find it in the db
Developer (20 Oct 2009 17:02:04): and when i m logged in with it, I added it yesterday and now it's gone
Developer (20 Oct 2009 17:03:02): i ll try using other users
TR (20 Oct 2009 17:16:21): You didn't tell me that you had created these users. The database is refreshed every night back to the baseline...as we discussed
TR (20 Oct 2009 17:16:21): If you add data you have to let me know and I will create a new baseline.

TR (21 Oct 2009 16:08:49): These users that you need. Are they in the database now?
Developer (21 Oct 2009 16:09:02): not yet
Developer (21 Oct 2009 16:09:07): but i can ping them to you
Developer (21 Oct 2009 16:09:12): at least the login
TR (21 Oct 2009 16:09:19): You don't need to ping them to me. Just tell me when they're created
Developer (21 Oct 2009 16:09:25): ok
Developer (21 Oct 2009 16:09:52): but got too much to do today probably will have them ready monday morning
TR (21 Oct 2009 16:10:20): Ok, so as per last time....when they are created please let me know.
Developer (21 Oct 2009 16:10:30): ok

Developer (25 Oct 2009 13:18:21): hi TR
TR (25 Oct 2009 13:18:27): Hi
Developer (25 Oct 2009 13:18:31): what's time is the DB refresh taking time ?
TR (25 Oct 2009 13:18:41): 00:00GMT
Developer (25 Oct 2009 13:18:44): ok
Developer (25 Oct 2009 13:18:52): i ll ping you by the end of the day my new users
TR (25 Oct 2009 13:18:57): Ok, you don't need to ping me the users, just create them and tell me when you have done it
Developer (25 Oct 2009 13:19:01): in the mean time
Developer (25 Oct 2009 13:19:25): I'm working on a script to insert our users in the Db before each time
TR (25 Oct 2009 13:19:39): Ok, you don't need to do that, just create them and tell me when you've done it.
Developer (25 Oct 2009 13:19:48): so this will help us lot and you will be free to do your updates as you want and delete our users if you need to
TR (25 Oct 2009 13:20:02): Ok great. But the process is already working, you just have to tell me once you've created them and they will always be there
Developer (25 Oct 2009 15:00:26): hi TR
Developer (25 Oct 2009 15:00:34): what do i have to give you about the created users? only login
TR (25 Oct 2009 15:00:43): nothing, just tell me when you've create them.
Developer (25 Oct 2009 15:00:48): or Zid, Xid...
Developer (25 Oct 2009 15:00:54):
TR (25 Oct 2009 15:00:54): just tell me WHEN they are created...so I can add them to the baseline.
Developer (25 Oct 2009 15:01:04): today
TR (25 Oct 2009 15:01:17): they are there now?
Developer (25 Oct 2009 15:01:21): not yet, but I will create these users
Developer (25 Oct 2009 15:02:07): ppm_alpha_4 ppm_alpha_5
Developer (25 Oct 2009 15:02:21): ppm_alpha_2 ppm_alpha_3 ppm_alpha_4 ppm_alpha_5
Developer (25 Oct 2009 15:02:21): please don't delete them this time

If at first you don't succeed...

...then try again. Then try again more 125 times. Then quit.

PROCEDURE get_id
    ( p_id_out         OUT NUMBER
    , p_name_in        IN VARCHAR2
    , p_create_user_in IN VARCHAR2 )
IS
    v_new_id      NUMBER := 0;
    v_max_tries   PLS_INTEGER := 127;
    v_default_id  NUMBER := 0;
BEGIN
    v_new_id := lookup_id(p_name_in); -- will be 0 if not found

    WHILE v_new_id = 0 AND v_max_tries > 0
    LOOP
        BEGIN
            INSERT INTO entry
            ( entry_id
            , entry_name
            , create_date
            , create_user
            , create_app
            , mod_date
            , mod_user
            , mod_app)
            VALUES
            ( entry_seq.NEXTVAL
            , p_name_in
            , SYSDATE
            , p_create_user_in
            , 'get_id'
            , SYSDATE
            , p_create_user_in
            , 'get_id' )
            RETURNING entry_id INTO v_new_id;

        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    
        v_max_tries := v_max_tries - 1;
    END LOOP;

    p_id_out := v_new_id;
END get_id;

Thanks BB for sending this.

The Undocumented "/1000" currency formatting function

Forum question:

Hi,

How can I format currency values to shorthand?

i.e. how can I display 12500 as 12.5, 2700 as 2.7, 700 as 0.7 etc?

I have tried using various masks but can't achieve the results I'm looking for.

That's a tough one. How to make 700 into 0.7? Could there be some Oracle feature to help with this?

Two quick replies later:

Thanks for the replies guys

I wasnt aware of the "/1000" feature, but it has done exactly what I need.

Oracle needs to do more to promote these display format features. What else are they hiding? That's what we want to know.