Oracle WTF

Concatenation, Concatenation, Concatenation

2011-03-26T09:08:00.000Z

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

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

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

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

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

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

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

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

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

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

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.

What is this "testing" thing?

2007-08-14T18:08:00.000+01:00

Recently asked on the OTN PL/SQL forum:

I am reading a book: Learning Oracle PL/SQL by Bill Pribyl and Steven Feuerstein.
I am a newbie of PL/SQL and I got no other programming XPs. but in the very first of this book they are introducing something called: testing, to make some test programms /utilites.
I found that is very hard to make, is it neccessary?

Apparently, after you've typed the final semicolon, you can either sit back and open a beer with the satisfaction of a job well done, or you can check whether the thing works. And apparently it's hard, so, do we really have to?

Well, another poster was recently arguing that PL/SQL is not suited to unit testing anyway, or refactoring either for that matter, because it isn't object oriented. I think that will be my excuse from now on. You want me to test stuff, go make PL/SQL more objecty.

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.

One liner

2007-05-03T01:38:00.000+01:00

I was untangling a query when I came across eight variations of this code where only the literals 'a' to 'e' changed. Two of these were nested within an additional NVL so that the second would execute if the first returned null.

nvl(decode(2, 1, 'a', 2, 'b', 3, 'c', 4, 'd', 5, 'e', ' '), 'na')

DATE comparisons: the scenic route

2007-04-08T13:37:00.000+01:00

Need to calculate the number of minutes between two dates? Yes, the dull way is to subtract one from the other and multiply by 1440. But why do that, when you can simply convert each date into Julian format by converting it into a string and then back to a date, and converting the resulting date into a Julian string, and (in a separate step) applying TO_NUMBER to the result to get an integer, so that you can simply subtract one from the other and multiply by 1440.

Except that rounds to the nearest day, so an additional step is to calculate the minutes since midnight for each of the two dates, which you can do by simply converting each date into an 'HH:MI AM' string, then back to a date, then back to an 'SSSSS' string, converting it to a number, dividing by 60 and adding it to the result of the first calculation. I think.

Anyway, consider the following library of handy date functions our Oracle WTF Easter gift to you, the online development community.

CREATE PACKAGE dates_pkg
AS
    FUNCTION julian_date
        ( date_to_convert DATE )
        RETURN NUMBER;

    FUNCTION minutes_since_midnight
        ( timevalue DATE )
        RETURN NUMBER;

    FUNCTION minutes_elapsed
        ( lowdate DATE
        , highdate DATE )
        RETURN NUMBER;

END dates_pkg;
/

CREATE PACKAGE BODY dates_pkg
AS
    FUNCTION julian_date
        ( date_to_convert DATE)
        RETURN NUMBER
    IS
        varch_value VARCHAR (10);
        num_value NUMBER (20);
    BEGIN
        SELECT TO_CHAR
               ( TO_DATE(TO_CHAR(date_to_convert,'MM/DD/YYYY'),'MM/DD/YYYY')
               , 'J')
        INTO   varch_value
        FROM   dual;

        SELECT TO_NUMBER (varch_value)
        INTO   num_value
        FROM   dual;

        RETURN (num_value);
    END julian_date;


    FUNCTION minutes_since_midnight (
        timevalue DATE)
        RETURN NUMBER
    IS
        secs_elapsed NUMBER (20);
        mins_elapsed NUMBER (20);
    BEGIN
        SELECT TO_NUMBER
               ( TO_CHAR(TO_DATE(TO_CHAR(timevalue,'HH:MI AM'),'HH:MI AM')
               , 'SSSSS') )
        INTO   secs_elapsed
        FROM   dual;

        SELECT (secs_elapsed / 60)
        INTO   mins_elapsed
        FROM   dual;

        RETURN (mins_elapsed);
    END minutes_since_midnight;


    FUNCTION minutes_elapsed
        ( lowdate DATE
        , highdate DATE )
        RETURN NUMBER
    IS
        final_number NUMBER (20);
        low_julian NUMBER (20);
        high_julian NUMBER (20);
        num_days NUMBER (20);
        num_minutes NUMBER (20);
        temp_mins NUMBER (20);
        min_low NUMBER (20);
        min_high NUMBER (20);
    BEGIN
        SELECT julian_date (lowdate)
        INTO   low_julian
        FROM   dual;

        SELECT julian_date (highdate)
        INTO   high_julian
        FROM   dual;

        SELECT (high_julian - low_julian)
        INTO   num_days
        FROM   dual;

        SELECT (num_days * 1440)
        INTO   num_minutes
        FROM   dual;

        SELECT minutes_since_midnight (lowdate)
        INTO   min_low
        FROM   dual;

        SELECT minutes_since_midnight (highdate)
        INTO   min_high
        FROM   dual;

        SELECT (min_high - min_low)
        INTO   temp_mins
        FROM   dual;

        SELECT (num_minutes + temp_mins)
        INTO   final_number
        FROM   dual;

        RETURN (final_number);

    END minutes_elapsed;
END dates_pkg;

Just for fun, let's test it:

CREATE TABLE wtf_test (start_date NOT NULL, end_date NOT NULL) AS
SELECT DATE '2006-12-25' + DBMS_RANDOM.VALUE(1,365)
     , DATE '2007-12-25' + DBMS_RANDOM.VALUE(1,365)
FROM   dual CONNECT BY LEVEL <= 1000;

-- ...several runs here to allow for caching etc, last set of results shown...

SQL> set timing on autotrace traceonly stat

SQL> SELECT dates_pkg.minutes_elapsed(start_date,end_date) FROM wtf_test;

1000 rows selected.

Elapsed: 00:00:03.96

Statistics
----------------------------------------------------------
  16000  recursive calls
      0  db block gets
     74  consistent gets
      0  physical reads
      0  redo size
   9330  bytes sent via SQL*Net to client
    809  bytes received via SQL*Net from client
     68  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   1000  rows processed

SQL> SELECT (end_date - start_date) * 1440 FROM wtf_test;

1000 rows selected.

Elapsed: 00:00:00.16

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
     74  consistent gets
      0  physical reads
      0  redo size
  25485  bytes sent via SQL*Net to client
    809  bytes received via SQL*Net from client
     68  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   1000  rows processed

So the handy package version takes 25 times as long as the 1-line SQL version.

And in the interests of fairness, in case you're thinking perhaps that is just the normal overhead of calling PL/SQL functions in SQL, let's try our own function:

CREATE FUNCTION minutes_elapsed
    ( lowdate DATE
    , highdate DATE )
    RETURN NUMBER
AS
BEGIN
    RETURN (highdate - lowdate) * 1440;
END minutes_elapsed;
/

SQL> SELECT minutes_elapsed(start_date,end_date) FROM wtf_test;

1000 rows selected.

Elapsed: 00:00:00.26

Statistics
----------------------------------------------------------
     21  recursive calls
      0  db block gets
    107  consistent gets
      0  physical reads
      0  redo size
  25496  bytes sent via SQL*Net to client
    809  bytes received via SQL*Net from client
     68  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   1000  rows processed

Still 15 times faster.

Many thanks to Padders for sharing this one.

Quote For The Day

2007-03-23T07:51:00.000Z

"PL/SQL development can be a tedious and time-consuming job – often monopolizing the valuable time and efforts of Oracle developers." - SQL Navigator page, Quest Software

Spare a thought today for PL/SQL developers, having their precious time tediously monopolised by, umm, PL/SQL development.

423,551 Invalid Indexes

2007-03-19T20:55:00.000Z

A friend received an alarming email from a commendably proactive DBA who was concerned that he had discovered a potentially serious performance issue on the production system:

The following result shows there are many invalid indexes. For example, in the first row we see that there are nearly 423551 invalid indexes for just one row of a table. So if we rebuilt the index it would improve the performance.

Wow, that sure is a lot of invalid indexes for just one row of a table. For the sake of anonymity, I've run his query against the SCOTT/TIGER schema, where fortunately there are only 14 invalid indexes (or perhaps 10, if that's how you count them). Perhaps you can see what the problem might be:

SELECT DISTINCT
       t.table_name
     , i.index_name
     , t.num_rows table_rows
     , i.num_rows index_rows
FROM   dba_tables t
     , dba_indexes i
WHERE  t.table_name = 'DEPT'
AND    t.owner = 'SCOTT'
AND    i.owner = t.owner
AND    i.index_type = 'NORMAL';

TABLE_NAME                     INDEX_NAME                     TABLE_ROWS INDEX_ROWS
------------------------------ ------------------------------ ---------- ----------
DEPT                           PK_EMP                                  4         14
DEPT                           EMP_DEPT_IX                             4         14
DEPT                           PK_DEPT                                 4          4
DEPT                           PK_SALGRADE                             4          5

4 rows selected.

There is worse to come. It's not just DEPT that has invalid indexes. He goes on:

The following result shows there are many invalid indexes, so if we rebuild the index, it would improve the performance. At the same time from the first and last rows, indexes are not being used, so we need to look into it.

TABLE_NAME                     INDEX_NAME                     TABLE_ROWS INDEX_ROWS
------------------------------ ------------------------------ ---------- ----------
SALGRADE                       PK_DEPT                                 5          4
SALGRADE                       PK_EMP                                  5         14
SALGRADE                       EMP_DEPT_IX                             5         14
SALGRADE                       PK_SALGRADE                             5          5

TABLE_NAME                     INDEX_NAME                     TABLE_ROWS INDEX_ROWS
------------------------------ ------------------------------ ---------- ----------
EMP                            PK_DEPT                                14          4
EMP                            PK_EMP                                 14         14
EMP                            EMP_DEPT_IX                            14         14
EMP                            PK_SALGRADE                            14          5

I just can't help wondering what he thinks an index is.

Performance Tuning and The Big O

2007-01-17T00:33:00.000Z

Oracle Performance Tuning is a big subject, as anyone will appreciate who has read any of a number of books that set out to help you to understand what problem you are trying to solve, what factors may affect the performance you are seeing, what strategies are available to the query optimizer, and so on.

While Cary Millsap's Optimizing Oracle Performance focusses on finding, tracing and prioritising specific problems in the face of vague reports that the system seems a bit slow this week, and Jonathan Lewis' Cost-Based Oracle Fundamentals takes us on a tour of the CBO to help answer such questions as Why isn't my EXISTS query using an index? (and why isn't it faster than the IN version?) a poster on OraFAQ has an approach we've not seen before:

Hi,

The following is a problem I need help with and I am willing to pay for help if necessary. Any info would be greatly appreciated.

Two tables in a database:

Table1 contains a list of phone numbers
Table2 contains a list of phone numbers as well

I would like to create a Table3, in which Table3 contains all numbers from Table1 that is not in Table2. I am looking for the shortest runtime possible, keeping in mind that you can use whatever method(s) you deem necessary.

Table1 contains 30 Million rows,
Table2 contains 2000 rows.

given a regular SQL expression, it will yield Big O(m*n)

Where m = rowcount of Table1
and n = rowcount of Table2

Generate for me, a method in which, runtime will yield Big O (m log2 n).

I don't need code, I want to hear your logic. Table1 is customers, Table2 is a list of prepaid phone numbers. Table3 is list of people to bill.

As usual no database version is given. The first suggestion, as you might expect, is the quite reasonable:

select phone_no from table1
minus
select phone_no from table2;

accompanied by a comment that it doesn't seem like a great piece of schema design to have two tables in the first place. However, the reply comes back:

The guy who wrote this problem just told me that this answer is incorrect.

His response to me was:

it's not too simplistic, but it is incorrect. This will still yield a big O(m*n).

Give it one more try, you are thinking too much in terms of DB.

Ask yourself, what are the only structures that would yield BigO(nlog2n)? Answer that, and you will get your answer.

Any ideas?

Resident mathematician Ross Leishman tried to explain to me what Big O (m log2n) means, and I can confirm that it is not after all a When Harry Met Sally reference as most of us would probably assume. Apparently the version with an EXISTS subquery was what was wanted, which seemed odd to me on a number of levels, not least that an IN subquery would probably produce the same plan, especially in 10g where the new HASH JOIN RIGHT ANTI allows the database to build its hash table from the 2,000-row table2 rather than the 30 million-row table1. But of course we don't know the database version, do we? Or whether the columns are nullable, unique or indexed, or how values are distributed, or really anything about the actual environment that would help in solving a real-world performance problem. I can see where I'm going wrong though. I am thinking too much in terms of DB.

Short loop

2007-01-13T15:13:00.000Z

It's good to see a GOTO every now and then. However, the sender of this one (thanks!) was most impressed by the loop that goes from 1 to v_totalcntr, and indeed the fact that there is a loop and a v_totalctr variable at all, with the variable carefully set from the cursor's %ROWCOUNT, when it can only ever have one value:

DECLARE
  CURSOR cur_pricing IS
      SELECT col1, col2
      FROM   sometable;

  var_pricing cur_pricing%ROWTYPE;

BEGIN
  OPEN cur_pricing;
  FETCH cur_pricing INTO var_pricing;

  IF cur_pricing%NOTFOUND THEN
      GOTO continue;
  END IF;

  v_totalcntr := cur_pricing%ROWCOUNT;

  FOR r IN 1..v_totalcntr
  LOOP
      -- loads of stuff here
      -- but no fetch from cur_pricing
      -- not even for the one time this loop will execute :-)
  END LOOP;

  <<continue>>
  NULL;
END;

Of course you could just fetch the value you want and process it, but where would be the fun in that?

PS The person who sent this in emailed me with a point I must admit hadn't occurred to me:

Saw you posted this one - thanks. But did you pick up on what they probably thought they were doing? I think whoever wrote it thought that %rowcount would have the TOTAL number of rows that the cursor would return - so they thought they would be looping around ALL the records in the cursor. The fact that they also forgot to fetch again in the loop just adds to the problem of course.

Code Re-use

2006-12-13T15:27:00.000Z

This code was anonymised by the sender and some of the original logic may be a bit harder to make sense of as a result. Instead of EMP records, you should probably imagine it checking billion-dollar financial deals or nuclear reactor core temperature readings. Anyway, I think I've figured out what it's supposed to do.

You pass in an EMP record containing first name, last name, email address and so on, and it calls OTHER_PROC(email_address, the_emp_record_as_passed_in) - but only if the email address is not null, and it is unique for employees with that name. For some reason. But how to check? Simple:

Use a cursor to load all the rows for that name into an array. (Apparently there were 130+ columns in the original table.)
Check array.COUNT to see how many rows there are.
If there is only one row, use it, taking care to use an NVL expression because we definitely don't want a NULL email address.
If there is more than one row, open the same cursor again, and this time loop through it comparing each row's email address with the previous one. If it's the same, set lv_email_same = 1, otherwise set it to 0. That way, at the end of the loop we'll know whether they were all the same or not, right?

