Oracle WTF

Mmm, π

2017-10-14T12:31:00.002+01:00

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

2017-10-07T09:15:00.000+01:00

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

2014-01-05T11:46:00.000+00:00

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

2013-06-01T08:51:00.000+01:00

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

2012-05-23T07:35:00.001+01:00

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

2012-05-19T14:20:00.001+01:00

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

2011-03-26T09:08:00.000+00:00

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

2011-03-12T15:04:00.000+00:00

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

2011-03-12T14:52:00.000+00:00

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

2010-01-21T07:26:00.003+00:00

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

What is the command to edit a crontab?
What are the first and fourth parameters on the crontab?
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?
Have you used the OLAP command? and who invented it?
When do you set PCTFREE?
When is the PGA in the SGA?
Where is the Java pool?
How do I stop a checkpoint when I commit?

The £10 UKOUG Weak Joke Challenge

2009-11-29T13:44:00.000+00:00

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?

2009-10-31T15:21:00.003+00:00

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...

2009-09-02T11:57:00.002+01:00

...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

2009-05-03T12:32:00.001+01:00

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.

How to talk your way out of a hole

2009-04-10T08:31:00.001+01:00

One last shot from our favourite consultant:

Be careful what you expect from this proof of concept. We can’t prove the performance will match the requirements, and I would argue that performance isn’t a function of this architecture, it’s a function of technology.
The issue is not the architecture, it’s that [this company] doesn’t have the technology. I would even argue that the technology required might not exist yet.
We can say that indicatively if the technology did exist, then this architecture would hit that performance requirement.

Let's hear it for architecture astronauts.

Rollback segments explained

2009-03-20T07:57:00.004+00:00

I recently read this in a book about data warehousing:

Source System Rollback Segments
When extracting from a relational source, extracts that take a long time can be problematic. If an extract asks for all records updated in the last 24 hours, the system must locate the appropriate set of records. This means that no user can change the updated_date field while your query is being processed. As transactions flow in during your query, they are queued up in a separate place called a rollback segment, to be applied once your request is finished. If your query takes too long, this queue gets too large and runs out of space. The system then kills your job and processes the transactions that have been queued up. In general, the folks responsible for the transaction system don't like this kind of behavior.

Now to be fair, the book was published in 1998 and is not specifically about Oracle. Does anyone know whether there has ever been a commercial DBMS that worked anything like this, or did they just make it up?

The Consultant on Backups

2009-03-14T17:30:00.001+00:00

Our correspondent overheard The Consultant sorting out the backup requirements for the new system:

Consultant: You have a 6 hour window overnight, now as the queues get longer under heavy loading the end of day queue clearing will run into that 6 hours. Your backup window will start to get squeezed, so we need to know the minimum time to back-up this amount of data, including the time to shut down and start up the databases.

Technical guy: Why?

Consultant: Because it has to take place in that 6 hour window.

Technical guy: Why is that?

Consultant: Because then the users will come back on line and want to use the system.

Technical guy: So?

Consultant: They can't use the system if it's down to be backed up.

Technical guy: We'll use an online backup and they can do whatever they like.

Consultant: Well, if I was in auditing I'd fire you right now. You simply have to shut a database down to back it up. It's the only way you can get a consistent backup.

The Consultant

2009-02-17T07:22:00.003+00:00

The Consultant has a Ph. D, vast experience of high-performance systems architecture, a black belt in karate and a reputation as a genius. He's been brought in by senior management at vast but necessary expense for a strategic rethink of the way data is shared between systems, while implementing SOA, improving performance and finding the Higgs Boson. Needless to say, he tends towards the view that database development is overrated. He's already sorted out the data warehouse. Overheard by our correspondent:

I’ve finished the design for the data warehouse. Although I say design, it’s pretty simple. That’s why it was so quick. All data warehouses are essentially the same in that they are a dimensional model. That means that you essentially have everything that is a fact, an immutable fact [waves arms expressively], in the fact table. Just the one, big, table. That’s why they’re so attractive as reporting solutions - everything is in the same place so it’s easy to understand and the reporting is easy to automate. So in that fact table you’ve got all trades, the cashflows, positions, accounting information, accounts, exceptions, counterparties. Anything that’s a fact goes in that table [does wide googly eyes expression with dramatic pause]. Then anything derived is called a dimension, like for instance P&L calculations, whether the account is on balance sheet, or off... they go in the dimension table. Basically all we have to do is just pump messages into that fact table from the bus and then recalculate the dimensions in the dimension table periodically, and that’s the technical job. Getting the facts in there and getting the calculations done.

