Bracketing frenzy ....oracle

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.

EAV nightmare ....oracle

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.

Who needs modular code? ....oracle

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.

Bring out your WTFs ....oracle

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.

Grow Your Own Concurrency Problem ....oracle

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;

Universal SQL Performance Improver Discovered ....oracle

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.

Bring out your WTFs ....oracle

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.

It's One More, Innit? ....oracle

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

Two days before the day after tomorrow ....oracle

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;

When is a BLOB not a blob? ....oracle

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

INTEGER Type is Platform-Independent shock ....oracle

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.

I Object, Your Honour... ....oracle

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

Counting Sheep ....oracle

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.

EAV Returns: The Concrete Elephant approach ....oracle

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:

1. It seems like a clever idea at first.
2. 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.

The Phantom's Gonna Git Ya ....oracle

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

Wanna Date? ....oracle

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.

Stop Press: Oracle Granted License To Extend February ....oracle

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

Hey, we do the WTFs... ....oracle

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.

Tip: add DISTINCT to every query ....oracle

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 Part II... ....oracle

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