Oracle WTF

Young rz.He over on Stack Overflow has a question about why SQL*Plus doesn't parse every damn 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.

This 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 to the imperial π following our exit from the EU, or the chancellor increases the UK's π in the next budget in order to stimulate economic growth, 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;

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 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);

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;

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.

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.

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.

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?

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.

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';

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?

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

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

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

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.

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.

I recently read this in a book about data warehousing:

Source System Rollback SegmentsWhen 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?

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

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:

1. FTS (Full Table Scans) are always bad and Index usage is always good.
2. Usage of dynamic SQL within the stored procedure code is always bad even for search procedures.
3. Empty Space in an index that gets created due to the DML operations do not get used.
4. Indexes should be rebuilt at regular intervals.
5. Indexes and statistics are the same thing. Also, histograms are needed only on indexed columns.
6. Usage of cursors is always bad so avoid them like the plague.
7. Truncate command cannot be rolled back because it is a non-logged operation.
8. Table variables in SQL Server are always only memory resident.
9. Column order in a covered index does not matter.
10. In the case of SQL Server, one can separate the clustered index from the table.
11. Only committed data gets written to the disk.
12. Logical I/Os (LIO) are not a cause of concern, only Physical IO (PIO) are.
13. Count(1) is better performing than count(*).
14. Issue frequent commits in the application to make the transaction faster and also improve concurrency.
15. 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,

16. The buffer cache hit ratio is a reliable indicator of system performance.
17. Bitmap indexes are perfect for columns with a small number of distinct values, like 'Y' and 'N'.
18. 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.

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