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;

5 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!

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?

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?

Tony Andrews said...

Updated link to original article: http://www.oracle.com/technetwork/issue-archive/o34tech-plsql-090955.html