PROCEDURE unleash_havoc (p_emp_rec emp%rowtype)
IS
   lv_email_same  NUMBER(1) :=0; -- 0: no, 1: yes
   lv_email_null  NUMBER(1) :=0; -- 0: no, 1: yes
   lv_email       emp.email%TYPE := NULL;
   ln_row         NUMBER;

   TYPE emp_tab IS TABLE OF emp%ROWTYPE;
   lt_emp_data  emp_tab;

   CURSOR c_emp (p_last_name VARCHAR2, p_first_name VARCHAR2) IS
      SELECT *
      FROM   emp e
      WHERE  e.last_name = p_last_name
      AND    e.first_name = p_first_name;

BEGIN
   OPEN c_emp (p_emp_rec.last_name, p_emp_rec.first_name);
   FETCH c_emp BULK COLLECT INTO lt_emp_data;
   CLOSE c_emp;

   IF lt_emp_data.COUNT = 1 THEN
       ln_row := lt_emp_data.FIRST;
       lv_email  := NVL(lt_emp_data(ln_row).email,NULL);
       other_proc(lv_email,p_emp_rec);

   ELSIF lt_emp_data.COUNT > 1 THEN

       FOR r IN c_emp (p_emp_rec.last_name, p_emp_rec.first_name) LOOP
           IF NVL(r.email,'X') = NVL(lv_email,'X') THEN
               lv_email := r.email;
               lv_email_same := 1;
           ELSE
               lv_email := r.email;
               lv_email_same := 0;
           END IF;

           IF r.email IS NULL THEN
               lv_email_null := 1;
           ELSE
               lv_email_null := 0;
           END IF;
       END LOOP;

       IF  lv_email_same = 1
       AND lv_email_null = 0
       THEN
           lv_email  := NVL(lt_emp_data(ln_row).email,NULL);
           other_proc(lv_email,p_emp_rec);
       ELSE
           ...
       END IF;
   END IF;
END;

Pause For Thought part 2

2006-10-19T07:28:00.000+01:00

In an AskTom thread about native compilation, a poster mentions that he is seeing some big CPU waits on his system and wonders whether native compilation would help. After some discussion of tracing and profiling and his 32 Tuxedo connections, he reports back that from talking to the programmer (possibly this guy), he might have narrowed the problem down to a procedure called DELAY():

I just happened to talk to the programmer and what i understand is that that specific function when called so many times , it is internally calling a procedure with passing a value n, where n is like 4,5,6 etv. That procedure is called delay and its job is to create a delay in the logic flow. So the delay procedure accepsts values like 10 as seconds and then it
FOR di IN 1..sed LOOP
   NULL;
END LOOP;
A random thought, could this when run so many times accumulate huge cpu?

Tom thinks maybe it might.

Thanks Tom for sending this in.

Death By Furniture

2006-10-12T23:25:00.000+01:00

According to www.identifiers.org, there are two classes of relational database: "Code Class" and "Identifier Class".

We hadn't heard of those either, but it's all made clear in this presentation (pdf, 1.2MB), in which the limitations of the conventional approach and a novel approach to schema design are explained without the aid of Powerpoint, in a series of pictures like this one:

Still confused? Never mind, you can follow the debate on this OTN thread, which George begins by asking for a simple clarification regarding the capabilities of the Oracle RDBMS. From the answers to this, he should be able to determine whether Oracle is old-hat Code Class or funky new Identifier Class:

I have an interest in establishing how the Oracle System Catalogues cope with particular changes. I have never used Oracle, but I have carried out the same test on another RDBMS. If I had access to Oracle I would have carried out this test myself.

The test goes as follows.

Create a new database.

Create a new simple table, with just a few columns.

Create a form for the table, and add a few rows.

Rename the table or a column – if you can’t, then the RDBMS is Code Class.

If you can rename the table or a column, then do so, and invoke the form that you used before changing the table or column name. If it doesn’t work, the RDBMS is Code Class; if it does then the RDBMS is Identifier Class.

In an Identifier Class RDBMS changes of column or relation/table name will not interfere with the operation of any form already in place based on that table.

I'll be very grateful if anybody can give me a definitive answer on this, either through already having explored the issue or by running the test.

It turns out that "Code Class" covers all existing RDBMS products ever conceived, and "Identifier Class" is an improved model invented by George himself, in which some theoretical 4GL development tool yet to be designed allows you to change table and column names without breaking existing code or having to define a view, and surrogate keys are, well, pretty much the same except that they are now called attribute independent relative position independent identifiers. Perhaps one day Oracle will advance to this point, especially now that they've fixed DBMS_OUTPUT and must be wondering what to do next (perhaps after getting a product to work on Apple Mac and fixing the OTN "change password" facility). We can but dream.

Useful books

2006-09-16T23:01:00.000+01:00

An enormous spider appears. It's clearly coming for you. Or not. What do you do?

Staying calm, place a glass or similar containment device over the creature.

Now gently slide a card under the glass, taking care not to trap any legs. There should be eight after you have completed this manoeuvre.

Transfer to a suitable platform such as a book, and carry to a window or other suitable release location.

No Oracle experts were harmed in the making of this blog.

Reintroduce the creature into the wild, where it can begin a new life free to roam among its own kind. Or just wander back in of course, but spiders are not known for their homing instinct. Or are they? To be honest we haven't checked. This one said something about "Scotland" and "revenge".

Finally, always sterilise your equipment.

Meanwhile in Edinburgh, police were called to a flat in the Stockbridge area following reports of a violent assault...

Just, Why?

2006-09-13T12:34:00.000+01:00

I'm currently debugging a procedure of the form illustrated below, lovingly recreated using the ubiquitous scott.emp table. I'm also taking suggestions on what might've been going through the head of the person who wrote it.


RENAME emp TO emp_table
/
CREATE OR REPLACE VIEW emp
AS
   SELECT *
     FROM emp_table
/
CREATE OR REPLACE TRIGGER mod_emp
   INSTEAD OF UPDATE
   ON emp
   FOR EACH ROW
DECLARE
   new_emp                       emp_table%ROWTYPE;
   old_emp                       emp_table%ROWTYPE;
BEGIN
   new_emp.empno       := :NEW.empno;
   new_emp.ename       := :NEW.ename;
   new_emp.job         := :NEW.job;
   new_emp.mgr         := :NEW.mgr;
   new_emp.hiredate    := :NEW.hiredate;
   new_emp.sal         := :NEW.sal;
   new_emp.comm        := :NEW.comm;
   new_emp.deptno      := :NEW.deptno;
   old_emp.empno       := :OLD.empno;
   old_emp.ename       := :OLD.ename;
   old_emp.job         := :OLD.job;
   old_emp.mgr         := :OLD.mgr;
   old_emp.hiredate    := :OLD.hiredate;
   old_emp.sal         := :OLD.sal;
   old_emp.comm        := :OLD.comm;
   old_emp.deptno      := :OLD.deptno;
   update_emp (old_emp, new_emp);
END;
/

CREATE OR REPLACE PROCEDURE update_emp (
   old_emp                             emp_table%ROWTYPE
  ,new_emp                             emp_table%ROWTYPE
)
IS
BEGIN
   IF old_emp.empno != new_emp.empno
   THEN
      RETURN;   --can't update primary key attribute
   END IF;

   IF     old_emp.empno IS NOT NULL
      AND new_emp.empno IS NULL
   THEN
      DELETE FROM emp_table
            WHERE empno = old_emp.empno;

      RETURN;
   END IF;

   IF    (    old_emp.ename IS NULL
          AND new_emp.ename IS NOT NULL)
      OR (    old_emp.ename IS NOT NULL
          AND new_emp.ename != old_emp.ename)
   THEN
      UPDATE emp_table
         SET ename = new_emp.ename
       WHERE empno = old_emp.empno;
   END IF;
   -- ..
   -- ..
   -- ..
   IF    (    old_emp.deptno IS NULL
          AND new_emp.deptno IS NOT NULL)
      OR (    old_emp.deptno IS NOT NULL
          AND new_emp.deptno != old_emp.deptno)
   THEN
      UPDATE emp_table
         SET deptno = new_emp.deptno
       WHERE empno = old_emp.empno;
   END IF;
END;
/

A Year of WTFs

2006-09-09T13:52:00.000+01:00

Each year, the Oracle WTF organisation celebrates its anniversary by inviting a fellow Oracle professional to share an evening of fine Eastern European lager and disappointing bar snacks in a pub where you can't hear each other properly, and this year it was the turn of former newbie (but now of course senior expert) DBA Lisa Dobson.

Actually that's not quite true - I realised today it was just over a year ago that we started the blog, and the other evening some of us met up with Lisa who happened to be in town, and if I'd thought of this a bit earlier I could probably have pretended it was all planned. Anyway it was nice meeting Lisa, and we enjoyed the story about the colleague who accidentally deleted the entire production server, as luck would have it a week after the server room air conditioning took out the only other server by dripping a surprisingly large amount of water into it. That rather outdid my story about the day we turned up for work at a client's site and found nobody could log in, because as it happened someone had stolen the servers during the night. Or the other one, if I'd remembered to tell it, about the high street retail chain whose backup system involved a PC and a timer plug. (I shouldn't laugh - it actually worked quite well.) It was also reassuring to hear we are not the only ones who don't understand a single bloody word of those "Oracle" blogs about installing a Java framework in JDeveloper to implement service-oriented BPEL with a right-click and some XML. Umm, neat.

It seems we're also not the only ones to have been shocked and saddened by Doug Burns' recent senseless killing spree. This monster must be stopped. (...is probably what Doug said as he reached for his copy of "Expert One-On-One Oracle").

Anyway, one year, 63 posts, and some lessons learned:

Post one of Tom Kyte's pet hates like WHEN OTHERS THEN NULL (or WHEN OTHERS THEN DBMS_OUTPUT, which isn't much better quite frankly) for a mention on his blog and a surefire thousand hits. We need one about IM Speak now b/c im sure u will C loadsa hits ;-)
Post something involving security for a mention on Pete Finnigan's site. We also get a steady stream of visitors googling for "forgot system password +oracle", "how to get password of a user in oracle" and so on, and being directed to Umm, I Forgot My Password, part 2. (In future just put it on a Post-It under your keyboard like everyone else.)
Titles involving common Oracle error codes seem to do well. A lot of visitors come here from a Google search for SP2-0552 (guys, you have a variable with a colon in front of it somewhere). We need some posts about ORA-0600 or TNSNAMES.
A lot of people seem to be searching for an explanation of joins and end up at our Joins Explained, heaven help them, where a Mr Sanders Kaufman explains things like "Left Joins are joined on the left columns of two tables. Right Joins are joined on the right columns of two tables." I'm glad we got that cleared up.
Google Analytics Rock. So (while we're at it) do Statcounter, Feedburner and CoComment.

We were recently offered some sponsorship by price comparison website Shopzilla (or rather, they emailed me a week ago and I remembered it in the pub). The consensus among those present was that we should not accept it, although personally I think all that would change if we could get The Daily WTF's Beanbag Girl.

We were also kindly offered a spot on Pythian Group's Logfile Of The Vanities or whatever it's called (or rather, they emailed me a month ago and I seem to have deleted the message, sorry guys, meant to get back to you) but I didn't take them up on it, partly because there didn't seem much point in writing a piece telling people about Oracle blogs they already read (surely?) via blogs.oracle.com, and also because it's explicitly for DBAs, and with the possible exception of Padders we are all developers (even though these days "developer" is often assumed to mean someone who is into aspect-oriented JDeveloper BPEL plug-ins). Our main thing is SQL, PL/SQL and data modelling, and AFAIK none of those have much to do with the DBA role. In fact, thinking about it I'm not sure why they asked. And is that Pie-thian as in Pythagoras and Python, or Pith-ian as in Gryffindor and Slytherin, anyway?

Production-ready code

2006-08-29T04:08:00.000+01:00

I'm not sure that there's much more to add to this forums.oracle.com thread.

Competition

2006-08-19T17:00:00.000+01:00

Well, not really a competition because we haven't got any prizes to give away, but the code below (thanks Padders) has a real "How Many WTFs Can You Spot?" feel about it. I make it four.

PROCEDURE log_error
    ( p_source  IN  VARCHAR2
    , p_result  IN  VARCHAR2)
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    p_primarykey errorlogging.primarykey%TYPE;
BEGIN
    SELECT errorlogging_seq.NEXTVAL
    INTO   p_primarykey
    FROM   dual;

    INSERT /*+ APPEND */ INTO errorlogging NOLOGGING
    ( primarykey
    , source
    , result
    , timestamp
    , wherewasi
    , processed_count
    , process_id )
    VALUES
    ( p_primarykey
    , p_source
    , SUBSTR(p_result, 1, 1000)
    , SYSDATE
    , NULL
    , NULL
    , 1 );

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN NULL;
END;

Show Me The Money

2006-08-08T06:47:00.000+01:00

Money, money, money. It's a rich man's world. It can't buy you love, but as we are constantly reminded it can buy you cheap Levitra and pre-approved loans, and those are just as good and will probably boost this site's stats. It makes the world go round. It's great - but how to format it?

Well, here is one way. The editing history tells the whole story.

/*=============================================================================
Procedure:    FN_FORMAT_DOLLARS
Description:  Formats a number as dollars (2 decimals)

MODIFICATION HISTORY:
Person        Date        Comments
---------     ------      -------------------------------------------
XX            01/24/03    Created
SF            08/05/03    Rewrote the function as a TO_CHAR statement
==============================================================================*/

FUNCTION fn_format_dollars (p_dollars IN NUMBER)
    RETURN VARCHAR2
IS
    -- v_dollars   VARCHAR2 (50);
BEGIN

    /*
    v_dollars := TO_CHAR (p_dollars);
    IF INSTR (v_dollars, '.') = 0
    THEN
        v_dollars :=  v_dollars  || '.';
    END IF;

    -- too many decimal places...
    WHILE   LENGTH (v_dollars) - INSTR (v_dollars, '.') > 2
    LOOP
        v_dollars := SUBSTR (v_dollars, 1, LENGTH (v_dollars) - 1);
    END LOOP;

    -- not enough decimal places...
    WHILE   LENGTH (v_dollars) - INSTR (v_dollars, '.') < 2
    LOOP
        v_dollars := v_dollars || '0';
     END LOOP;
    */

    RETURN TO_CHAR(p_dollars, 'FM999999999990.00');

END fn_format_dollars;

Many thanks to rd for sending this in.

The fine line between clever and stupid

2006-07-17T22:18:00.000+01:00

Stojka Mongo began to be concerrned when he came across table AT_ILOAD_DAYS that looked like this:

CREATE TABLE at_iload_days
( daystring      VARCHAR2(8)
, batch_created  VARCHAR2(1)  DEFAULT 'N' );

where "daystring" contained values like "20030901".

Then he found this convenient procedure to "fill" it:

CREATE OR REPLACE PROCEDURE pr_generate_iload_days
AS
    -- Procedure to fill days from most recent entry in at_iload_days up to current sysdate */
    d_date_string    VARCHAR2(8) DEFAULT NULL;
    d_maxdate_string VARCHAR2(8) DEFAULT NULL;
BEGIN
    -- sysdate will be max date to generate:
    d_maxdate_string := TO_CHAR(SYSDATE, 'yyyymmdd');

    -- Fetch most recent datestring from at_iload_days + 1 day into d_date_string
    -- (default to commercial launch date)
    EXECUTE IMMEDIATE
    'select to_char((to_date(nvl(max(daystring),''20030505''),''yyyymmdd'')+1),''yyyymmdd'') from at_iload_days'
    INTO d_date_string;

    -- We shouldn't load data that will exist in the future, should we?
    IF d_date_string <= d_maxdate_string THEN
        EXECUTE IMMEDIATE
        'insert into at_iload_days (daystring,batch_created) values (' ||
        '''' ||d_date_string || '''' || ',''N'')';

        EXECUTE IMMEDIATE 'COMMIT';
    END IF;
