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

Umm, I forgot my password ....oracle

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.

Error-Prone Error Handling ....oracle

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

Create Your Own DUAL Table ....oracle

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;

What is it with dates? ....oracle

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;

Umm, I forgot my password, Part 2 ....oracle

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.

Simplest row generator meets maximum inefficiency ....oracle

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.

If only there was a SUM function ....oracle

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

The Decibel Method ....oracle

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.

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

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 Clue Was In The Name ....oracle

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.

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

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"

SP2-0552: Bind variable not declared ....oracle

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

Oracle World Goes Sensible shock ....oracle

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.

Code generation nonpareil ....oracle

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.

If in doubt, test, test and test again ....oracle

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

Dynstatic SQL... ....oracle

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.

Why Machines Will Never Take Over The World ....oracle

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.

That Reminds Me... ....oracle

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.

Pause for thought ....oracle

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.

The fine line between clever and stupid ....oracle

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.

Show Me The Money ....oracle

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.

Competition ....oracle

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;

Production-ready code ....oracle

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

A Year of WTFs ....oracle

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:

1. 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 ;-)
2. 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.)
3. 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.
4. 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.
5. 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?

Just, Why? ....oracle

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

Useful books ....oracle

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

Death By Furniture ....oracle

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.

Pause For Thought part 2 ....oracle

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.

Code Re-use ....oracle

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:

1. Use a cursor to load all the rows for that name into an array. (Apparently there were 130+ columns in the original table.)
2. Check array.COUNT to see how many rows there are.
3. 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.
4. 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;

Short loop ....oracle

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;

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

Performance Tuning and The Big O ....oracle

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.

423,551 Invalid Indexes ....oracle

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.

Quote For The Day ....oracle

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

DATE comparisons: the scenic route ....oracle

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.

One liner ....oracle

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

Welcome back ....oracle

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.

What is this "testing" thing? ....oracle

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.

Frameworkia ....oracle

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.

WREAK_APPLICATION_HAVOC ....oracle

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.