Select from dual return 3 rows ! 2005-11-08 - By Magnus Andersen
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.
In the may issue of Oracle Magazine, Steven <span class="italicbodycopy"
>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:<br>
<br>
<a href="http://www.oracle.com/technology/oramag/oracle/04-may/o34tech_plsql
.html">http://www.oracle.com/technology/oramag/oracle/04-may/o34tech_plsql.html<
/a><br>
<br>
and here is how he suggests that you can do it.<br>
<br>
</span><span class="bodycopy"> Code Listing 5: Creating a DUAL-like table</span>
<p>
</p>
<pre> 1 CREATE TABLE onerow (dummy VARCHAR2(1))<br> 2 /<br> 3 GRANT SELECT
ON onerow TO PUBLIC<br> 4 /<br> 5 CREATE PUBLIC SYNONYM onerow FOR onerow<br
> 6 /<br> 7 CREATE OR REPLACE TRIGGER enforce_onerow<br> 8 BEFORE INSERT
<br> 9 ON onerow<br>10 DECLARE<br>11 PRAGMA AUTONOMOUS_TRANSACTION;
<br>12 l_count PLS_INTEGER;<br>13 BEGIN<br>14 SELECT COUNT (*)<br>15
INTO l_count<br>16 FROM onerow;<br>17 <br>18 IF l_count = 1
<br>19 THEN<br>20 raise_application_error<br>21
(-20000<br>22 , 'The onerow table can have only one row
.'<br>23 );<br>24 END IF;<br>25 END;
<br>26 /<br></pre>
<br>
HTH,<br>-- <br>Magnus Andersen<br>Systems Administrator / Oracle DBA<br>Walker
& Associates, Inc.
|
|
