Tuesday, March 07, 2006

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;

8 comments:

Tony Andrews said...

Well at least we can rest assured that that table can't get corrupted.

Oh, hang on a minute...

SQL> delete onerow;

1 row deleted.

SQL> insert into onerow values ('X');

1 row created.

SQL> insert into onerow values ('Y');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from onerow;

D
-
X
Y

Oops!

Scott Swank said...

Got too many transactions in your general ledger? No problem:

SQL> exec replace_onerow('gl_transactions');

Scott Swank said...

Note that since replace_onerow doesn't even have the good graces to
run with authid current_user it doesn't really matter whether _you_
are allowed to drop gl_transactions. Rather it matters whether the
owner of replace_onerow has such privs. Lovely.

Robert Vollman said...

When I see code like this I think of two words:

SQL Injection

OraWTFGuy said...

>>When I see code like this I think of two words: SQL Injection
I can think of one word: How?

James Padfield said...

Of course if you call the procedure every time before you SELECT from dual then there is no way it can get corrupted.

William Robertson said...

So to summarise: if there is a possibility that your database may find itself with additional rows in SYS.DUAL, worry no longer: just run this handy utility and switch to using the new 'next_pky()' function for sequence value retrieval.

Now if someone hacks into your SYS schema and adds rows to DUAL, you can still continue to generate sequence values without interruption.

Of course a whole load of other things may start breaking all around you, possibly including calls to USER, SYSDATE and SYSTIMESTAMP depending on your Oracle version, because you still have multiple rows in DUAL.

Oh and the trigger doesn't work either, so onerow could have multiple rows in it as well.

I know - why not add a "WHERE ROWNUM = 1" clause to the "SELECT FROM onerow" query?

Mark J. Bobak said...

The source of this "use your own dual" is a Feuerstein article in the May/June 2004 Oracle Magazine. Here's the URL: http://www.oracle.com/technology/oramag/oracle/04-may/o34tech_plsql.html

Back when this first came out, I wrote him a letter, and it was published in the letters to the editor of a subesequent issue of Oracle Magazine.

I pointed out the problem w/ the trigger, suggested a better way to implemnt the table, and make sure that if you're going to bother with your own DUAL replacement, at least make it an IOT, so that you get some scalability improvement to go along with it. Of course, none of that is necessary since 10g, where DUAL is a memory structure, there's no way to add a row to it, and the performance problem associated with it disappears anyways.

-Mark