Re: Select from dual return 3 rows !

And there was a reply that I report.

====================================================================================================================

Two Suggestions for Dual 

Regarding the article "Self-Managing PL/SQL" by Steven Feuerstein in the 
May/June 2004 issue of Oracle Magazine, I wanted to raise two points, both 
regarding the DUAL table. Might I suggest that a user create his or her own 
DUAL table as an index-organized table (IOT), rather than a heap table? This 
will reduce logical I/Os significantly. If you go with an IOT and define it 
with the following DDL- 


create table mydual(dummy varchar2(1) primary key constraint one_row 
check(dummy='X')) organization index; 


-you can avoid the overhead of a trigger and still protect the table from 
having more than one row. Note that the primary key protects against more than 
one row containing 'X' and the check constraint protects against any rows 
containing something other than 'X', which results in exactly one row 
containing 'X'. 

Second, the idea to create the NEXT_PKY function utilizes EXECUTE IMMEDIATE. 
While I agree that there's no better way to do it unless you want to write an 
absurd amount of code, it should be noted that this would cause a PARSE call on 
each execution that cannot be avoided. Again, because sequence generators can 
be heavily used pieces of code, the impact of the extra parsing should be 
considered on highly concurrent systems.
Mark J. Bobak
mark@xxxxxxxxx 

Thanks for raising these very interesting and valid points about my suggestion 
for replacing the DUAL table. I hope Oracle Magazine readers will put them to 
use wherever appropriate.
Steven Feuerstein

====================================================================================================================



Dimitre Radoulov




  ----- Original Message ----- 
  From: Magnus Andersen 
  To: ineyman@xxxxxxxxxxxxxx 
  Cc: ORACLE-L 
  Sent: Tuesday, November 08, 2005 10:22 PM
  Subject: 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 onerow10  DECLARE11     PRAGMA AUTONOMOUS_TRANSACTION;12     l_count 
PLS_INTEGER;13  BEGIN14     SELECT COUNT (*)15       INTO l_count16       FROM 
onerow;17   18     IF l_count = 1
19     THEN20        raise_application_error21                        (-2000022 
                      , '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: