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;