More Fake Performance Tips

2008-11-01T07:43:00.008+00:00

We're not sure if this is a joke or just (more likely) the work of an idiot. Here are 15 Tips for better performance and tuning in Oracle SQL and PL/SQL:

FTS (Full Table Scans) are always bad and Index usage is always good.

Usage of dynamic SQL within the stored procedure code is always bad even for search procedures.

Empty Space in an index that gets created due to the DML operations do not get used.

Indexes should be rebuilt at regular intervals.

Indexes and statistics are the same thing. Also, histograms are needed only on indexed columns.

Usage of cursors is always bad so avoid them like the plague.

Truncate command cannot be rolled back because it is a non-logged operation.

Table variables in SQL Server are always only memory resident.

Column order in a covered index does not matter.

In the case of SQL Server, one can separate the clustered index from the table.

Only committed data gets written to the disk.

Logical I/Os (LIO) are not a cause of concern, only Physical IO (PIO) are.

Count(1) is better performing than count(*).

Issue frequent commits in the application to make the transaction faster and also improve concurrency.

Views are evil evil DB Objects that always slow down performance.

We like the fact that 8, 9 and 10 appear to be about SQL Server, despite the heading. Probably Nawal could only think of 12 fake Oracle tips but thought nobody would notice. Can you help him out with some more misleading tips for Oracle? (Or SQL Server. Nobody will notice.) For example,

The buffer cache hit ratio is a reliable indicator of system performance.
Bitmap indexes are perfect for columns with a small number of distinct values, like 'Y' and 'N'.
The Inuit have fifty words for snow.

Spotted by Michel Cadot on Oracle-L.

Update: Yong Huang pointed out that the source of the article appears to be a list of common myths posted on a SQL Server blog, which makes it slightly less funny than it first seemed. Oh well.

TGI g_friday

2008-08-01T11:57:00.003+01:00

Found in a package body:

g_friday CONSTANT VARCHAR2(6) := 'Friday';

...then a couple of hundred lines later:

if to_char(business_date,'fmDay') = g_friday then
    ...end-of-week processing...
else
    ...regular processing...
end if;

Now that's flexible. If end-of-week processing is ever moved to the weekend, all you have to do is set g_friday := 'Saturday'.

Auxiliary Constructs Appeal

2008-06-21T23:24:00.005+01:00

Will somebody give this guy some auxiliary constructs? He just needs to know what's the auxiliary constructs, and examples in the auxiliary constructs. So if you have any auxiliary constructs you don't need, now's the time to dig deep. The appeal starts here.

Fine tuning

2008-04-27T20:15:00.002+01:00

Mike is doing some work on an application that started life as SQL Server. Now that it has been converted to Oracle, there are one or two bits that could still do with a little fine tuning.

One particular procedure seemed to take rather a lot of time, and several developers had tried to get better performance out of it without much success. Here it is:

CREATE OR REPLACE PROCEDURE getupdatedrunids
    ( p_customer_id  IN VARCHAR2 DEFAULT NULL
    , pc_results_out IN OUT SYS_REFCURSOR )
AS
    v_fetch_status     INTEGER := 0;
    v_sql_status       INTEGER;
    v_fetch_status1    INTEGER := 0;
    v_sql_status1      INTEGER;
    v_event_id         VARCHAR2(50);
    v_runid            NUMBER(10, 0);
    v_count_run_conns  INTEGER := 0;
    v_temp_runs        INTEGER;

