Re: Select from dual return 3 rows !

In the may issue of Oracle Magazine, Steven Feuerstein has an article of how
to write self managing PL/SQL. In the article he recommends to not use the
dual table but to actually create your own. You can find the article here:

http://www.oracle.com/technology/oramag/oracle/04-may/o34tech_plsql.html

and here is how he suggests that you can do it.

 Code Listing 5: Creating a DUAL-like table

   1  CREATE TABLE onerow (dummy VARCHAR2(1))
 2  /
  3  GRANT SELECT ON onerow TO PUBLIC
 4  /
  5  CREATE PUBLIC SYNONYM onerow FOR onerow
 6  /
 7  CREATE OR REPLACE TRIGGER enforce_onerow
 8     BEFORE INSERT
  9     ON onerow
10  DECLARE
11     PRAGMA AUTONOMOUS_TRANSACTION;
12     l_count PLS_INTEGER;
13  BEGIN
14     SELECT COUNT (*)
15       INTO l_count
16       FROM onerow;
17
18     IF l_count = 1
19     THEN
20        raise_application_error
21                        (-20000
22                       , 'The onerow table can have only one row.'
23                        );
24     END IF;
25  END;
26 /


HTH,
--
Magnus Andersen
Systems Administrator / Oracle DBA
Walker & Associates, Inc.

Other related posts: