Sequence returns multiple numbers 
Author Message
 Sequence returns multiple numbers



Quote:
> O dear !

> Try "select * from dual" .. a bit paranoid maybe, but somebody might
have
> fouled up the system.dual table (if such a thing is possible)

> cheers

> Glenn Baron

Thanks, guys, you were right on the money! There were two rows in DUAL.
Further consulation with Oracle Support in Sweden (they were excellent,
BTW) shed some more light on the problem. DUAL works almost like any
other table, in that you can INSERT into and SELECT from it. You can't
however, do a DELETE FROM DUAL WHERE.... Instead, you have to DELETE
FROM DUAL, and then do a simple INSERT od the single row. Apparently,
the value which you put in DUAL doesn't matter too much, but the
default is 'X'.

I had no idea that it was *possible* to INSERT INTO DUAL. It is, after
all, a pretty scary proposition. I haven't yet determined if you need
any special privileges to do this kind of thing. If not, I feel that
this is a serious security issue.

Anyway, thanks again!

--
Claes Engelin
Prevas AB
Gothenburg, Seden
claes(dot)engelin(at)prevas(dot)se

--== Sent via Deja.com http://www.***.com/
---Share what you know. Learn what you don't.---



Fri, 26 Oct 2001 03:00:00 GMT
 Sequence returns multiple numbers

Quote:

>Thanks, guys, you were right on the money! There were two rows in DUAL.
>Further consulation with Oracle Support in Sweden (they were excellent,
>BTW) shed some more light on the problem. DUAL works almost like any
>other table, in that you can INSERT into and SELECT from it. You can't
>however, do a DELETE FROM DUAL WHERE.... Instead, you have to DELETE
>FROM DUAL, and then do a simple INSERT od the single row. Apparently,
>the value which you put in DUAL doesn't matter too much, but the
>default is 'X'.

DUAL doesn't "works almost like any other table" - it *is* just
another normal table, and as such it behaves *exactly* as any other
table. So you can do anything with it, you can insert, update, delete
(with or without the where clause, so your assumption about DELETE
FROM DUAL WHERE .... is not correct!), you could even drop it! But you
need appropritae privileges for all that.

Examples:


D
-
X


1 row created.


D
-
X
Y


1 row deleted.


D
-
X

Quote:
>I had no idea that it was *possible* to INSERT INTO DUAL. It is, after
>all, a pretty scary proposition. I haven't yet determined if you need
>any special privileges to do this kind of thing. If not, I feel that
>this is a serious security issue.

No, no Oracle's security issue. It all depends on how your DBA treets
the database security, especially the security of system objects. DUAL
is owned by user SYS (super-superuser) and by default (at database
creation time) the PUBLIC is granted a SELECT privilege on it. And
that is all. But if your DBA has granted something elese on this table
to anyone, or if he made SYS acount available to any unauthorised
user, or if he/she likes to play with the database connected as SYS -
then this is only your internal security issue that can leed to such
unexpected results as you were experiencing.

Quote:
>Anyway, thanks again!

>--
>Claes Engelin
>Prevas AB
>Gothenburg, Seden
>claes(dot)engelin(at)prevas(dot)se

HTH,

Certified Oracle7 DBA (OCP)
================================================
The above opinions are mine and do not represent
any official standpoints of my employer


Fri, 26 Oct 2001 03:00:00 GMT
 Sequence returns multiple numbers

Quote:
> DUAL doesn't "works almost like any other table" - it *is* just
> another normal table, and as such it behaves *exactly* as any other
> table. So you can do anything with it, you can insert, update, delete
> (with or without the where clause, so your assumption about DELETE
> FROM DUAL WHERE .... is not correct!), you could even drop it! But you
> need appropritae privileges for all that.

That's true, except for one tiny little difference:
The query-optimizer always knows that DUAL is a small table without checking
the table statistics.
--
Erwin Dondorp
<http://www.wxs.nl/~erwindon>


Thu, 01 Nov 2001 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Returning records from select with a sequence number attached

2. Returning Sequence Numbers In VB

3. Returning Sequence Numbers

4. Multiple sequence numbers in a single query

5. Returning Sequence Numbers In VB

6. Unable to return an Oracle sequence number

7. Question regarding single sequence vs multiple sequences for surrogate key population

8. Question regarding single sequence vs multiple sequences for surrogate key population

9. record number/sequence number in MSAccess table

10. add data to sequence number

11. a database generated sequence number

12. How create a table with a numbered sequence?


 
Powered by phpBB® Forum Software