BEGIN
    DELETE FROM temp_runs;

    DELETE FROM temp_run_connections;

    INSERT INTO temp_runs
         ( run_id )
    SELECT DISTINCT i.run_id
    FROM   event_status i
         , run_status    b
    WHERE  i.run_id = b.run_id
    AND    i.event_id IN
           ( SELECT DISTINCT i.event_id
             FROM   event_status i
                  , run_status   b
             WHERE  i.run_id = b.run_id
             AND    b.customer_id = p_customer_id
             GROUP  BY i.event_id
             HAVING COUNT(i.run_id) > 1)
             AND    b.customer_id = p_customer_id;

    BEGIN
        v_temp_runs := 0;
        SELECT COUNT(*)
        INTO   v_temp_runs
        FROM   dual
        WHERE  EXISTS
               ( SELECT *
                 FROM   temp_runs );
    END;

    IF v_temp_runs > 0 THEN
    
        DECLARE
            CURSOR c_runs IS
                SELECT DISTINCT run_id
                FROM   temp_runs;
        BEGIN
            OPEN c_runs;
            FETCH c_runs INTO v_runid;

            IF c_runs%NOTFOUND
            THEN
                v_sql_status1   := 2;
                v_fetch_status1 := -1;
            ELSE
                v_sql_status1   := 0;
                v_fetch_status1 := 0;
            END IF;
        
            WHILE v_fetch_status1 = 0
            LOOP
                BEGIN
                    DECLARE
                        CURSOR cust_incidents_cs IS
                        
                            SELECT DISTINCT i.event_id
                            FROM   event_status i
                            WHERE  i.run_id = v_runid
                            AND    i.rejected = 0;
                    BEGIN
                        OPEN cust_incidents_cs;
                    
                        FETCH cust_incidents_cs INTO v_event_id;

                        IF cust_incidents_cs%NOTFOUND
                        THEN
                            v_sql_status   := 2;
                            v_fetch_status := -1;
                        ELSE
                            v_sql_status   := 0;
                            v_fetch_status := 0;
                        END IF;

                        <<i_loop1>>
                        WHILE v_fetch_status = 0
                        LOOP
                            BEGIN
                                INSERT INTO temp_run_connections
                                     ( run_id
                                     , connectedids)
                                SELECT DISTINCT v_runid
                                     , i.run_id AS connectedids
                                FROM   event_status i
                                WHERE  i.run_id < v_runid
                                AND    i.event_id = v_event_id
                                AND    i.rejected = 0
                                AND    i.run_id IN
                                       ( SELECT DISTINCT run_id
                                         FROM   temp_runs );

                                <<fetchnext>>
                                FETCH cust_incidents_cs INTO v_event_id;

                                IF cust_incidents_cs%NOTFOUND
                                THEN
                                    v_sql_status   := 2;
                                    v_fetch_status := -1;
                                ELSE
                                    v_sql_status   := 0;
                                    v_fetch_status := 0;
                                END IF;
                            END;
                        END LOOP;

                        CLOSE cust_incidents_cs;
                    END;
                
                    FETCH c_runs INTO v_runid;

                    IF c_runs%NOTFOUND
                    THEN
                        v_sql_status1   := 2;
                        v_fetch_status1 := -1;
                    ELSE
                        v_sql_status1   := 0;
                        v_fetch_status1 := 0;
                    END IF;
                END;
            END LOOP;
            CLOSE c_runs;
        END;
    END IF;

    SELECT COUNT(*)
    INTO   v_count_run_conns
    FROM   temp_run_connections;

    IF v_count_run_conns > 0
    THEN
        OPEN pc_results_out FOR
            SELECT DISTINCT run_id, connectedids
            FROM   temp_run_connections;
    END IF;

END getupdatedrunids;

His final version was much faster. See if you can spot the difference:

CREATE OR REPLACE PROCEDURE getupdatedrunids
    ( p_customer_id  IN VARCHAR2 DEFAULT NULL
    , pc_results_out IN OUT SYS_REFCURSOR )
AS
BEGIN
    OPEN pc_results_out FOR
        SELECT DISTINCT e2.run_id, ic.run_id AS connectedids
        FROM   run_status r1
             , run_status r2
             , event_status e1
             , event_status e2
        WHERE  r1.customer_id = p_customer_id
        AND    r2.customer_id = r1.customer_id
        AND    e1.run_id = r2.run_id
        AND    e1.rejected = 0
        AND    e2.run_id = r1.run_id
        AND    e2.event_id = e1.event_id
        AND    e2.run_id > e1.run_id
        AND    e2.rejected = 0
        ORDER BY 1, 2;

END getupdatedrunids;

WREAK_APPLICATION_HAVOC

2008-02-10T12:35:00.000+00:00

Tom Kyte recently blogged about the senseless and yet strangely common practice of coding something like this:

WHEN OTHERS THEN
   RAISE_APPLICATION_ERROR(-20001,'Following Error Occured:' || SQLERRM);

