Re: Select from dual return 3 rows !

From: Magnus Andersen <mag.andersen_at_gmail.com>
Date: Tue, 8 Nov 2005 16:22:57 -0500
Message-ID: <5ea165840511081322v2a176a9el10cee29896082c29@mail.gmail.com>

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.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 08 2005 - 15:25:14 CST