Mmm, π

Young rz.He over on Stack Overflow has a question about why SQL*Plus doesn't parse every line of a PL/SQL block in case it's a comment in order to avoid unnecessary prompts for substitution variables. Here's his mcve:

SQL> begin
  2      null; -- &commented_out
  3  end;
  4  /
Enter value for commented_out: wtf

PL/SQL procedure successfully completed.

Oh, wait - it's not that. It's actually this:

CREATE OR REPLACE PROCEDURE cal_circle AS
-- DECLARE

  pi              CONSTANT NUMBER := 3.1415926;
  radius          NUMBER := 3;

  -- to make it more dynamic I can set 
  -- radius NUMBER := &enter_value;

  circumference   DECIMAL(4,2) := radius * pi * 2;
  area            DECIMAL(4,2) := pi * radius ** 2;

BEGIN

  -- DBMS_OUTPUT.PUT_LINE('Enter a valur of radius: '|| radius);
  dbms_output.put_line('For a circle with radius '
   || radius
   || ',the circumference is '
   || circumference
   || ' and the area is '
   || area
   || '.');
END;
/

But anyway.

An excellent answer explains all about clients and servers and procedure parameters and why it's really not SQL*Plus's job to pre-parse everything before sending it to the database and it wouldn't be such a great idea if it did. I voted up. But what really baked my noodle was the opening comment (my emphasis):

You can use a parameter instead of a substitution variable to allow different users to call the procedure with different values of pi.

...with a fixed version of the code posted as an example...

CREATE OR REPLACE PROCEDURE CAL_CIRCLE
  ( P_RADIUS IN NUMBER, P_PI IN NUMBER )
AS
  CIRCUMFERENCE DECIMAL(4, 2) := P_RADIUS * P_PI * 2;
  AREA          DECIMAL(4, 2) := P_PI * P_RADIUS ** 2;

BEGIN
  DBMS_OUTPUT.put_line('For a circle with radius '
                       || P_RADIUS
                       || ', the circumference is '
                       || CIRCUMFERENCE
                       || ' and the area is '
                       || AREA
                       || '. ' || 'Calculated with Pi = ' || P_PI);
END;

You will agree that is much more flexible. Now we can call it for a conventional π, like this:

SQL> call cal_circle(3, 3.1416);
For a circle with radius 3, the circumference is 18.85 and the area is 28.27. Calculated with Pi = 3.1416

Or like this:

SQL> call cal_circle(3, acos(-1));
For a circle with radius 3, the circumference is 18.85 and the area is 28.27. Calculated with Pi = 3.1415926535897932384626433832795028842

But what if we need to switch back to the imperial π following our exit from the EU, or the chancellor reduces the UK's π in order to stimulate economic growth, or we want to use the biblical π for religious reasons, or if we simply want to use the same procedure in an alternative universe with a fundamentally different geometry than our own? No problem:

SQL> call cal_circle(3, 2.71828);
For a circle with radius 3, the circumference is 16.31 and the area is 24.46. Calculated with Pi = 2.71828

Now that is reusability. (The radius can't be more than 9.99, but come on, you can't have everything.)

As usual, of course, the easy way is no fun:

create or replace function circumference
    ( p_radius in number )
    return number
    deterministic
as
begin
    return p_radius * 6.2831853071795864769252867666;
end circumference;

(with something similar for area - at which point you might consider putting them both into a circle package). Or, slightly more fun:

create or replace type circle as object
( radius         number
, diameter       number
, circumference  number
, area           number
, constructor function circle(p_radius number) return self as result );
/

create or replace type body circle
as
    constructor function circle
        ( p_radius number )
        return self as result
    is
    begin
        self.radius := p_radius;
        self.diameter := self.radius * 2;
        self.circumference := self.radius * 6.2831853071795864769252867666;
        self.area := 3.1415926535897932384626433833 * radius ** 2;

        return;
    end;
end;
/

SQL> select circle(3) as mycircle from dual;

MYCIRCLE(RADIUS, DIAMETER, CIRCUMFERENCE, AREA)
----------------------------------------------------
CIRCLE(3, 6, 18.8495559, 28.2743339)

1 row selected.

The power of scripting

So your system has a neat automated archive and purge function for your rolling partitions, driven by the PART_RETENTION table which holds the table name, partition type ('DAILY', 'MONTHLY', 'QUARTERLY' or 'YEARLY') and how many of each to keep. As a general rule, you want 35 dailies, 13 month-ends, 5 quarter-ends and 2 year-ends for each table. Let's say you have ten tables. All you need is a handy script to set that up. This is what someone actually came up with. (This is just an example. There were a lot more tables).

insert into part_retention (table_name, partition_type, retention ) values 'COUNTRIES', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'DEPARTMENTS', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'EMPLOYEES', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'JOBS', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'JOB_HISTORY', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'LOCATIONS', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'REGIONS', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'SALES', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'ORDERS', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'ORDER_ITEMS', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'CUSTOMERS', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'COUNTRIES', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'DEPARTMENTS', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'EMPLOYEES', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'JOBS', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'JOB_HISTORY', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'LOCATIONS', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'REGIONS', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'SALES', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'ORDERS', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'ORDER_ITEMS', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'CUSTOMERS', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'COUNTRIES', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'DEPARTMENTS', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'EMPLOYEES', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'JOBS', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'JOB_HISTORY', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'LOCATIONS', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'REGIONS', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'SALES', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'ORDERS', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'ORDER_ITEMS', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'CUSTOMERS', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'COUNTRIES', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'DEPARTMENTS', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'EMPLOYEES', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'JOBS', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'JOB_HISTORY', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'LOCATIONS', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'REGIONS', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'SALES', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'ORDERS', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'ORDER_ITEMS', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'CUSTOMERS', 'YEARLY', 2);

Looping the hard way

The task was to construct partition names from 'P001' to (for some reason) 'P336', as part of a larger maintenance script. Here's what they came up with:

declare
   p varchar2(4);
   i number := 1;
begin
   loop
      if i < 10 then
         p := 'P00' || to_char(i);
      elsif i < 100 then
         p := 'P0' || to_char(i);
      else
         p := 'P' || to_char(i);
      end if;
      
      i := i + 1;
      
      exit when i > 336;

      dbms_output.put_line(p);
   end loop;
end;

e_howdidIdeservethis

A friend has found himself supporting a stack of code written in this style:

DECLARE
   e_dupe_flag EXCEPTION;
   PRAGMA EXCEPTION_INIT(e_dupe_flag, -1);

BEGIN
   ...

EXCEPTION
   WHEN e_dupe_flag THEN
      RAISE e_duplicate_err;

  etc...

Because, as he says, coding is not hard enough.

This reminded me of one that was sent in a while ago:

others EXCEPTION;

"I didn't know you could do that" adds our correspondent.

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    (  k.keyword IN
          ('rape', 'decapitation', 'dismemberment', 'fire', 'altar', 'priest', 'prostitute')
        OR v.fname IN ('Mari', 'Magda')
        OR 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, and whether MySQL has a LIKE operator.

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?