which fairly obviously achieves nothing except take a standard message like this:

ORA-06501: PL/SQL: program error
ORA-06512: at line 6

and pointlessly scramble it into this:

ORA-20001: Following Error Occured:ORA-06501: PL/SQL: program error
ORA-06512: at line 11

which adds some meaningless text, hides the original line number, and miss-spells "occurred". Not bad for a day's work.

It turned out that some people had been doing this for years because they were simply too stupid to realise that they didn't have to.

Anyway you know all this because you read Tom Kyte's blog. But have a look at this helpful page of advice from Tech On The Net, under "Oracle/PLSQL: SQLERRM Function"

You could use the SQLERRM function to raise an error as follows:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error
      (-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);

      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;

Now that is even better. The first example adds some meaningless text, hides the original line number, and duplicates the error code (unless it's a NO_DATA_FOUND exception, but let's not go there), to produce something like this:

ORA-20001: An error was encountered - -6501 -ERROR- ORA-06501: PL/SQL: program error
ORA-06512: at line 11

The error logging example pointlessly captures SQLCODE (nobody will ever use it), throws away all but the first 200 characters of the error stack, logs nothing about what happened, and fails to re-raise the exception so if you don't check the log you won't know anything went wrong until your customers start asking where their stuff is.

Wouldn't it be great if there were, say, a Boolean third parameter to RAISE_APPLICATION_ERROR that would make it retain the existing error stack, freeing up the message line for you to put something intelligent and helpful, like, I don't know,

BEGIN
    RAISE program_error;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR
        ( -20001
        , 'Biscuits cannot be ordered on a ' || TO_CHAR(SYSDATE,'fmDay') ||
          ' without a hot beverage'
        , TRUE);
END;

to produce something like this:

ORA-20001: Biscuits cannot be ordered on a Sunday without a hot beverage
ORA-06512: at line 5
ORA-06501: PL/SQL: program error

We can but dream.

Frameworkia

2007-10-26T08:33:00.000+01:00

We thought long and hard about possible titles for this new PL/SQL development standard proposed on OTN, but we couldn't improve on the one it came with.

I want share a new IDEA to create a new standard PL/SQL developing:
Function ( Standard Buffer) return number
    variable Number;
    variable1 Varchar2;
begin
    variable := get from Standard Buffer;
    variable1 := get from Standard Buffer;

    { make your business }

    put in standard buffer your results
end;
Give me feedback if you are interested at the new STANDARD called "FRAMEWORKIA".

A lot possibilities are ready.

Do you see the genius of it?

Er, no.

Sensing that there were people who still needed convincing, user601181 posted some sample code developed using the new Frameworkia:

CREATE OR REPLACE FUNCTION iacore
    ( eobufferia IN OUT typeeodata )
    RETURN NUMBER
IS
    CURSOR cur_getroutingcondition
        ( idc_workflow IN VARCHAR2
        , idc_operation_node IN VARCHAR2 ) IS
        SELECT *
        FROM   wf_condition
        WHERE  id_workflow = idc_workflow
        AND    id_operation_node = idc_operation_node;

    rec_getroutingcondition cur_getroutingcondition%ROWTYPE;

    CURSOR cur_dobufferiaassign
        ( idc_workflow IN VARCHAR2
        , idc_operation_node IN VARCHAR2 ) IS
        SELECT *
        FROM   wf_assignement
        WHERE  id_workflow = idc_workflow
        AND    id_operation_node = idc_operation_node;

    rec_dobufferiaassign cur_dobufferiaassign%ROWTYPE;

    next_node         NUMBER;
    next_node_ck      NUMBER;
    stop_node         NUMBER;
    operation         VARCHAR2(256);
    operation_call    VARCHAR2(256);
    type_node         VARCHAR2(32);
    workflow          VARCHAR2(32);
    line              VARCHAR2(256);
    status_wf_v       VARCHAR2(3);
    pid_chain_node    NUMBER;
    ia_tid            VARCHAR2(64);
    ia_tid_micro      VARCHAR2(64);
    ret_code_default  NUMBER;
    ret_code          NUMBER;
    retval1           NUMBER;
    statementexc      VARCHAR2(256);
    schema_function   VARCHAR2(32);
    package_function  VARCHAR2(32);
    dblink_function   VARCHAR2(32);
    first_node_flag   VARCHAR2(2) := 'NO';
    id_debug_source   NUMBER;
    mapin_keyp        VARCHAR2(1024);

    headerbufferia typebufferia;
    assignbufferia typebufferia;
    checkbufferia  typebufferia;

    rec_wfnode               wf_node%ROWTYPE;
    rec_wffunctionsourcecode wf_function_source_code%ROWTYPE;
    rec_wflogger             wf_logger%ROWTYPE;
    rec_wfbusiness           wf_business%ROWTYPE;
    rec_wffieldmapping       wf_fieldmapping%ROWTYPE;