END pr_generate_iload_days;

Our favourite line:

EXECUTE IMMEDIATE 'COMMIT';

It's hard to tell but I think that procedure can be rewritten as:

CREATE OR REPLACE PROCEDURE pr_generate_iload_days
AS
BEGIN
    INSERT INTO at_iload_days (daystring)
    SELECT TO_CHAR
           ( TO_DATE(NVL(MAX(daystring),'20030505'),'yyyymmdd')+1
           , 'yyyymmdd' )
    FROM   at_iload_days
    HAVING NVL(MAX(daystring),'20030505') <= TO_CHAR(SYSDATE, 'yyyymmdd')
END pr_generate_iload_days;

although if DAYSTRING had actually been a date, it would have just been:

CREATE OR REPLACE PROCEDURE pr_generate_iload_days
AS
BEGIN
    INSERT INTO at_iload_days(day)
    SELECT NVL(MAX(day), DATE '2003-05-05') +1
    FROM   at_iload_days
    HAVING NVL(MAX(day), DATE '2003-05-05') <= SYSDATE;
END pr_generate_iload_days;

Thanks 3360 for the title for this post.

Pause for thought

2006-07-15T00:14:00.000+01:00

Requirement:

Can I let a procedure wait for a specific time (10 seconds) before inserting values into a table?

One solution (according to a poster on OTN - don't try this at home):

declare
  dStart DATE := SYSDATE;
  nDiff  NUMBER;
begin
  dbms_output.put_line('dStart: '||TO_CHAR(dStart, 'dd.mm.yyyy hh24:mi:ss'));
  LOOP
    nDiff := (SYSDATE - dStart)*86400;
    EXIT WHEN nDiff >= 10;
  END LOOP;
  dbms_output.put_line('nDiff: '||nDiff);
  dbms_output.put_line('END: '||TO_CHAR(SYSDATE, 'dd.mm.yyyy hh24:mi:ss'));
end;
/

Or if you prefer,

create or replace function timeout
    return number
is
begin
    return to_number(to_char(sysdate,'SSSSS'));
end timeout;
/

Function created.

create or replace procedure tcal( t in number ) as
    a  number:=1;
    st number:=to_number(to_char(sysdate,'SSSSS'));
    x varchar2(12);
    y varchar2(12);
begin
    while(a<99999999) loop
        dbms_output.enable(100000); ---> Just to make loop busy...
        a := a + 1;
        exit when (timeout - st)>=t;
    end loop;

    x:=to_char(trunc(sysdate)+st/(24*60*60),'HH:MI:SS AM');

    dbms_output.put_line('       Started: '||x);

    y:=to_char(trunc(sysdate)+timeout/(24*60*60),'HH:MI:SS AM');

    dbms_output.put_line('       Ended:   '||y);
    dbms_output.put_line(timeout-st||' seconds reached...');
end ;
/

Procedure created.

Now that is all well and good, you may say, after all the cpu isn't doing anything else at the moment and these days they rarely catch fire, but couldn't we just use the supplied procedure DBMS_LOCK.SLEEP? Well apparently it is impractical. Those with a quiet afternoon to spare might like to follow the reasons why, at forums.oracle.com/forums/thread.jspa?threadID=402345.

That Reminds Me...

2006-06-23T16:59:00.000+01:00

Sometimes the solutions people come up with to a given problem are breathtaking in their combination of ingenuity and insanity. Take this view, which exists purely to transform a boring "reminder number" like 3, 4, 5, ... into the English text "Third Reminder", "Fourth Reminder", "Fifth Reminder", ...

CREATE OR REPLACE VIEW reminders_view AS
SELECT reminder_id
, reminder_seq
, ( SELECT 
      DECODE (reminder_seq, 
       1, 'No reminder', 
       2, 'Reminded', 
       3, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
       4, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
       5, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    6, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    7, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    8, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    9, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    10, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    11, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    12, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    13, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    14, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    15, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    16, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    17, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    18, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    19, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    20, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    21, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    22, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    23, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    24, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    25, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    26, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    27, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    28, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    29, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
    30, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder', 
       'Reminded') FROM DUAL) reminder_type 
FROM reminders;

What a beauty! It works as follows:

1) Use a DECODE to determine what the number is. Note that 2 is treated as a special case, even though it ends up the same as all the "other" numbers above 30.

2) For numbers between 3 and 30:

2.1) Convert to a string

2.2) Concatenate with '-MAY-2004'

2.3) Convert to a date

2.4) Convert back to a string using the 'ddspth' format mask

2.5) Convert to Init Caps

2.6) Append the word ' Reminder'

3) Enclose the whole lot in a redundant "(SELECT ... FROM DUAL)" scalar subquery.

Obviously, this saved the developer from the tiresome task of typing 'Third', 'Fourth', 'Fifth' etc. And presumably he/she isn't aware of the CASE expression that could have reduced it to:

CREATE OR REPLACE VIEW reminders_view AS
SELECT reminder_id
, reminder_seq
, CASE WHEN reminder_seq = 1
           THEN 'No reminder'
       WHEN reminder_seq BETWEEN 3 AND 30
           THEN INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder'
       ELSE 'Reminded'
       END reminder_type 
FROM reminders;

(And why stop at 30 when May has 31 days?!)

Well, it gave me a much needed laugh on a Friday afternoon, anyway.

All names have been changed to protect the guilty.

Why Machines Will Never Take Over The World

2006-06-06T23:20:00.000+01:00

It is a recurring theme in science fiction that as computers increase in power and sophistication, they may one day reach a point where they decide they can do better without us, and condemn us to lives suspended in racked pods with our brains plugged into a vast virtual world, or alternatively send increasingly resourceful and indestructable robots to hunt us down in our bunkers, while above ground survivors fight a desperate war for survival amidst the wreckage of civilisation.

If you find this vision of the future alarming, take heart in this SQL query, which was generated by a machine. Not, perhaps, a Cyberdyne Systems T-800 or the Matrix Mainframe, but something called OLAP API. We don't know what that is either, but we can tell you that if any robot descended from it ever attempts to enslave humanity using a virtual world and an unfeasible pod system, rest assured that you will have time to get out of its way.

Dynstatic SQL...

2006-05-25T21:51:00.000+01:00

Here's Connor again...

Note that the following code has been "anonymised" to protect the guilty.

procedure P is
begin
   ...
   ...
   execute immediate 'drop  table T';
   execute immediate 'create table T as select * from ......';
   ...
   ...
   ...
   for i in ( select * from T ) loop
        ...
        ...
   end loop;
end;

Ah, a mix of dynamic and static references... Now how precisely did that compile? Nope, I'm not sure either.

If in doubt, test, test and test again

2006-05-25T21:40:00.000+01:00

Thanks to Rob Baillie for the following example...

I was glancing through some legacy code today, and came across this.

It's funny how barnacles can accumulate when code changes over time.

            if r2.status_id = 3 then
                 v_gp := r2.rate;
            elsif r2.status_id in (11, 12) then
                if r2.type_id = 3 then
                    v_gp := r2.rate;
                else
                    v_gp := r2.rate;
                end if;
            else
                v_gp := r2.rate;
            end if;

Skip over the record being called r2 and work out what it actually does...

Code generation nonpareil

2006-05-23T21:02:00.000+01:00

We've all engaged in code generation of one sort or another. For certain problems it's just the right, or perhaps the only tool. But then your content management vendor starts to throw this sort of thing at your database...

