Re: Select from dual return 3 rows !
- From: Magnus Andersen <mag.andersen@xxxxxxxxx>
- To: ineyman@xxxxxxxxxxxxxx
- Date: Tue, 8 Nov 2005 16:22:57 -0500
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: