Oracle WTF

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.