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

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

www.janus-software.com/fb_fyracle.html

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

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

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

The Clue Was In The Name

email from tester: 

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

reply: 

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

email from tester: 

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

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

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

The Decibel Method

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

hello

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

can anyone throw some light how to do it.

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

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

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

For columns, you should be using so called decibel method: if you suspect that column C1 in table TAB is not used, you can always execute the following commands:

update TAB set C1=NULL;
commit;

If the reaction to that is a loud scream, accompanied by swearwords and a genuine cornucopia of various expletives, you've made a mistake, the column was used. It's time for the "I'm sorry, I didn't know that this column was still being used" routine. You can rest assured that this swearing sucker is gonna be busy for a while.

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

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

Problem solved I think.

Our thanks to Herod T for the plug.

If only there was a SUM function

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

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

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

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

We can but dream.

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

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

    COMMIT;
END LOOP;

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

Simplest row generator meets maximum inefficiency

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

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

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

Umm, I forgot my password, Part 2

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

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

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

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

-- Check if the password is same as the username

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

insert into mytable values ('username','password');

-- Check for the minimum length of the password

Etape 2: run this script as sys

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

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

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

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

insert into mytable values (username,password);

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

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

Many thanks to Andrew P. Clarke for submitting this.

What is it with dates?

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

Over to Graham...

---

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

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

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

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

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

Create Your Own DUAL Table

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

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

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

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

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

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

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

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

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

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

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

END replace_onerow;

Error-Prone Error Handling

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

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

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

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

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

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

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

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

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

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

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

... which is clearly more informative than:

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

Umm, I forgot my password

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.