BEGIN
    headerbufferia := eobufferia(1);

    workflow := frameworkia.getvalue(headerbufferia,'ID_WORKFLOW');

    ---- DETERMINO QUALE NODO INVOCARE
    pid_chain_node := frameworkia.getvalue(headerbufferia,'WF_NODE_ID');

    ----- SE IL NODO E' NULL ALLORA E' IL PRIMO NODO
    IF pid_chain_node IS NULL
    THEN
        -------DETERMINO HANDLER E FILENAME PER IL LOGGER
        SELECT *
        INTO   rec_wflogger
        FROM   wf_logger
        WHERE  id_workflow = workflow;
        -- rec_WfLogger.ID_WORKFLOW
        -- rec_WfLogger.ID_DEBUG_LEVEL
        -- rec_WfLogger.ID_DIRHANDLER
        -- rec_WfLogger.ID_FILENAME

        --------INSERISCO NELL'HEADER
        frameworkia.setvalue
        ( headerbufferia
        , 'ID_DEBUG_WF'
        , rec_wflogger.id_debug_level );

        frameworkia.setvalue
        ( headerbufferia
        , 'ID_DIRHANDLER'
        , rec_wflogger.id_dirhandler );

        frameworkia.setvalue
        ( headerbufferia
        , 'ID_FILENAME'
        , rec_wflogger.id_filename );

        frameworkia.setvalue
        ( headerbufferia
        , 'CHARACTER_EVIDENCE'
        , '§§§§§§§§§§§§§§§§§§§§' );

        -------DETERMINO L'ID NODE
        SELECT wf_node_id
        INTO   pid_chain_node
        FROM   wf_node
        WHERE  id_workflow = workflow
        AND    wf_first_node = 'YES';

        SELECT *
        INTO   rec_wfnode
        FROM   wf_node
        WHERE  id_workflow = workflow
        AND    wf_first_node = 'YES';

        frameworkia.setvalue
        ( headerbufferia
        , 'WF_NODE_ID'
        , rec_wfnode.wf_node_id );

        SELECT b.status
        INTO   status_wf_v
        FROM   wf_node a
             , wf_name b
        WHERE  a.id_workflow = workflow
        AND    a.wf_node_id = rec_wfnode.wf_node_id
        AND    a.id_workflow = b.id_workflow;

        IF status_wf_v = 'OFF'
        THEN
            RETURN -1;
        END IF;

        ia_tid := frameworkia.getvalue(headerbufferia,'IA_TID');
        ret_code_default := 0;
        ret_code         := 0;

        frameworkia.setvalue
        ( headerbufferia
        , 'RET_CODE_DEFAULT'
        , ret_code_default );

        frameworkia.setvalue
        ( headerbufferia
        , 'RET_CODE'
        , ret_code);

        IF ia_tid IS NULL
        THEN
            ia_tid := 'TIA' || dbms_random.STRING('U',1 * 1 + 6) ||
                      TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF6');

            frameworkia.setvalue
            ( headerbufferia
            , 'IA_TID'
            , ia_tid );
        END IF;

That's just the first hundred lines, and I've formatted it. The complete iacore function was well over 600 lines long.

I for one welcome the new standard.

Welcome back

2007-08-10T11:16:00.000+01:00

Our guest administrator "Splogger" has now left the building, along with his page of helpful links to items on Amazon.com and a range of gentlemen's health products.

Suspiciously, a couple of days before he arrived we were taken off air by Blogger's spambots, presumably alerted by the amount of irrelevant, repetitive, and nonsensical text and links to Viagra sites they found here. From what I read, it seems possible that the Blogger automated suspension to prevent blog spam might have actually left the account vulnerable to blog spammers. As ironies go, that is up there with rain on your wedding day and good advice that you just didn't take.