Mmm, π

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:

--  (more...)

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 set that up. This is (more...)

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:

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

   end loop;


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

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


   WHEN e_dupe_flag THEN
      RAISE e_duplicate_err;


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

This reminded me of one I saw recently:

   e_excp_err EXCEPTION;
and another (more...)

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:

[girl_tattoo1.jpg] [girl_tattoo2.jpg]

Naturally I couldn't help (more...)

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

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

   ( p_xyz_id_out OUT NUMBER,
     p_input_array IN myarrayrectype )
   p_xyz_id_out := NULL;

      INSERT INTO xyztab

Explain this

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

explain this


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

No solutions so far.

Make Me One With Everything

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

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

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

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

Or even,

l_col VARCHAR2(30) := '1';

Interview questions

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

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

The £10 UKOUG Weak Joke Challenge

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

Are there any Brummies here today?

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

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

Now where are those user accounts?

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

Developer (11 Oct 2009 14:39:51): I created some users and now they are gone?
TR (11 Oct 2009 14:40:01): We have implemented a daily flashback to the data baseline so that repeatable tests can run every day in that database.
TR (11 Oct 2009 14:40:03): You need to notify us (as per the mail I sent out) when you make data changes that you want to keep from day to day.
TR (11 Oct (more...)

If at first you don’t succeed…

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

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

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

The Undocumented "/1000" currency formatting function

Forum question:


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

How to talk your way out of a hole

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

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, (more...)

The Consultant on Backups

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

The Consultant

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

More Fake Performance Tips

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

TGI g_friday

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