Can we insert value into DUAL table?

[karthi keyan]

Can we insert value into DUAL table?

 

Regards,

Karthi

[Darlei Soares]

No

2008/9/19 karthi keyan <kart...@gmail.com>:

[Thomas Olszewicki]

Karthi,
If you connect as the user with SYSDBA privs, then you can,
but... just don't do it !
If you have any DML in your code, Pl/sql or any other application
using
DUAL table, you may have created whole bunch of new exceptions.
In many applications, there is an assumption that DUAL table will
always have only one row.
A code like SELECT ...INTO...FROM DUAL may start raising exception
TOO_MANY_ROWS
after your "experiment".
HTH
Thomas

[deepak sethi]

Hi Karthi,

yes we can insert value into dual table

There is only one column dummy with varchar2 data type and length only one

But we have to log in as sys (as SYSDBA)

 

Regards

Deepak

[Rob Wolfe]

You CAN, but what possible reason could you have that would possess you to
want to do such a bizarre thing?

I am serious. This is just a VERY BAD IDEA and if anyone that worked for
me was suggesting to me that they wanted to do it I would have to have a
long think about their judgement.

Rob

> --
> Deepak Sethi
> India
>
> >
>

[Darlei Soares]

Rob Wolf is right, is so bad insert values in dual, i use the dual
table for calcs, exemple

select (85/753)*5 from dual

of for me to found the sysdate, is so bizarre insert values in dual

2008/9/19 Rob Wolfe <rob....@oraclegeeks.com>:

[Michael Moore]

I think that even if you do insert additional rows, you will only get one row when you select from it. Oracle does some special things with DUAL. It's not like a normal table.

Mike

[ora...@msn.com]

On Sep 19, 12:30 pm, "Michael Moore" <michaeljmo...@gmail.com> wrote:
> I think that even if you do insert additional rows, you will only get one
> row when you select from it. Oracle does some special things with DUAL. It's
> not like a normal table.Mike
>
> On Fri, Sep 19, 2008 at 7:46 AM, Darlei Soares

> <darlei.manches...@gmail.com>wrote:

>
>
>
>
>
> > Rob Wolf is right, is so bad insert values in dual, i use the dual
> > table for calcs, exemple
>
> > select (85/753)*5 from dual
>
> > of for me to found the sysdate, is so bizarre insert values in dual
>

> > 2008/9/19 Rob Wolfe <rob.wo...@oraclegeeks.com>:

>
> > > You CAN, but what possible reason could you have that would possess you
> > to
> > > want to do such a bizarre thing?
>
> > > I am serious. This is just a VERY BAD IDEA and if anyone that worked for
> > > me was suggesting to me that they wanted to do it I would have to have a
> > > long think about their judgement.
>
> > > Rob
>
> > > On Fri, September 19, 2008 09:58, deepak sethi wrote:
> > >> Hi Karthi,
> > >> yes we can insert value into dual table
> > >> There is only one column dummy with varchar2 data type and length only
> > one
> > >> But we have to log in as sys (as SYSDBA)
>
> > >> Regards
> > >> Deepak
>

> > >> On Fri, Sep 19, 2008 at 6:28 AM, karthi keyan <karthi...@gmail.com>

> > wrote:
>
> > >>>  Can we insert value into DUAL table?
>
> > >>> Regards,
> > >>> Karthi
>
> > >> --
> > >> Deepak Sethi

> > >> India- Hide quoted text -
>
> - Show quoted text -

In 10g and later releases this is true, although this doesn't make
doing so a good idea. In 9i and earlier releases inserts into DUAL
are visible and can seriously affect database functionality.

********** DO NOT TRY THIS -- FOR ILLUSTRATIVE PURPOSES ONLY
**********

Database version 10.2.0.3.0

SQL> insert into dual values ('Z');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) -- huh?!? what gives?!?
2 from dual;

COUNT(*)
----------
1

SQL> select * from dual; -- now, that's weird...

D
-
X

SQL> delete from dual -- proves record exists, it just can't be seen
2 where dummy = 'Z'
3 /

1 row deleted.

SQL> commit;

Commit complete.

SQL> delete from dual -- record no longer exists, try to delete it
anyway
2 where dummy = 'Z'
3 /

0 rows deleted.

SQL>

Oracle has, indeed, performed magic with DUAL in 10g and later
releases to keep fat fingers from ruining its functionality.
Unfortunately in 9iR2 and earlier versions such actions can seriously
affect applications and Oracle base functionality.



David Fitzjarrell

[Thomas Olszewicki]

> David Fitzjarrell- Hide quoted text -

>
> - Show quoted text -

Now this is even more bizarre:
This is run from pl/sql Developer (Allround Automation)

Connected to Oracle Database 11g Enterprise Edition Release
11.1.0.6.0
Connected as SYS

SQL> select * from dual;

DUMMY
-----
X

SQL> insert into dual values ('Y');

1 row inserted

SQL> commit;

Commit complete

SQL> select count(*) from dual;

COUNT(*)
----------
1

SQL> select * from dual; -- NOTE 2 values.

DUMMY
-----
X
Y

SQL> select sysdate from dual; -- NOTE 1 value.

SYSDATE
-----------
9/19/2008 5

SQL> delete from dual where dummy='Y';

1 row deleted

SQL> commit;

Commit complete

SQL>

Magic....
Thomas

[Michael Moore]

On this part ...

SQL> select sysdate from dual; -- NOTE 1 value.

SYSDATE
-----------
9/19/2008 5

what is the "5" ?