OPEN p_cursor FOR
 'SELECT content_id, content_status
    FROM content
   WHERE content_type_id = 319
     AND content_id IN (
      51055 , 45531 , 42208 , 42911 , 46494 , 52898 , 44262 , 44312 , 47474 , 42792 ,
      45956 , 45109 , 53432 , 14936 , 29040 , 28779 , 53015 , 48366 , 53739 , 48565 ,
      47188 , 46573 , 43038 , 53534 , 51999 , 49731 , 52847 , 43883 , 41522 , 50804 ,
      49975 , 45729 , 53260 , 47658 , 41325 , 49454 , 41374 , 45328 , 51612 , 54347 ,
      50092 , 48147 , 42416 , 42570 , 49533 , 41948 , 51740 , 52973 , 42648 , 44867 ,
      48289 , 45943 , 49556 , 54550 , 46801 , 43628 , 40569 , 41576 , 46752 , 44982 ,
      42309 , 45146 , 47198 , 44993 , 47768 , 47060 , 46889 , 45651 , 47045 , 45830 ,
      41248 , 54370 , 43741 , 44183 , 28451 , 45094 , 54332 , 47030 , 42060 , 41293 ,
      48287 , 48012 , 47740 , 45688 , 43639 , 48484 , 47583 , 45304 , 51478 , 42633 ,
      40558 , 43793 , 41587 , 49407 , 28803 , 43272 , 46464 , 45602 , 43866 , 44521 ,
      41200 , 48044 , 46927 , 29186 , 45774 , 43722 , 45128 , 43398 , 47397 , 41670 ,
      51888 , 47534 , 29237 , 42486 , 53811 , 44704 , 46618 , 48994 , 44848 , 44573 ,
      52956 , 44487 , 42435 , 48164 , 43451 , 52031 , 51300 , 52595 , 53141 , 44032 ,
      50904 , 41477 , 42161 , 44622 , 52695 , 43838 , 44562 , 45373 , 44882 , 47247 ,
      42367 , 50921 , 46265 , 41933 , 48960 , 43143 , 43345 , 28412 , 48868 , 49005 ,
      43533 , 46953 , 52420 , 44017 , 49817 , 41490 , 41395 , 43027 , 47703 , 29064 ,
      41689 , 50564 , 44112 , 28957 , 29544 , 45223 , 43252 , 49699 , 47356 , 43565 ,
      53604 , 45478 , 29422 , 53041 , 45697 , 44054 , 42469 , 44035 , 48750 , 46667 ,
      50060 , 46698 , 48306 , 45849 , 45563 , 42452 , 45143 , 46110 , 47800 , 43076 ,
      41280 , 45862 , 41657 , 42114 , 47523 , 51841 , 45988 , 48023 , 47307 , 43203 ,
      53464 , 44202 , 47984 , 46218 , 45255 , 51412 , 52409 , 47442 , 45932 , 28269 ,
      43823 , 46835 , 47785 , 50968 , 46050 , 41916 , 47219 , 51772 , 41980 , 43377 ,
      43681 , 50007 , 43072 , 47150 , 45900 , 53376 , 54338 , 45360 , 49086 , 46520 ,
      47626 , 29529 , 50321 , 51982 , 53660 , 51644 , 42028 , 41901 , 53269 , 47995 ,
      43754 , 49163 , 46069 , 51213 , 53566 , 52304 , 49588 , 51134 , 45167 , 46447 ,
      42108 , 45570 , 29439 , 46091 , 44160 , 52227 , 47324 , 42256 , 51262 , 28854 ,
      43220 , 41363 , 45272 , 42097 , 46790 , 51102 , 50338 , 44149 , 49849 , 49375 ,
      41814 , 44738 , 54211 , 52712 , 54364 , 50283 , 54442 , 46863 , 48687 , 45178 ,
      28464 , 44084 , 47615 , 44504 , 50895 , 46231 , 45723 , 47753 , 47230 , 29600 ,
      43430 , 28764 , 41619 , 44217 , 43649 , 47502 , 41509 , 47425 , 42873 , 51814 ,
      41438 , 44925 , 15364 , 41539 , 50336 , 42777 , 42127 , 44967 , 46942 , 43578 ,
      46644 , 48887 , 52748 , 46904 , 42290 , 44000 , 45446 , 45967 , 46539 , 44361 ,
      52990 , 45341 , 42326 , 47952 , 49746 , 29512 , 45806 , 50252 , 48121 , 46981 ,
      29162 , 41702 , 48700 , 50692 , 49039 , 44689 , 51181 , 42988 , 41737 , 41782 ,
      54283 , 44796 , 42667 , 48793 , 42597 , 49195 , 46018 , 48070 , 42975 , 49264 ,
      43675 , 29478 , 51000 , 43287 , 49052 , 51589 , 42506 , 44301 , 44649 , 44908 ,
      48072 , 50857 , 46020 , 44067 , 42013 , 42091 , 43464 , 48578 , 54015 , 41412 ,
      51361 , 52509 , 47124 , 51316 , 44143 , 29724 , 52714 , 44638 , 46142 , 41459 ,
      42586 , 43919 , 48945 , 44346 , 49390 , 44822 , 46631 , 43107 , 43591 , 42144 ,
      29755 , 43662 , 47540 , 43808 , 45390 , 42223 , 42371 , 42551 , 47918 , 47337 ,

For brevity's sake I'll skip the next 11,000 elements of the IN list.

Oracle World Goes Sensible shock

2006-05-09T11:14:00.000+01:00

You may be wondering WTF happened to all the WTFs, and so are we. It seems the Oracle world has been going through a rather depressing sensible season recently, in which nobody posts Pro*Ada code and wonders why it won't run at the SQL*Plus prompt, or suggests adding "AND 1=1" to any query to make it go faster. Even Mr Feuerstein seems to have deserted us.

Perhaps Mike Ault's foray into international economics cheered some of us up. He proposed an ingenious regulatory system requiring (I think) US grain, medicine and other key exporters to adjust their prices in response to international oil market fluctuations, on the grounds that certain oil producers are not taking America seriously enough. Now that'll teach Johnny Foreigner a lesson. "It is time for America to get tough", he adds. Oh dear.

On a more conventional note, a Mr Sahil Malik complained at great length on cdos about how hard it was to install 9i Personal Edition on his PC. At one point he fumed:

Larry Ellison IMHO has only one business idea - "Defeat bill gates and trap every programmer in matrix like pods powering oracle databases". WHAT THE HECK !! Time he matured up a bit.

I agree. Larry Ellison, if you're listening, you need to mature up a bit and forget the whole Matrix pod idea. We all know that ends badly.

Over on the OTN SQL Developer Forum, we were intrigued when one frustrated poster asked:

I cannot find clustered option for the indexes or PKs that I create in SQL Developer.

Where is the CLUSTERED check box in user interface?

Where indeed? Helpfully, Sharon from the SQL Developer team promised to get one added ASAP:

I have added an enhancement request to get this added to the interface in a future release.

We look forward to seeing what that does.

SP2-0552: Bind variable not declared

2006-04-06T14:44:00.000+01:00

Please help. Why does SQL*Plus keep giving me the error

SP2-0552: Bind variable "INTARRAY" not declared.

Thanks in advance.

DECLARE
    BatchSize : constant := 50;

    subtype IndexRanges is INTEGER range 1 .. BatchSize;
    type IntArrays is array( IndexRanges) of INTEGER;
    IntArray : IntArrays;

    EXEC SQL DECLARE network_cursor CURSOR FOR
        select pal.provider_id from hold_provider_address_link pal;

    EXEC SQL OPEN network_cursor;

-- establish a local block, with an exception to
-- handle the "no data found" condition
begin
    EXEC SQL WHENEVER NOT FOUND raise NO_MORE_DATA;
    FETCH_LOOP:
    loop -- fetch the data, 20 rows at a time
        EXEC SQL FETCH network_cursor
        INTO :IntArray;

        for I in 1..20 loop
            -- process batches of 20 rows
            ...
        end loop;

        commit;
    end loop FETCH_LOOP;

exception
    -- the exception NO_MORE_DATA is raised when there is
    -- no more data to FETCH
    when NO_MORE_DATA =>
        PUT("No more data to fetch. N of rows fetched was ");
        PUT(ORACLE.SQLROWS);
        NEW_LINE;

        -- turn off the error handling
        EXEC SQL WHENEVER NOT FOUND CONTINUE;
end;
/

I mentioned this in a comment the other day so apologies if you've seen it before, but I felt it really deserved its own post.

(Hint: SP errors are from SQL*Plus, and INTARRAY is a bind variable from SQL*Plus's point of view because it begins with ":")

By the way I don't mean to laugh too much at the poor guy who posted this problem on a forum, as from his other posts he seems to have had a crappy application dumped on him without much support from anyone at his company. I did ask him what language it was written in but he hasn't replied. Suggestions, anyone? (My guess is Pro*Ada, which in my opinion we don't see enough of these days.)

A Thneed's a Fine-Something-That-All-People-Need!

2006-03-28T16:32:00.000+01:00

Now maybe I'm misusing this whole forum with this posting, and if so I apologise profusely, but...

www.janus-software.com/fb_fyracle.html

Are you worried about "under-licencing" too...?

Not only does open source Firebird-Fyracle have a catchy name and almost match Oracle on the 6 well known "gold standard key database evaluation mapping criteria", but it is "just as idiosyncratic", AND you can run "Compiere v2.5.3c" ( almost )!

Who could possibly argue with the rigorously documented "proof of the pudding" that this product is "faster than Oracle on the same hardware"

The Clue Was In The Name

2006-03-27T10:43:00.000+01:00

email from tester:

This bug is blocking test: bg9876-a, I have elevated it to priority 1.

reply:

The bug you've raised says "please provide a version of write_transaction() stored procedure, that doesn't write anything to the database", can you explain where you're going with this?

email from tester:

It is a question of practicality for us, we have used this method in every release, we need to test high throughput rates without a Performance Oracle server.

ORA-06553: PLS-906: Compilation is not possible

2006-03-25T21:35:00.000Z

Or in this case posted on the OTN Forums, is it even desirable. It's nice to see there is the ability for the database to simply say, "I'm sorry but this code is too stupid to run".

The Decibel Method

2006-03-20T15:49:00.000Z

A poster on the hardcore comp.databases.oracle.server newsgroup had some tables in the production database that he felt were redundant, and asked, not unreasonably I felt, whether there was a way to tell from his Developer 6i application whether they were in fact in use:

hello

we are using oracle 9i production database and d2k 6i applications. is there any way by which i can find the tables/ columns that are not in used by applications. So that i can move them out from our production database.

can anyone throw some light how to do it.

After the obligatory initial responses along the lines of "it can't be done" and "you are an idiot for even thinking about it" (you take your life in your hands when you post on cdos), one respondent begins,

For tables, the solution is called "auditing". You can audit desired objects.

Reasonable enough. Give it a year, then if nobody has used the tables, they are probably not so important. Then he continues:

For columns, you should be using so called decibel method: if you suspect that column C1 in table TAB is not used, you can always execute the following commands:
update TAB set C1=NULL;
commit;
If the reaction to that is a loud scream, accompanied by swearwords and a genuine cornucopia of various expletives, you've made a mistake, the column was used. It's time for the "I'm sorry, I didn't know that this column was still being used" routine. You can rest assured that this swearing sucker is gonna be busy for a while.

If not, you can proceed and drop the column. The previous update has an added benefit of making "drop column" operation faster. It will also expose weak points in all those lousy applications that use "select *" and expect the table to populate all of their variables.

An alternative to the decibel method is fine-grain auditing, described in the books by D. Knox. It's much more tedious and requires much larger knowledge then the decibel method, which is also a lot of fun.

Problem solved I think.

Our thanks to Herod T for the plug.

If only there was a SUM function

2006-03-17T14:29:00.000Z

...then we wouldn't have to write code like this, which, as Graham Oakes can confirm, takes ages:

FOR r IN ( SELECT tid FROM t_brel WHERE bqid = iqid )
LOOP
    SELECT q.lamount, q.famount
    INTO   v_lamount, v_famt
    FROM   t_aq   atq
         , t_q    q
    WHERE  atq.tid = r.tid
    AND    q.qid = atq.qid
    AND    qtype = 10;

    v_ltotal := v_ltotal + v_lamount;
    v_ftotal := v_ftotal + v_famt;
END LOOP;

UPDATE t_q
SET    lamount = v_ltotal
     , famount = v_ftotal
WHERE  qid = iqid;

We can but dream.

While we're on the subject, we received this from a correspondent who wishes to remain anonymous:

FOR r IN
(
    SELECT /*+ FIRST_ROWS */
           *
    FROM   pay_details
    WHERE  acct_fk = p_accountpk
)
LOOP
    DELETE pay_details
    WHERE  primarykey = r.primarykey;

    COMMIT;
END LOOP;

I particularly like the FIRST_ROWS hint. "What, it's slow? Better make sure it uses that index..."

Simplest row generator meets maximum inefficiency

2006-03-11T01:31:00.000Z

Generating a set of values that are not stored in a table is a reasonably common problem. I recently came across this solution for a set of irregular interval values.

 OPEN cur_rows FOR
   'SELECT 3  FROM dual UNION '||
   'SELECT 6  FROM dual UNION '||
   'SELECT 9  FROM dual UNION '||
   'SELECT 12  FROM dual UNION '||
   'SELECT 24  FROM dual UNION '||
   'SELECT 36  FROM dual';
 LOOP
    FETCH cur_rows INTO v_num_value;
    EXIT WHEN cur_rows%NOTFOUND;

I didn't know which to admire the most. The clever use of dynamic SQL or the sort distinct to get rid of any duplicate literals that may occur from accidentally typing the same line twice. I think the latter wins for being also relationally pure apparently.

Umm, I forgot my password, Part 2

2006-03-10T11:45:00.000Z

In a thread on OTN forums, a poster asked how he could recover a user's password. Naturally he was told that it can't be done because the password itself is not stored, only a hash based on the username and password combination.

After some interesting discussion of password hashing, brute force and rainbow table attacks and the like, a poster makes the following rather novel suggestion:

if you apply the password verify function, yes it is possible to get the password of a user.

Etape 1: edit the utlpwdmg.sql script and add the line which is in bold (insert into...)

-- Check if the password is same as the username

IF NLS_LOWER(password) = NLS_LOWER(username) THEN raise_application_error(-20001, 'Password same as or similar to user'); END IF;

insert into mytable values ('username','password');
-- Check for the minimum length of the password
Etape 2: run this script as sys

Etape 3: grant the profile to user whom u want to get the password.

u will be able to get the new password by consulting the table mytable (u must create this table)

This had me puzzled at first, and I had to check what $ORACLE_HOME/rdbms/admin/utlpwdmg.sql did. In fact it creates a default password verification function called "verify_function" ("verify_password" might have made a better name, but that's obfuscation for you), and then assigns it to the default profile using ALTER PROFILE DEFAULT ... PASSWORD_VERIFY_FUNCTION verify_function;

This means that any attempt to change the password for a user with the default profile (see ALTER USER examples in the documentation), will automatically execute verify_function(username, password, old_password). The idea is to apply some rules to prevent easily guessed passwords such as your username, but Mouhamadou's ingenious addition is his extra line,

insert into mytable values (username,password);

Now any attempt to change the password for a user with the default profile that successfully passes this extra security step will result in the new password being logged in mytable in clear text.

As we like to say on Oracle WTF, problem solved.

Many thanks to Andrew P. Clarke for submitting this.

What is it with dates?

2006-03-09T21:30:00.000Z

What exactly is it with dates that so many Oracle developers struggle with? Why do they go to such lengths to avoid using the DATE type? Why, if DATE validation or arithmetic is required, would they use CHARs or NUMBERs? Abuses of DATEs seems to be a recurring theme on Oracle WTF. Indeed, here's another good example kindly provided by Graham Oakes.

Over to Graham...

This is a cracker, the easy way to check the supplied date in a string (after all who actually wants to use date types) is a valid date.

IF to_number(substr(v_valuedate,3,2)) NOT BETWEEN 1 AND 12
THEN
    v_rowstatustype := -190;
ELSE
    -- check 31 day months
    IF substr(v_valuedate,3,2) IN ('01','03','05','07','08','10','12')
    THEN
        IF to_number(substr(v_valuedate,1,2)) > 31
        THEN
            v_rowstatustype := -200;
        END IF;

    -- check 30 day months
    ELSIF substr(v_valuedate,3,2) IN ('04','06','09','11')
    THEN
        IF to_number(substr(v_valuedate,1,2)) > 30
        THEN
            v_rowstatustype := -200;
        END IF;

    -- check leap year feb
    ELSIF substr(v_valuedate,3,2) = '02'
          AND MOD(to_number(substr(v_valuedate,5,4)),4) = 0
    THEN
        IF to_number(substr(v_valuedate,1,2)) > 29
        THEN
            v_rowstatustype := -200;
        END IF;

    -- check non-leap year feb
    ELSIF substr(v_valuedate,3,2) = '02'
          AND MOD(to_number(substr(v_valuedate,5,4)),4) != 0
    THEN
        IF to_number(substr(v_valuedate,1,2)) > 28
        THEN
            v_rowstatustype := -200;
        END IF;
    END IF;
END IF;

Create Your Own DUAL Table

2006-03-07T16:59:00.000Z

If you want to retrieve a sequence value into a PL/SQL variable, you have to SELECT FROM DUAL. (Or use RETURNING INTO of course, but never mind that now.)

According to some, this is not only an inconvenient restriction, but also prone to failure if SYS.DUAL contains more than one row.

Help is at hand in the form of the utility below, which solves both problems at once by installing a table, a public synonym, a trigger and a function. Now your application will never again be unable to retrieve sequence values directly into PL/SQL variables on days when DUAL contains more than one row. So that's one less thing to worry about.

CREATE OR REPLACE PROCEDURE replace_onerow (
 table_name_in IN VARCHAR2
)
IS
BEGIN
 BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE ' || table_name_in;
 EXCEPTION
    WHEN OTHERS THEN NULL;
 END;

 EXECUTE IMMEDIATE 'CREATE TABLE '
                   || table_name_in
                   || ' (dummy VARCHAR2(1))';

 EXECUTE IMMEDIATE
    'CREATE OR REPLACE TRIGGER onerow_' || table_name_in ||
    '  BEFORE INSERT
       ON ' || table_name_in || '
    DECLARE
       PRAGMA AUTONOMOUS_TRANSACTION;
       l_count PLS_INTEGER;
    BEGIN
       SELECT COUNT (*)
       INTO   l_count
       FROM   ' || table_name_in || ';

       IF l_count = 1
       THEN
          raise_application_error
          ( -20000
          , ''The ' || table_name_in || ' table can only have one row.'' );
       END IF;
    END;';

 EXECUTE IMMEDIATE 'BEGIN INSERT INTO '
                   || table_name_in
                   || ' VALUES (''X''); COMMIT; END;';

 EXECUTE IMMEDIATE 'GRANT SELECT ON '
                   || table_name_in
                   || ' TO PUBLIC';

 EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM '
                   || table_name_in
                   || ' FOR '
                   || table_name_in;

 EXECUTE IMMEDIATE
    'CREATE OR REPLACE FUNCTION next_pky (seq_in IN VARCHAR2)
        RETURN PLS_INTEGER AUTHID CURRENT_USER
     IS
        retval PLS_INTEGER;
     BEGIN
        EXECUTE IMMEDIATE ''SELECT '' || seq_in
                      || ''.NEXTVAL FROM ' || table_name_in ||
                      '|| ''INTO retval;
        RETURN retval;
     END next_pky;';

END replace_onerow;

Error-Prone Error Handling

2006-03-07T13:42:00.000Z

A colleague found this handy utility on the internet. The idea is that whenever you get an Oracle error, the errant SQL statement will be written away to a table along with the message for you to read and enjoy at your leisure. Which would be fine I suppose, if it didn't introduce a whole lot of errors of its own.

create table caught_errors (
  dt        date,               
  username  varchar2( 30), -- value from ora_login_user
  msg       varchar2(512),
  stmt      varchar2(512)
);

create or replace trigger catch_errors
   after servererror on database
declare
   sql_text ora_name_list_t;
   msg_     varchar2(2000) := null;
   stmt_    varchar2(2000) := null;
begin

  for depth in 1 .. ora_server_error_depth loop
    msg_ := msg_ || ora_server_error_msg(depth);
  end loop;

  for i in 1 .. ora_sql_txt(sql_text) loop
     stmt_ := stmt_ || sql_text(i);
  end loop;

  insert into 
    caught_errors (dt     , username      ,msg ,stmt )
           values (sysdate, ora_login_user,msg_,stmt_);
end;
/

Note that whenever a SQL error occurs on the database this trigger will fire and:

1) try to stuff the entire SQL statement that failed into a varchar2(2000), regardless of how big it actually is

2) if that doesn't blow up, then tries to insert the same value into a varchar2(255) column

In SQL Plus, this leads to errors like this (using an invalid table name in a large SELECT statement):

ERROR at line 30:
ORA-00604: error occurred at recursive SQL level 1
ORA-01401: inserted value too large for column
ORA-06512: at line 12
ORA-00942: table or view does not exist

... which is clearly more informative than:

ERROR at line 30:
ORA-00942: table or view does not exist

Umm, I forgot my password

2006-03-01T13:47:00.000Z

Probem solved, with the following convenient password reset procedure found in a large production database, with EXECUTE granted to PUBLIC and a handy public synonym:

CREATE OR REPLACE PROCEDURE reset_user_password(p_username IN VARCHAR2)
AS
BEGIN
   execute immediate 'ALTER USER '||upper(p_username)||' IDENTIFIED BY '||upper(p_username);
END;
/

Many thanks to Robert De Laat for this submission.

Hey, we do the WTFs Part II...

2006-02-27T20:30:00.000Z

At this rate, we might need to make Connor an honorary member. Here's another gem from his web site that he's picked up on his travels. PL/SQL doesn't get much "better" than this...

Tip: add DISTINCT to every query

2006-02-26T08:48:00.000Z

We all know that SQL can be a harsh mistress, and relational theorists such as Chris Date have long argued that the language is fundamentally flawed and that vendors have been misapplying relational theory from the start.

Now a Perl developer on perlmonks.org has been reading Date's book and finds that it explains all of his frustrations with databases. One tip for addressing their shortcomings is to add the handy DISTINCT keyword to every single query, because stupid old SQL doesn't automatically apply the degree of uniqueness you might have in mind:

In fact, one of the founders of relational theory, C.J. Date, recommends that every SQL SELECT statement be written as SELECT DISTINCT ... Unfortunately, many folks get upset with this for a variety of reasons. First, DBMS do a horrible job of optimizing DISTINCT queries. There are many cases where the DISTINCT can be safely ignored but in practice, using DISTINCT with large sets of data and complicated queries can have an abysmal impact on performance. I think this can be put into the "premature optimization" category, though. Until we know that DISTINCT is causing an impact, isn't it better to improve the chance of getting correct results and worry about performance afterwards?

I had to read that a couple of times to realise that adding a DISTINCT to every single query in the hope that it might mask some unknown deficiency in your model, your query, or the SQL language itself is not the "premature optimization" being referred to here - he means the idea that doing so might affect performance. After all, it might not, right?

Read the rest of the discussion at perlmonks.org: "Why SQL Sucks (with a little Perl to fix it)". It also appears on Database Debunkings, "On the sins of SQL".

Hey, we do the WTFs...

2006-02-24T20:59:00.000Z

I found this beauty on Connor McDonald's web site. It's perfect fodder for Oracle WTF and Connor is happy for us to include it here.

Stop Press: Oracle Granted License To Extend February

2006-02-24T20:30:00.000Z

Yes, it's official. Oracle has been granted permission to extend February by 3 days. Shame no-one told the developers responsible for INTERVAL arithmetic.

SQL> SELECT DATE '2006-01-31' + INTERVAL '1' MONTH
  2  FROM   dual;
SELECT DATE '2006-01-31' + INTERVAL '1' MONTH
                           *
ERROR at line 1:
ORA-01839: date not valid for month specified

Wanna Date?

2006-02-19T01:32:00.000Z

Dates are known to be exceedingly difficult and avoiding them at all costs is something of a skill. This function, in the spirit of Never do in SQL what you can do in PL/SQL, calculates a date range before calling another procedure that also avoided using dates for its input date parameters. So despite its absence, it is at least partially responsible for this mess.

I stripped the code down to its date handling which studiously avoids using date calculations wherever possible, and uses string handling instead, leaving in the comments because they are also the documentation.

create or replace function start_date (
p_range         in      varchar2,
p_in_date       in      varchar2    -- DD-MON-YYYY format String
)
return varchar2
as
l_out_date varchar2(11);
month varchar2(10) := to_char(to_date(p_in_date,'DD-MON-YYYY'),'MON');
year varchar2(10)  := to_char(to_date(p_in_date,'DD-MON-YYYY'),'YYYY');
v_cnt_yr        number;
v_end_date      date    := to_date(p_in_date,'DD-MON-YYYY');
v_start_date    varchar2(11);
begin
if p_range = 'QTD' THEN

  if month in ('JAN','FEB','MAR') then
     -- if given month = march and date is 31
     -- then data for jan,feb and march.
     l_out_date := '01-JAN-'||year;
  elsif month in ('APR','MAY','JUN')  then
     l_out_date := '01-APR-'||year;
  elsif month in ('JUL','AUG','SEP')  then
     l_out_date := '01-JUL-'||year;
  elsif month in ('OCT','NOV','DEC')  then
     l_out_date := '01-OCT-'||year;
  end if;

elsif p_range = 'YTD' then

  -- beginning of the year.
   l_out_date := '01-JAN-'||year;

elsif p_range = 'M' or p_range='MTD' then

  -- beginning of month
   l_out_date := '01-'||month||'-'||year;

elsif p_range like 'B%' then
   v_cnt_yr     := substr(ltrim(rtrim(p_range)),2);
   -- We take the start date as the first day after trailing
   -- back the required no. of months
   l_out_date   := to_char(last_day(add_months(
                      last_day(v_end_date), -v_cnt_yr)) + 1,
                      'DD-MON-YYYY');
end if;
return l_out_date;
end;

After reading it I thought, "So what does this do that TRUNC doesn't?" Apparently not a lot when you need to get the month, quarter or year to date. If you try this at home remember to format the return of START_DATE for readability since it usefully returns a 4000 character string.

SQL> exec :d := '17-JUN-2006'

PL/SQL procedure successfully completed.

SQL> select start_date('QTD',:d) start_date,
2    trunc(to_date(:d),'Q') from dual;

START_DATE  TRUNC(TO_DA
----------- -----------
01-APR-2006 01-APR-2006

SQL> select start_date('YTD',:d) start_date,
2    trunc(to_date(:d),'Y') from dual;

START_DATE  TRUNC(TO_DA
----------- -----------
01-JAN-2006 01-JAN-2006

SQL> select start_date('MTD',:d) start_date,
2    trunc(to_date(:d),'MM') from dual;

START_DATE  TRUNC(TO_DA
----------- -----------
01-JUN-2006 01-JUN-2006

But what about the mysterious 'B%' format mask? This calculates the first day of the month, where 'Bn' is n-1 months ago, tricky eh? In SQL we are forced to call two functions instead of having START_DATE call ADD_MONTHS for us with two bonus LAST_DAYS thrown in for good measure. The n-1 bit could even be a bug but who knows?

SQL> select start_date('B12',:d) start_date,
2    trunc(add_months(to_date(:d),-11),'MM') from dual;

START_DATE  TRUNC(ADD_M
----------- -----------
01-JUL-2005 01-JUL-2005

The convenience obviously outweighs the problem of having to deal with an undocumented date function that accepts and returns strings. Also with this function I have the luxury of substituting 'M' for 'MTD', but not 'Y' for 'YTD' or 'Q' for 'QTD' though I suspect these could be improvements for versions 2.0 and 3.0.

The Phantom's Gonna Git Ya

2006-02-14T20:44:00.000Z

I know I'm asking for trouble here by offering an AskTom page for a WTF, but I couldn't resist. If there was ever a time you wanted your spelling to be spot on, it would be when posting a link to a spell-checker...

EAV Returns: The Concrete Elephant approach

2006-02-09T18:25:00.000Z

Anyone who has read Tales Of The Oak Table, not to mention Tony Andrews' blog or any of the countless articles and discussions on the subject on AskTom and elsewhere, will know two things about the fabled "Entity-Attribute-Value" approach to database design, in which you model all "things" in one table with a "thing ID" and a "thing type", plus a second table holding one row per "attribute", and thus create an application that can model any conceivable type of thing, ever:

It seems like a clever idea at first.
It isn't.

But wait. A poster on OTN forums ("SIMPLE Database Design Problem") has solved the major problems inherent in the original Entity-Attribute-Value approach, by simply denormalising away the Attribute-Value part.

Now the ENTITIES table will have all the columns for every entity type. Maybe a lot of them will be null because "INVOICE" rows will use mainly different columns to "TROPICAL_DISEASE" rows, but disk space is cheap, and look at the simplification we have achieved by not having to babysit all those old-fashioned tables. And it's not a generic design any more, is it? It's concrete.

The table would look something like this:

ENTITYID ENTITYTYPE NAME      PRICE DIET  COLOUR ANNUAL_TURNOVER
-------- ---------- --------- ----- ----- ------ ---------------
1        PERSON     William
2        FRUIT      Banana                Yellow
3        COMPANY    Megacorp                     100000000
4        ANIMAL     Fruitbat        Fruit
5        SNACK      Snickers  0.4

accompanied by a generic RELATIONS table like this:

ENTITY1 ENTITY2 RELATIONSHIP
------- ------- ------------
3       1       EMPLOYS
1       2       EATS
1       5       EATS

Want to list the snacks eaten by Megacorp employees? Simple:

SELECT emp.entityid, emp.name, snack.name, snack.price
FROM   entities emp
       JOIN relations emprel
       ON  emprel.entity2 = emp.entityid
       AND emprel.relationship = 'EMPLOYS'
 
       JOIN entities com
       ON com.entityid = emprel.entity1
       AND com.entitytype = 'COMPANY'
 
       JOIN relations snrel
       ON  snrel.entity1 = emp.entityid
       AND snrel.relationship = 'EATS'
 
       JOIN entities snack
       ON  snack.entityid = snrel.entity2
       AND snack.entitytype = 'SNACK'
 
WHERE  emp.entitytype = 'PERSON'
AND    com.name = 'Megacorp';

Want to make FRUITBAT an employee of SNICKERS?

INSERT INTO relations VALUES (5, 4, 'EMPLOYS');

The thread becomes increasingly surreal as more and more posters suggest likely issues, from performance (he's prototyped it and the slowdown is insignificant) to complexity (the code will be generated dynamically from an object library) and the limited number of columns per table in Oracle (he might go with MySQL) while Erdem remains cheerfully confident that it will work (it won't).

My thanks to 3360 for sharing this. Send your WTFs to us at OracleWTF@bigfoot.com.

Counting Sheep

2006-02-06T14:01:00.000Z

I'd not seen this Oracle Forums thread before, though it started in 2002 and now has 198 replies. Somebody once asked for some PL/SQL coding standards, someone else offered to email some, and then for ever after gets bombarded with requests from other people saying "Please send same to me at another-idiot-sheep@nobrain.com" Every now and then someone kindly posts a URL to some PL/SQL standards on the web, or points out that these people are just getting their email addresses onto a lot of spam mailing lists, but on and on they go asking for a copy to be sent direct to them. It's surprisingly funny.

I Object, Your Honour...

2006-02-05T17:00:00.000Z

Erm, excuse me for interrupting, but what exactly is this?

There are 2 ways to construct an ANYDATA. The CONVERT* calls enable construction of the ANYDATA in its entirety with a single call. They serve as explicit CAST functions from any type in the Oracle ORDBMS to ANYDATA.

(Found here in the 10.1 documentation).

INTEGER Type is Platform-Independent shock

2006-01-31T19:09:00.000Z

We are grateful to oracleplsqlprogramming.com for their December 2005 Tip of the Month: Insights into PL/SQL Integers, in which we learn this:

INTEGER - defined in the STANDARD package as a subtype of NUMBER, this datatype is implemented in a completely platform-independent fashion, which means that anything you do with NUMBER or INTEGER variables should work the same regardless of the hardware on which the database is installed.

And thank goodness for that, is what we say. Sometimes you just don't need platform-dependent results from your PL/SQL integer calculation depending on the hardware on which the database is installed.

When is a BLOB not a blob?

2006-01-19T16:43:00.000Z

When it's a Bee-Lob, apparently. If think you know how to pronounce some of the more common Oracle-related words, you have to check Eddie Awad's post, "Char or Car", and the follow-up comments...

awads.net/wp/2006/01/18/char-or-car

Two days before the day after tomorrow

2006-01-18T12:06:00.000Z

Clearly a South Park fan worked here once:

cat oracle_GetThisworkingDay

DATE=`date +%Y%m%d%H%M`
CUTOFF=$2

#!/bin/ksh
# oracle_GetThisworkingDay
# Script to retrieve the current working day (YYYYMMDD) from
# the working_calendar table in the Oracle database.
oracle_GetPreviousWorkingDay `oracle_GetNextWorkingDay $DATE $CUTOFF`

I'll spare you the contents of these scripts. Suffice to say they call the following procedures:

   FUNCTION previous_day (
      p_date                              DATE DEFAULT SYSDATE
   )
      RETURN VARCHAR2
   IS
      v_result                      VARCHAR2 (10);
   BEGIN
      SELECT dt
        INTO v_result
        FROM working_calendar
       WHERE dt = (SELECT MAX (dt)
                     FROM working_calendar
                    WHERE dt < p_date );
    RETURN TO_CHAR(v_result,'YYYYMMDD');
  END;

( nice use of SQL there ) and of course...

   FUNCTION next_day (
      p_now                               DATE DEFAULT CURRENT_DATE
   )
      RETURN DATE
   IS
      RESULT                        DATE;
   BEGIN
      SELECT MIN (dt)
        INTO RESULT
        FROM working_calendar
       WHERE dt >= p_now + 1;

      RETURN RESULT;
   END;

It's One More, Innit?

2006-01-17T21:08:00.000Z

Thanks to Scott Lynch for submitting an example of how a J2EE application developer just might not trust the database to do its job.

Over to Scott...

From a big bucks retail management system (now owned by a big bucks DBMS vendor).

1. Get NextVal from the sequence.

2. Assign the value, an integer, to a string.

3. Check to see if the string they just created exists.

4. Cast the integer that has been cast to a string, to a BigDecimal.

5. Add 1 to it (because they're obviously smarter than some silly old sequence).

I just love step 3.

Sheer brilliance on that one. And it's repeated for almost every table in this particular little slice of the application.

------------------------------------------------------------------------------------------------

public long getNextId() throws java.sql.SQLException{
   if (conn == null)
   {
      throw new java.sql.SQLException("Connection not set");
   }
   long nextIdLong = 0;
   try
   {
      //Create a statement
      tStmt = conn.createStatement();

      //Create a query string to get all the fields from the table. The
      //presentation layer will decide which field to display
      String query = "SELECT some_seq.nextval FROM dual";

      //The complete query is executed
      rs = tStmt.executeQuery(query);
      rs.next();
      String nextIdString = rs.getString(1);

      if (nextIdString != null) {
         nextIdLong = ((new BigDecimal(nextIdString)).add(new BigDecimal(1))).longValue();
      }

      tStmt.close();

   } catch (SQLException e)
   {
      throw new java.sql.SQLException(e.toString());
   }
   return nextIdLong;
}

Bring out your WTFs

2006-01-12T16:57:00.000Z

Always on the lookout for blog material requiring minimal editorial effort, we welcome your WTF submissions. Amusing and instructive examples of mind-boggling Oracle-related madness (ideally short ones) can now be sent to us at our new e-mail address: oraclewtf@bigfoot.com. Please remember to include "OracleWTF" in your Subject line.

I would also like to welcome our two new contributors, Tony Andrews and Scott Swank.

Universal SQL Performance Improver Discovered

2006-01-12T16:42:00.000Z

In an AskTom thread this week, the poster wrote:

"...I have been told before by several people, and I have implemented myself on several SQLs that adding the clause "AND 1=1" literally to any SQL statement helps improve the performance of the SQL statement dramatically."

And we've all been wasting our time looking for a FAST=TRUE parameter.

Grow Your Own Concurrency Problem

2006-01-03T15:08:00.000Z

What's that? the sound of ORA-00001: approaching...

...
 FUNCTION key_not_in_table(pkey IN INT) RETURN BOOLEAN
 IS
  countkey INT;
 BEGIN
  SELECT count(key) INTO countkey
  FROM key_values WHERE key = pkey;

  IF countkey > 0 THEN
   RETURN FALSE;
  END IF;
  RETURN TRUE;

 END key_not_in_table;
 
 PROCEDURE insert_or_update(pkey IN INT,
   pval IN INT)
 IS
 BEGIN
  IF key_not_in_table(pkey) THEN
   INSERT INTO key_values
   VALUES (key, value, 0);
  ELSE
   UPDATE key_values
   SET value =  pval
   WHERE key = pkey;
  END IF;
 END insert_or_update;

Bring out your WTFs

2006-01-02T17:36:00.000Z

Always on the lookout for blog material requiring minimum editorial effort, we welcome your WTFs. Amusing and instructive examples of mind-boggling Oracle-related madness (ideally short ones) can now be sent to us at our new e-mail address: OracleWTF@bigfoot.com. I would also like to welcome our two new WTF contributors, Tony Andrews and Scott Swank.

Who needs modular code?

2005-12-30T10:46:00.000Z

Wouldn’t it be nice if when people wrote some useful code, they tried to make it suitably modular and reusable? This is what I have to contend with at the moment. We have a large, complex system written in Oracle Forms that we are now partially re-writing in HTMLDB (hurrah!) One function I want to replicate is the ability to change your own Oracle password; the current Forms application has a form to do this that looks like this:

Old Password: [ ] New Password: [ ] Confirm New Password: [ ]

So that should be a moment’s work to redo, right? Wrong. There is a package of procedures for user maintenance that contains the following 2 procedures that are relevant:

PROCEDURE change_password_validate
(p_username         IN VARCHAR2,
 p_old_password     IN VARCHAR2,
 p_new_password     IN VARCHAR2,
 p_confirm_password IN VARCHAR2,
 p_profile_name     IN VARCHAR2,
 p_mode             IN VARCHAR2 DEFAULT 'N',
 p_mask             IN  VARCHAR2) ;

PROCEDURE change_password_process
(p_username IN VARCHAR2,
 p_new_password IN VARCHAR2,
 p_confirm_password IN VARCHAR2,
 p_mask           IN VARCHAR2,
 p_profile_name IN VARCHAR2,
 p_encrypt_pw IN VARCHAR2,
 p_admin_mode IN VARCHAR2 DEFAULT NULL) ;

Some design flaws are immediately evident:

Validation is totally separate from processing. If I choose to, I can skip the validate routine altogether and call the process routine to change the password to anything I like, regardless of whether I get the old password right or confirm it correctly. (Actually, this foolish separation of validation from processing is a company standard!)
I get to choose whether the password is to be stored (in our own application’s USERS table) in encrypted form or not. HTF do I know whether it should be encrypted or not?
I need to supply something called p_mask, which I think may be something to do with the encryption process, or maybe the validation process – none of this is documented of course, or at least nobody knows where any such documentation may be found. I have tried passing the word ‘mask’ and it seems to work, except that all subsequent attempt to change the password then fail on the validation of the “old” password – perhaps because it has been encrypted in an unexpected manner.
I also need to supply something called p_profile_name, which I do happen to know is a user attribute something like a role, stored in the USERS table. Well excuse me, but if I’m passing in the username as a parameter, why should I have to go look up the USERS record and obtain the profile_name value just to pass it into this lazy procedure?
I don’t fully understand p_mode and p_admin_mode either, but at least they have defaults which I assume (for now) I can live with.

So instead of being a 5 minute job, this is probably going to occupy about a day of my time: locating source code (the packages are wrapped in the database), studying source code to see what it is doing. And if all that fails, trying to find someone from the team that wrote the code and ask them to tell me what I should be doing.

EAV nightmare

2005-12-28T10:34:00.000Z

My charitable Christmas mood only goes so far. I'm looking through a spec which reads like a "database design nightmare!" theme advent calendar. Each page reveals a potential disaster more frightening than the one before. This is my favourite new year hang over inducing cocktail of entity attribute values and generic application design all wrapped up in a gloriously mal-specified mess.

Table: Parameters

id  VARCHAR2(50) PRIMARY KEY  --The application requesting the value
identifier NUMBER       PRIMARY KEY  --The name of the parameter
type  CHAR(1) --‘I’, ‘S’ or ‘B’ is type of value for the parameter
string_val VARCHAR2(50) --‘Y’ or ‘N’ or NULL if BOOLEAN or the string 
integer_val NUMBER       -- The integer value

note: The id holds a value comprising the IP address, type of
application and the instance at that IP.

Bracketing frenzy

2005-12-22T18:38:00.000Z

I just found myself decyphering this:

SELECT ...
FROM report_results rpt
WHERE NOT ( (substr(rpt.report_type,2,1) in ('1','2','3','4','5')) and
          (rpt.sig_type = 'ISDNMA') and
          ((rpt.group_ctn != rpt.ctn_prefix||rpt.ctn_suffix) and
           (length(rpt.group_ctn) != length(rpt.ctn_prefix||rpt.ctn_suffix)) and
           ((length(rpt.group_ctn) != (length(rpt.ctn_prefix||rpt.ctn_suffix)-1)
           ))));

Which turns out to mean this:

SELECT ...
FROM   report_results rpt
WHERE  NOT (     SUBSTR(rpt.report_type,2,1) IN ('1','2','3','4','5')
            AND  rpt.sig_type = 'ISDNMA'
            AND  rpt.group_ctn != rpt.ctn_prefix||rpt.ctn_suffix
            AND  LENGTH(rpt.group_ctn) != LENGTH(rpt.ctn_prefix||rpt.ctn_suffix)
            AND  LENGTH(rpt.group_ctn) != LENGTH(rpt.ctn_prefix||rpt.ctn_suffix)-1 );

No wonder there are no brackets left in the shops.

Code Generation Frenzy

2005-11-25T19:20:00.000Z

Today's WTF moment is a bit of a monster, so I will try to explain what it does, and you can skim the actual code (this is probably a good idea) and still appreciate it.

Imagine that you have some static reference tables, let's say ORDER_STATUSES and LOCATIONS (amongst others), and you decide it would be a good idea to build an interface so that any application code can simply retrieve, say, the default order status or the head office location.

Being a bit clever, you notice that all the tables have the same structure, consisting of an ID and a description, and it occurs to you that if you UNIONed them all together, with the table name as a "type" key, you would have that generic lookup table you always wanted, along with a generic cursor to query it. (It avoids having one cursor per table, after all, so there is an efficiency gain already, right?) So you write this:

/* -------------------------------------------------------------------------------------
Name:    xxxx
Author   xxxx
Description: Script to create the types functions dynamically during schema creation
Input parameters: None
----------------------------------------------------------------------------------------*/
set serveroutput on size 1000000

variable v_destination_dir varchar2(256);
variable v_errorfilename varchar2(256);
variable v_datafile_name varchar2(256);

BEGIN
    :v_destination_dir := '&1';
    :v_errorfilename := 'type_model_functions.err';
    :v_datafile_name := 'type_model_functions.lst';
END;
/

DECLARE
    CURSOR c_tables IS 
        SELECT DISTINCT table_name
        FROM   types_functions
        ORDER BY table_name;

    CURSOR c_types IS 
        SELECT table_name, function_name, REPLACE(type_value,'''','''''') type_value 
        FROM   types_functions
        WHERE  function_name IS NOT NULL
        ORDER BY table_name,function_name;

    v_prev_rec number; 
    file_handle  UTL_FILE.FILE_TYPE :=
                 UTL_FILE.FOPEN (:v_destination_dir,  :v_datafile_name, 'W'); 

BEGIN
    UTL_FILE.PUT_LINE(file_handle, 'set define off');
    UTL_FILE.PUT_LINE
    (file_handle, '/*----------------------------------------------------------------');
    UTL_FILE.PUT_LINE
    (file_handle, ' Name: type_model_functions.lst');
    UTL_FILE.PUT_LINE
    (file_handle, ' Description: Functions to return the id from the types tables');
    UTL_FILE.PUT_LINE
    (file_handle, ' (Package Specifications)');
    UTL_FILE.PUT_LINE
    (file_handle, ' Input parameters: None');
    UTL_FILE.PUT_LINE
    (file_handle, ' Author: xxxx');
    UTL_FILE.PUT_LINE
    (file_handle, chr(10));
    UTL_FILE.PUT_LINE
    (file_handle, '-----------------------------------------------------------------*/');
    UTL_FILE.PUT_LINE(file_handle, chr(9)|| 'CREATE OR REPLACE VIEW types_view AS ');
    v_prev_rec := 0;

    FOR r_tables IN c_tables LOOP
        IF v_prev_rec <> 0 THEN
            UTL_FILE.PUT_LINE(file_handle, chr(9)|| 'UNION');
        END IF;

        UTL_FILE.PUT_LINE
        ( file_handle
        , chr(9)|| 'SELECT typeid, typelabel, ''' ||
          r_tables.table_name||''' tablename from '||r_tables.table_name );

        v_prev_rec := c_tables%rowcount;
    END LOOP;

    UTL_FILE.PUT_LINE(file_handle, '/');
    UTL_FILE.PUT_LINE(file_handle, 'show err');
    UTL_FILE.PUT_LINE(file_handle, 'drop public synonym types_view ;');
    UTL_FILE.PUT_LINE(file_handle, 'create public synonym types_view for types_view ;');
    UTL_FILE.PUT_LINE(file_handle, 'grant select on types_view to public;');

    UTL_FILE.PUT_LINE(file_handle, 'CREATE OR REPLACE PACKAGE types AS ');

    FOR r_types IN c_types LOOP
        UTL_FILE.PUT_LINE
        ( file_handle
        , chr(9) ||
          '-- Function to return the typeid for ' ||
          r_types.type_value || ' ' || r_types.table_name );

        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| 'FUNCTION '||r_types.function_name||' RETURN NUMBER;');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| 'PRAGMA RESTRICT_REFERENCES ('||r_types.function_name||', WNDS);');
    END LOOP;

    UTL_FILE.PUT_LINE(file_handle, 'END types;');
    UTL_FILE.PUT_LINE(file_handle, '/');
    UTL_FILE.PUT_LINE(file_handle, 'show err');
    UTL_FILE.PUT_LINE(file_handle, chr(10));

    UTL_FILE.PUT_LINE
    (file_handle, 'CREATE OR REPLACE PACKAGE BODY types AS ');
    UTL_FILE.PUT_LINE
    (file_handle, chr(9)||'CURSOR c_types_view(p_typelabel varchar2, p_tablename varchar2) IS ');
    UTL_FILE.PUT_LINE
    (file_handle, chr(9)||'SELECT typeid from types_view ');
    UTL_FILE.PUT_LINE
    (file_handle, chr(9)||'WHERE  typelabel = p_typelabel and tablename = p_tablename ;');

    FOR r_types IN c_types LOOP
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| 'FUNCTION '|| r_types.function_name||' RETURN NUMBER IS ');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'v_typeid'||chr(9)||'NUMBER; ');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| 'BEGIN');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'OPEN  c_types_view (''' ||
                                        r_types.type_value||''','''||r_types.table_name||''');');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'FETCH c_types_view INTO v_typeid;');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'IF c_types_view%NOTFOUND THEN ');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| chr(9)|| 'RAISE no_data_found; ');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'END IF; ');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'CLOSE  c_types_view;');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'RETURN v_typeid; ');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| 'EXCEPTION');
        UTL_FILE.PUT_LINE
        (file_handle, chr(9)|| chr(9)|| 'WHEN OTHERS THEN');

        UTL_FILE.PUT_LINE
        ( file_handle
        , chr(9)|| chr(9)|| chr(9) ||
          'RAISE_APPLICATION_ERROR(-20001, ''Function types.' ||
          r_types.function_name || ': '''||'||sqlerrm );' );

        UTL_FILE.PUT_LINE(file_handle, chr(9)|| 'END '|| r_types.function_name|| ';');
    END LOOP;

    UTL_FILE.PUT_LINE(file_handle, 'END types;');
    UTL_FILE.PUT_LINE(file_handle, '/');

    UTL_FILE.PUT_LINE(file_handle, 'show err');

    UTL_FILE.PUT_LINE(file_handle, 'drop public synonym types;');
    UTL_FILE.PUT_LINE(file_handle, 'create public synonym types for types;');
    UTL_FILE.PUT_LINE(file_handle, 'grant execute on types to public;');

    UTL_FILE.PUT_LINE(file_handle, 'exit');

    UTL_FILE.FCLOSE (file_handle);
END;
/

@&1/type_model_functions.lst

exit

Now, given a set of values in TYPES_VALUES like this:

TABLE_NAME      FUNCTION_NAME                TYPE_VALUE
--------------- ---------------------------- ---------------
order_statuses  get_default_order_status     Default
order_statuses  get_order_cancelled_status   Cancelled
order_statuses  get_order_dispatched_status  Dispatched
order_statuses  get_order_closed             Closed
locations       get_head_office_location     Head Office
locations       get_accounts_office_location Accounts

the script generates something like this:

set define off
/*----------------------------------------------------------------------------
Name: type_model_functions.lst
Description: Packaged functions to return the typeid from the types tables
Input parameters: None
Author: xxxx
-----------------------------------------------------------------------------*/

CREATE OR REPLACE VIEW types_view AS 
SELECT typeid, typelabel, 'locations' tablename from locations
UNION
SELECT typeid, typelabel, 'order_statuses' tablename from order_statuses
/
show err
drop public synonym types_view ;
create public synonym types_view for types_view ;
grant select on types_view to public;

CREATE OR REPLACE PACKAGE types AS 
    -- Function to return the typeid for Accounts locations
    FUNCTION get_accounts_office_location RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES (get_accounts_office_location, WNDS);

    -- Function to return the typeid for Head Office locations
    FUNCTION get_head_office_location RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES (get_head_office_location, WNDS);

    -- Function to return the typeid for Default order_statuses
    FUNCTION get_default_order_status RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES (get_default_order_status, WNDS);

    -- Function to return the typeid for Cancelled order_statuses
    FUNCTION get_order_cancelled_status RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES (get_order_cancelled_status, WNDS);

    -- Function to return the typeid for Closed order_statuses
    FUNCTION get_order_closed RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES (get_order_closed, WNDS);

    -- Function to return the typeid for Dispatched order_statuses
    FUNCTION get_order_dispatched_status RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES (get_order_dispatched_status, WNDS);
END types;
/
show err


CREATE OR REPLACE PACKAGE BODY types AS 
    CURSOR c_types_view(p_typelabel varchar2, p_tablename varchar2) IS 
    SELECT typeid from types_view 
    WHERE  typelabel = p_typelabel and tablename = p_tablename ;

    FUNCTION get_accounts_office_location RETURN NUMBER IS 
        v_typeid NUMBER; 
    BEGIN
        OPEN  c_types_view ('Accounts','locations');
        FETCH c_types_view INTO v_typeid;
        IF c_types_view%NOTFOUND THEN 
            RAISE no_data_found; 
        END IF; 
        CLOSE  c_types_view;
        RETURN v_typeid; 
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR
            ( -20001, 'Function types.get_accounts_office_location: '||sqlerrm );
    END get_accounts_office_location;

    FUNCTION get_head_office_location RETURN NUMBER IS 
        v_typeid NUMBER; 
    BEGIN
        OPEN  c_types_view ('Head Office','locations');
        FETCH c_types_view INTO v_typeid;
        IF c_types_view%NOTFOUND THEN 
            RAISE no_data_found; 
        END IF; 
        CLOSE  c_types_view;
        RETURN v_typeid; 
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR
            ( -20001, 'Function types.get_head_office_location: '||sqlerrm );
    END get_head_office_location;

    FUNCTION get_default_order_status RETURN NUMBER IS 
        v_typeid NUMBER; 
    BEGIN
        OPEN  c_types_view ('Default','order_statuses');
        FETCH c_types_view INTO v_typeid;
        IF c_types_view%NOTFOUND THEN 
            RAISE no_data_found; 
        END IF; 
        CLOSE  c_types_view;
        RETURN v_typeid; 
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR
            ( -20001, 'Function types.get_default_order_status: '||sqlerrm );
    END get_default_order_status;

    FUNCTION get_order_cancelled_status RETURN NUMBER IS 
        v_typeid NUMBER; 
    BEGIN
        OPEN  c_types_view ('Cancelled','order_statuses');
        FETCH c_types_view INTO v_typeid;
        IF c_types_view%NOTFOUND THEN 
            RAISE no_data_found; 
        END IF; 
        CLOSE  c_types_view;
        RETURN v_typeid; 
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR
            ( -20001, 'Function types.get_order_cancelled_status: '||sqlerrm );
    END get_order_cancelled_status;

    FUNCTION get_order_closed RETURN NUMBER IS 
        v_typeid NUMBER; 
    BEGIN
        OPEN  c_types_view ('Closed','order_statuses');
        FETCH c_types_view INTO v_typeid;
        IF c_types_view%NOTFOUND THEN 
            RAISE no_data_found; 
        END IF; 
        CLOSE  c_types_view;
        RETURN v_typeid; 
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR
            ( -20001, 'Function types.get_order_closed: '||sqlerrm );
    END get_order_closed;

    FUNCTION get_order_dispatched_status RETURN NUMBER IS 
        v_typeid NUMBER; 
    BEGIN
        OPEN  c_types_view ('Dispatched','order_statuses');
        FETCH c_types_view INTO v_typeid;
        IF c_types_view%NOTFOUND THEN 
            RAISE no_data_found; 
        END IF; 
        CLOSE  c_types_view;
        RETURN v_typeid; 
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR
            ( -20001, 'Function types.get_order_dispatched_status: '||sqlerrm );
    END get_order_dispatched_status;
END types;
/
show err
drop public synonym types;
create public synonym types for types;
grant execute on types to public;
exit

Now all you have to do is ensure that there are values in the LOCATIONS table with typelabels 'Head Office', 'Accounts' and so on, and whenever you run the script it will create or replace a whole new package implementing whatever you put in TYPES_FUNCTIONS, and application code can simply invoke types.get_accounts_office_location().

Now, I wonder whether there is a way to generate the TYPES_FUNCTIONS table...

When RTFM Goes Wrong IV

2005-11-11T20:51:00.000Z

The sheer size of Oracle's documentation set always astonishes me, and it's a wonder that any of it ever manages to evolve across versions. But sure enough, the important bits usually get a brush-up during major release changes and a few new bits get thrown in. But not so for a particularly dry part of Chapter 2 of the "Application Developers Guide - Fundamentals" for 10g Release 2 (Chapter 5 in earlier versions), subtitled "Improving Transaction Performance".

Remember this is 10.2 - it's bang up to date as far as Oracle releases go. I don't think that any further comment is required here else the post will get too long so I'll just send you to the link and suggest you take particular note of the comments on stored PL/SQL functions, ANALYZE and explicit cursors.

http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqlproc.htm#sthref179

The rollback segment entry can be forgiven as not everyone is using UNDO yet (though I always hated it as a piece of advice when I used rollback segments).

You can never have too much redirection

2005-11-11T19:13:00.000Z

And who needs PL/SQL anyway when you already have all the procedural control you need, thanks to Mr Stephen Bourne and his excellent Shell?

echo -------------------------------------  >> $LOGS/$MY_LOGFILE_NAME
echo Load the xxx schema tables for xyz >> $LOGS/$MY_LOGFILE_NAME
echo ------------------------------------- >> $LOGS/$MY_LOGFILE_NAME
sqlplus -s $XXX_USERNAME/$XXX_PASSWORD @$XYZ_BIN/xyz_load_xxx_data.sql $LOGS $MY_ERRORFILE_NAME \
>> $LOGS/$MY_LOGFILE_NAME
retcode=`echo \$?`
if [ "$retcode" -ne 0 ] ;then
 echo Step failed >> $LOGS/$MY_LOGFILE_NAME
 exit $RETURN_GENERIC_FAIL
fi

echo ------------------------------------  >> $LOGS/$MY_LOGFILE_NAME
echo Processing abc files >> $LOGS/$MY_LOGFILE_NAME
echo ------------------------------------ >> $LOGS/$MY_LOGFILE_NAME
$xyz_BIN/xyz_process_abc_ndt_files.pl
retcode=`echo \$?`
if [ "$retcode" -ne 0 ] ;then
 echo Step failed >> $LOGS/$MY_LOGFILE_NAME
 exit $RETURN_GENERIC_FAIL
fi

echo ------------------------------------  >> $LOGS/$MY_LOGFILE_NAME
echo Processing MF files >> $LOGS/$MY_LOGFILE_NAME
echo ------------------------------------ >> $LOGS/$MY_LOGFILE_NAME
$xyz_BIN/xyz_process_def_ndt_files.pl
retcode=`echo \$?`
if [ "$retcode" -ne 0 ] ;then
 echo Step failed >> $LOGS/$MY_LOGFILE_NAME
 exit $RETURN_GENERIC_FAIL
fi

echo -------------------------------------  >> $LOGS/$MY_LOGFILE_NAME
echo Merge MF and customer tables for xyz >> $LOGS/$MY_LOGFILE_NAME
echo ------------------------------------- >> $LOGS/$MY_LOGFILE_NAME
sqlplus -s $XYZ_USERNAME/$XYZ_PASSWORD \
 @$XYZ_BIN/xyz_merge_mf_and_customer_tables.sql $LOGS $MY_ERRORFILE_NAME \
>> $LOGS/$MY_LOGFILE_NAME
retcode=`echo \$?`
if [ "$retcode" -ne 0 ] ;then
 echo Step failed >> $LOGS/$MY_LOGFILE_NAME
 exit $RETURN_GENERIC_FAIL
fi

echo -------------------------------------  >> $LOGS/$MY_LOGFILE_NAME
echo Populate equipment table for xyz >> $LOGS/$MY_LOGFILE_NAME
echo ------------------------------------- >> $LOGS/$MY_LOGFILE_NAME
sqlplus -s $XYZ_USERNAME/$XYZ_PASSWORD \ 
 @$XYZ_BIN/xyz_create_equipment.sql $LOGS $MY_ERRORFILE_NAME \
 >> $LOGS/$MY_LOGFILE_NAME
retcode=`echo \$?`
if [ "$retcode" -ne 0 ] ;then
 echo Step failed >> $LOGS/$MY_LOGFILE_NAME
 exit $RETURN_GENERIC_FAIL
fi

echo -------------------------------------  >> $LOGS/$MY_LOGFILE_NAME
echo Populate locations table for xyz >> $LOGS/$MY_LOGFILE_NAME
echo ------------------------------------- >> $LOGS/$MY_LOGFILE_NAME
sqlplus -s $XYZ_USERNAME/$XYZ_PASSWORD \
 @$XYZ_BIN/xyz_create_locations.sql $LOGS $MY_ERRORFILE_NAME \
 >> $LOGS/$MY_LOGFILE_NAME
retcode=`echo \$?`
if [ "$retcode" -ne 0 ] ;then
 echo Step failed >> $LOGS/$MY_LOGFILE_NAME
 exit $RETURN_GENERIC_FAIL
fi

runendt=`date`
echo ---------------------------------------------------- >> $LOGS/$MY_LOGFILE_NAME
echo "xyz dataload Ended - $runendt ....." >> $LOGS/$MY_LOGFILE_NAME
echo ---------------------------------------------------- >> $LOGS/$MY_LOGFILE_NAME
echo >> $LOGS/$MY_LOGFILE_NAME

exit $RETURN_SUCCESS

Spot the difference

2005-10-14T21:11:00.000+01:00

Prog rock

2005-10-07T23:58:00.000+01:00

Anyone who remembers The Ramones will have happy memories of their toetappingly monolithic slabs of pure punk pop. After a couple of albums of two-chord magic, however, there were those who said it was starting to sound samey, and I believe it was Joey Ramone's dad who commented in an interview that perhaps the boys might want to "complicate it up a bit".

It seems the authors of Oracle Database 10g PL/SQL Programming (Oracle Press, 2004) chose to follow similar advice in their chapter on working with 8i, er, I mean 10g collections. Anyone looking for a funkier way to DELETE FROM addresses WHERE address_id = 11, for example, need look no further:
www.peakretrieval.com/plsql/Chapter6/varray_dml1.sql

And while we're at it,

SELECT column_value
FROM   THE (SELECT CAST(emp_address AS varray_nested_table )
            FROM   emp
            WHERE  empno = 11);

(love that type name btw) could also be written as

SELECT column_value
FROM   emp, TABLE(emp_address)
WHERE  empno = 11;

but where's the fun in that?

You can never have too many tables

2005-10-04T00:22:00.000+01:00

There is no problem that cannot be solved with a loop and some dynamic SQL.

Problem: Your report can return rows for more than one feature code. How on Earth are you going to store the results?

Solution: Loop through the FEATURES table, creating three dynamically named tables for each feature code. Problem solved.

BEGIN
FOR rec IN
    ( SELECT f.short_code ft FROM features f
      WHERE  f.chargeable = 'Y' )
LOOP
    BEGIN
        EXECUTE IMMEDIATE 'drop table r_t8_' || rec.ft ;
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

    EXECUTE IMMEDIATE 'create table r_t8_' || rec.ft || '(' ||
    'report_type varchar2(50),' ||
    'report_date date,' ||
    'switch_id number,' ||
    'feat_count number,' ||
    'rental_rate number,' ||
    'discount number,' ||
    'rental_rating number)' ;

    BEGIN
        EXECUTE IMMEDIATE 'drop table r_t7_' || rec.ft ;
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

    EXECUTE IMMEDIATE 'create table r_t7_' || rec.ft || '(' ||
    'report_type varchar2(50),' ||
    'report_date date,' ||
    'switch_id number,' ||
    'feat_count number,' ||
    'rental_rate number,' ||
    'discount number,' ||
    'rental_rating number)' ;

    BEGIN
        EXECUTE IMMEDIATE 'drop table r_t9_' || rec.ft ;
    EXCEPTION WHEN OTHERS THEN
        NULL;
    END;

    IF rec.ft <> 'ZZ' THEN
        EXECUTE IMMEDIATE 'create table r_t9_' || rec.ft || '(' ||
        'report_type varchar2(50),' ||
        'report_date date,' ||
        'switch_id number,' ||
        'feat_count number,' ||
        'rental_rate number,' ||
        'discount number,' ||
        'rental_rating number)' ;
    END IF;
END LOOP;
END;
/

Now, how to write those reports so they populate the right tables? I know...

Watching the Watcher-Watchers

2005-10-03T13:56:00.000+01:00

I've just found a process that uses on-update triggers to insert rows into a table:

Queue_Monitor_Table
( source_table_name varchar2(30)
  rowid             varchar2(16) /*lovely,  but not the point*/
  enqueued_yn       char(1)
  dequeued_yn       char(1) )

This is processed by a pl/sql "queue_monitor_listener" that is started in a background process. A "queue_monitor_listener" if you can't guess, repeatedly scans the table for rows where enqueued_yn = 'N' and then it enqueues the rowid to an AQ queue, called the "row_update_queue". When it's happy that a row has been enqueued, it updates the enqueued_yn flag to "Y". As you might fear, there is also dequeue process. This also runs in a background job. When this has successfully dequeued from the row_update_queue, and done its task ( which could be the subject of a new WTF ), it updates the Queue_Monitor_Table to set dequeued_yn = 'Y'. Nice. The fun really seems to have begun when, in order to improve performance, it was decided that several "queue_monitor_listener" processes should be started in parallel background processes.

Never do in SQL what you can do in PL/SQL

2005-09-29T08:15:00.000+01:00

Back when I first started out as a trainee Forms developer, one of my colleagues had the bright idea of implementing some "CONNECT BY PRIOR" logic step by step, in Forms. The trigger would add a new blank record to the block, then retrieve the next row into it with SELECT INTO, then loop around until there were no more rows to process or Hell froze over. Perhaps unsurprisingly, it was a close thing.

I mentioned to Scott Swank that we were always on the lookout for material, and he sent me the following code that he'd come across (after editing it for brevity and anonymity), and somehow it reminded me of that old Forms trigger. This pattern is repeated ten times throughout the code, checking for various child attributes by (how else?) looping through all the records one by one until the desired attribute is found not to be true (and if something is not untrue then it must be true, right?)

PROCEDURE has_an_attribute
    ( x IN VARCHAR2, resultout OUT VARCHAR2 )
IS
    l_parent_id   VARCHAR2 (30);

    CURSOR c_child
    IS
        SELECT an_attribute
        FROM   child_table
        WHERE  parent_id = l_parent_id;
BEGIN
    l_parent_id := some_function(x);
    resultout := 'COMPLETE:Y';

    FOR v_child IN c_child
    LOOP
        IF v_child.an_attribute = 'N'
        THEN
            resultout := 'COMPLETE:N';
            EXIT;
        END IF;
    END LOOP;
END;

Check List

2005-09-28T17:18:00.000+01:00

( ) Written dodgy concatenated embedded sql?
( ) Wrapped it in shell script?
( ) Littered it with literals?
( ) Included some random control characters?
( ) Released it without checking it works?

( ) Remembered to add the sarcastic comment questioning someone else's ability?

# formatting had to be included in select statement
# could this suggest the database could use some normalisation?
strqry="select invoice_line_items.product_code||"
strqry=$strqry"invoice_line_items.customer_type||"
strqry=$strqry"invoice_line_items.product_sku||\n^D"
strqry=$strqry"substr(expiry_date,3,4)||"
strqry=$strqry"to_char(trunc(instant_cost/10000),'FMX')||"
strqry=$strqry"to_char(mod(instant_cost,10000),'FM0999')||"
strqry=$strqry"contract_type"
strqry=$strqry"from invoice_line_items, retail_calendar, customer_contract,"
strqry=$strqry"product_domain"
strqry=$strqry"where last_retail_day >= retail_calendar.retail_day"
strqry=$strqry"and first_retail_day <= retail_calendar.retail_day"
strqry=$strqry"and retail_calendar.day = $day"
strqry=$strqry"and retail_calendar.month = $month"
strqry=$strqry"and retail_calendar.year = $year"
strqry=$strqry"and invoice_line_items.line_code='$flag'"
strqry=$strqry"and customer_contract.line_code='$flag'"
strqry=$strqry"and invoice_line_items.product_code=customer_contract.product_code"
strqry=$strqry"and invoice_line_items.customer_type="
strqry=$strqry"      customer_contract.customer_type"
strqry=$strqry"and invoice_line_items.product_sku="
strqry=$strqry"      customer_contract.product_sku"
if [ $host_id != "-1" ]; then
        strqry=$strqry" and product_domain.host_id = $host_id"
fi
strqry=$strqry"and invoice_line_items.product_code=product_domain.product_code"
strqry=$strqry"and invoice_line_items.customer_type="
strqry=$strqry"      product_domain.customer_type"
strqry=$strqry"and invoice_line_items.product_sku="
strqry=$strqry"      product_domain.product_sku;"

Fun with String

2005-09-22T19:13:00.000+01:00

It seems that my predecessor needed to convert a row of five columns into a column with five rows. Simple enough, you might think, once you realise that you can use a handy collection type in a TABLE() expression. But hey, why stop there when there is a much, much harder way involving a string-to-table function and a self-join on three columns? ...that aren't quite unique... (Table and column names changed to protect the guilty)

SELECT ...
FROM   huge_table t
     , TABLE ( SELECT utils.string_to_table
                      ( t2.col1 || ',' || t2.col2 || ',' ||
                        t2.col3 || ',' || t2.col4 || ',' || t2.col5)
               FROM   huge_table t2
               WHERE  t2.switch_id = t.switch_id
               AND    t2.switch_ctn = t.switch_ctn
               AND    t2.phone_prefix = t.phone_prefix
               AND    ROWNUM < 2 )

Now if that isn't half ample in the WHERE, I don't know what is.

Joins explained

2005-09-15T08:13:00.000+01:00

I must first give credit to Padders for spotting this article a couple of months back, and to Adrian for reminding me about it. He was poised to blog it when Thai pointed out that it had recently been added to Database Debunkings' "To Laugh Or Cry" hall of shame. But of course there is no honour among bloggers and it's damned funny, so here it is. Remember to print out the following definitions and keep them by your desk in case you forget what a complex right inner join is:

Don't use joins
Joins become a problem with Oracle and SQL Server DBMSs because the two systems have fundamentally different approaches to the concept. Basically, joins don’t always work the same on every DBMS, and you can end up with unexpected result sets. Before we talk about the differences and how to get around them, you should understand the basic types of join clauses:
Join
Joins are powerful SQL commands for creating tables from data that is retrieved from multiple sources.
Equi-Join
Equi-Joins are formed by retrieving all the data from two separate sources and combining it into one, large table.

Inner/Outer Join
Inner Joins are joined on the inner columns of two tables. Outer Joins are joined on the outer columns of two tables.

Left/Right Join
Left Joins are joined on the left columns of two tables. Right Joins are joined on the right columns of two tables.

Compound/Complex Joins
There are also other kinds of joins—left/inner, left/outer, right/inner, and right/outer.
The important thing to remember about joins is to not use them if you want your application to work well with different database servers.

I love the way that it's exactly the kind of thing you would make up, if you were 12 and had absolutely no idea how to answer a homework question.

While I'm at it I can't help quoting a bit more, this time about the well-known ADD command:

ANSI SQL: CREATE, DROP, ADD, UPDATE, DELETE, INSERT, SELECT
As a rule, the only commands you should use are:
CREATE and DROP for Data Definition Language (DDL).

ADD, UPDATE, DELETE and INSERT for Data Manipulation Language (DML).

SELECT for data retrieval.

From the article "Database design for platform independence", Builder.com 2002.

Twirled Half Ample in the WHERE

2005-09-09T13:10:00.000+01:00

It is of course completely unfair to post this message by someone who clearly does not speak English. Actually you have to admire his valiant effort to describe a problem with dynamic ref cursors in a foreign language, through what must be the world's most eccentric translation software, or possibly using only a rather old dictionary. (Credit also goes to Art Metzer who actually provided a solution.) But it remains our all-time favourite Oracle forum post, so here it is:

Hi!!
I am with one doubts, I mounted one procedure that to need to return a cursor.

The STRING SQL is must be twirled half ample in the "WHERE".

For that haven't to make varios ifs, would like to know if is possible to create one parameter that it comes with where and to place in the routine to twirl, I tried to make this but not gave certain.

It below follows:
OPEN RCURSOR FOR SELECT * FROM TABELA1 WHERE CAMPO1 < 10 || VARIAVEL1
In var(VARIAVEL1) it would be:
'AND CAMPO2 = 3 AND CAMPO3 > = 25'
For example, this would be possible?

Or I will have exactly that to mount one 'IF' giant for to treat all the routine.

Tks.

See www.orafaq.com/forum/mv/msg/4467/12407/0 for the original post.

If at first you don't succeed

2005-09-03T14:34:00.000+01:00

Just came across this in a rather large query I have to fix:

NVL(di_core.di_tools.area_code_translate(v_switch_id,d.dncode),
    di_core.di_tools.area_code_translate(v_switch_id,d.dncode))

Mail it

2005-09-02T23:38:00.000+01:00

I recently received the following request on a web forum:

Hello,

Please let me know how can we send an oracle database via e-mail to other people. Please reply ASAP as it is very urgent. The project is struck because of this.

Thanks in advance

Hmm. I replied asking for some descripton of the business requirement, and mentioned exp/imp utilities, .dmp files being typically smaller than an entire database, possibly XML, etc. Back came the reply:

Thank you sir for your inputs.

Actually I am from core electronics, VLSI background.

We need to configure a database (it contains all the information of components that can be used).

I have worked with Access database(which we can send and receive by zipping and sending).

Now the problem is that the person1 who is else where in globe has to send me the oracle database. Now we dont know how do we do that.We dont even have those passwords or anything... I just have no clue wht it is like .. wht options.. nothing

Now could you please tell me how can we send the database

Thanks

Oh dear. What is the file extension of the files they are sending you? I asked. Do you have an Oracle license? I must admit I had to look up VLSI ("Very Large Systems Integration", of course). And then when all hope seemed lost:

got my way

I asked the person having database to put it on FTP...

i would then be able to get that whole folder and hence the database...

Thanks for your time

Problem solved I think.

I'm not making it up

2005-08-31T11:45:00.000+01:00

This gem is used at times of particularly high throughput to monitor the size of queues.

PROCEDURE getqueuecount (
   queuename                  IN       VARCHAR2
  ,agentname                  IN       VARCHAR2
  ,queuecount                 OUT      INTEGER
)
IS
   queueoptions        DBMS_AQ.dequeue_options_t;
   messageproperties   DBMS_AQ.message_properties_t;
   MESSAGE             connect_db_external_q_type;
   messageid           RAW (16);
   nodequeuewaiting    EXCEPTION;
   dequeuewaiting      BOOLEAN                      := TRUE;
   nolock              EXCEPTION;
   PRAGMA EXCEPTION_INIT (nolock, -054);
   PRAGMA EXCEPTION_INIT (nodequeuewaiting, -25228);
BEGIN
   LOCK TABLE q_lock_table IN EXCLUSIVE MODE NOWAIT;
   queuecount                 := 0;
   queueoptions.WAIT          := 1;
   queueoptions.consumer_name := agentname;
   queueoptions.navigation    := DBMS_AQ.first_message;

   BEGIN
      WHILE (dequeuewaiting)
      LOOP
         DBMS_AQ.dequeue (queuename
                         ,queueoptions
                         ,messageproperties
                         ,MESSAGE
                         ,messageid
                         );
         queuecount  :=   queuecount  + 1;
      END LOOP;
   EXCEPTION
      WHEN nodequeuewaiting
      THEN
         dequeuewaiting             := FALSE;
   END;

   ROLLBACK;
EXCEPTION
   WHEN nolock
   THEN
      queuecount                 := -1;
END;

Nesting instinct

2005-08-30T17:22:00.000+01:00

It is said that the human brain can retain only seven facts in short term memory at one time. Or something like that. Test yourself anyway with the following example, which occurs in the middle of a 175-line INSERT statement, in a 1200-line package body, all written in much the same self-explanatory style:

INSERT INTO some_unsuspecting_table
WITH all_dn AS (SELECT ... FROM ...)
SELECT ...
       CASE WHEN SUBSTR(all_dn.dn_data,6,4) IN ('PODN','UNDN')
           THEN SUBSTR(all_dn.dn_data,6,4)
           ELSE
               NVL2
               ( NVL(l.len, substr(a.features,16,15)),
                 DECODE(MAX(CASE WHEN all_dn.dn_data LIKE '%MSN%'
                                 THEN 2
                                 ELSE
                                     CASE WHEN all_dn.dn_data LIKE '%TWDN%'
                                          THEN 1
                                     END
                            END)
                        OVER (PARTITION BY NVL(l.len, SUBSTR(a.features,16,15) ) ),
                        2, 'MSN',
                        1, 'TWDN', 'LINE' ),
                 CASE WHEN all_dn.dn_data LIKE '%MSN%'
                      THEN 'MSN'
                      ELSE all_dn.dn_data
                 END )
       END main_service_type
     , ...and so on...
FROM   etc etc;

Now, how many levels of nesting was that? What, you passed out somewhere around OVER (PARTITION BY NVL(l.len, SUBSTR(a.features,16,15) ) )? Shame on you.

The little things that snowball

2005-08-30T16:39:00.000+01:00

There's nothing more I can say about this.

CREATE TABLE INVOICE_LINEITEM 
(
...
...
MONTH_OF_YEAR   CHAR(1)
...
)

Avoiding the overhead of SQL

2005-08-30T11:57:00.000+01:00

Ok, I'll go first, even though this is more of a "whyTF". Have a look at this piece of genius. I've only pulled the bare bones because these scripts are HUGE. Names are changed for the usual reasons. dumpdb.sh follows:

#!/bin/ksh

function showtables
{
  cat << EOF
acc:client_account
amt:client_model_type
...
700 lines later
...
ugp:ultimate_group_parameter
vap:validation_parameter
EOF
}

function execSQL
{
eval "echo \"$SQL_HEADER$1$SQLTRAILER\" \
  |sqlplus -S $ORACONNECT   |path=/usr/xpg4/bin:"$PATH" awk -F: $AWK_PROG \
  | sed 's/::/: :/g' | sed 's/~:/:/g' \
  $OUT_FILTER \
  $ OUTPUT"
}
.
. 700 functions like this, one per table.
.
function fDump_client
{
    execSQL "select * from client;"
}
.

for table in $tables_to_dump
do
    if [[ "$TO_TAB_FILE" == "Y" ]]; then
        ## Send output to file named <table>.csv
        export OUTPUT=" > $table.csv"
        echo "Dumping $table to $table.csv"
    fi
    # Get filter if required

    if [[ $? -eq 0 ]]; then
        # There is a specific function for this table so use it
        fDump_${table}

    else
        # No function - just use default SELECT
        execSQL "select * from ${table};"
    fi
done

which is called by "application support scripts" as demonstrated:

#!/bin/ksh

function select_client_access_type
{
    
    # dbselect gives:
    #
    # 1 CLIENT_KEY         NOT NULL CHAR(3)
    # 2 MEMBER_KEY         VARCHAR2(4)
    # 3 NAME               VARCHAR2(50)
    # 4 PHONE              VARCHAR2(22)
    # 5 ACCESS_ENABLED     VARCHAR2(1)
    # 6 ACCESS_TYPE        CHAR(1)
    # 7 MM_AND_OR_CLIENT   VARCHAR2(1)
    # 8 RIGHTS_GROUP_ID    NUMBER(4)
    # 9 MANAGER_ID         NUMBER(38)

    IFS=:
    if ! set -- $(dumpdb | grep "^${CLIENT_KEY}:")
    then
        echo "CLIENT '$CLIENT_KEY' not found"
        exit $EXIT_NO_CLIENT
    fi

    MEMBER_KEY="$2"
    ACCESS_ENABLED="$5"
    ACCESS_TYPE="$6"
    MM_AND_OR_CLIENT="$7"
    RIGHTS_GROUP_ID="$8"

    if [[ $ACCESS_ENABLED != Y ]]
    then
    if [[ $ACCESS_TYPE != T ]]
    then
        echo "CLIENT ACCESS_TYPE is $ACCESS_TYPE"
    fi
    fi
}