Home » SQL & PL/SQL » SQL & PL/SQL » select sysdate from dual
select sysdate from dual [message #125114] Thu, 23 June 2005 06:37 Go to next message
sneha_84
Messages: 21
Registered: May 2005
Junior Member
Friends,


I gave a query

select sysdate from dual;

It returns two dates into screen.

My problem is

In one PLSQL code

when I use

select last_day(x_date) into dVar from dual;

is telling.

Oracle error selected rows are more than requested rows.

Can you friends suggest the reason is.

Report message to a moderator

Re: select sysdate from dual [message #125128 is a reply to message #125114] Thu, 23 June 2005 07:19 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Well, it sounds to me like someone has inserted an extra row into DUAL. Don't do it! The very existence of the SYS.DUAL table is the assumption that it contain but one row and one column. Don't be messing around in Oracle's data dictionary!

Report message to a moderator

Re: select sysdate from dual [message #125132 is a reply to message #125128] Thu, 23 June 2005 07:30 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
HI Art

I too was thinking on same line that there someone has played with dual table...

& then for testing I started playing with my sys.dual table.

then before trying to rebuild dual table, i dropped it in my sys schema. Now I am struck in recreating it...

I recreated using my "create table statement..." but then all statements like "select sysdate from dual" etc are not working. I am getting "no rows selected" there.

I again dropped the table dual and the ran catalog.sql.

After this when i am trying to "desc dual" i am getting the error
"SP2-0749: Cannot resolve circular path of synonym "dual"".

Now can u advise how to resolve this issue.

Regds
Girish

Report message to a moderator

Re: select sysdate from dual [message #125134 is a reply to message #125132] Thu, 23 June 2005 07:53 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
Got the table back.

[EDIT]: I dropped the public synonym dual.

I had to re-run sql.bsq script. from location @oracle_home/rdbms/admin.

Anyone... can pls suggest what can be the after-effects of running sql.bsq script again.

All suggesstions will be highly appreciated.

Regds
Girish

[Updated on: Thu, 23 June 2005 08:05]

Report message to a moderator

Re: select sysdate from dual [message #125162 is a reply to message #125132] Thu, 23 June 2005 10:59 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
girish.rohini wrote on Thu, 23 June 2005 11:30

then for testing I started playing with my sys.dual table.


girish.rohini wrote on Thu, 23 June 2005 11:30

Now can u advise how to resolve this issue.


http://www.vtm.be/auto/forum/images/smilies/old/crepapelle.gif

Way to go! Very Happy

MHE

Report message to a moderator

Re: select sysdate from dual [message #125188 is a reply to message #125114] Thu, 23 June 2005 13:50 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Do not EVER play with the dual table again. If you do, you can and will cause your database and applications to blow up!!!!! . To fix it (if it is dropped),

create table sys.dual ( DUMMY VARCHAR2(1));
insert into sys.dual values('X');
commit;
SQL> select count(*) from sys.dual;

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

SQL> select * from sys.dual;

D
-
X

SQL>

If you get ANYTHING else, then your DUAL table is corrupted.

Report message to a moderator

Re: select sysdate from dual [message #125250 is a reply to message #125188] Fri, 24 June 2005 01:02 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
Thnx Bill/Maaher for ur replies & providing me a way to resolve this issue.

Now, as i mentioned earlier, that I did got the table back by running sql.bsq script, my query regarding re-running this script on a already created live data base (for testing purpose)still persists. Of course... I wont be playing around with sys tables in my production database.

Whats the effect of running this script again on a database. Is it going to hamper / change something in my database.


Regds
Girish

[Updated on: Fri, 24 June 2005 01:02]

Report message to a moderator

Re: select sysdate from dual [message #125260 is a reply to message #125188] Fri, 24 June 2005 02:08 Go to previous messageGo to next message
sneha_84
Messages: 21
Registered: May 2005
Junior Member
One friend has said

SQL> select count(*) from sys.dual; must return 1. But I am getting 2. but select * from sys.dual is correct as he specified I am getting.

Is my sys.dual is correpted.

Expecting comments from group.




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

SQL> select * from sys.dual;

D
-
X

SQL>

Report message to a moderator

Re: select sysdate from dual [message #125264 is a reply to message #125260] Fri, 24 June 2005 02:26 Go to previous messageGo to next message
sneha_84
Messages: 21
Registered: May 2005
Junior Member
Yes what our friend has said is correct.

My sy.dual was corrupted.

I truncate sys.dual

Then insert the data 'X'

Then when I test it is working OK.


Thanks for the Great Comment.


With regards

Sneha


/*
One friend has said

SQL> select count(*) from sys.dual; must return 1. But I am getting 2. but select * from sys.dual is correct as he specified I am getting.

Is my sys.dual is correpted.

Expecting comments from group.




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

SQL> select * from sys.dual;

D
-
X

SQL>
*/

Report message to a moderator

Re: select sysdate from dual [message #125265 is a reply to message #125260] Fri, 24 June 2005 02:28 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
Bill said:
Quote:

Do not EVER play with the dual table again. If you do, you can and will cause your database and applications to blow up!!!!! . To fix it (if it is dropped),

create table sys.dual ( DUMMY VARCHAR2(1));
insert into sys.dual values('X');
commit;
SQL> select count(*) from sys.dual;

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

SQL> select * from sys.dual;

D
-
X

SQL>

If you get ANYTHING else, then your DUAL table is corrupted.


Now comparing ur results with this shows that ur dual table is definitely corrupted.
Now one way to resolve this is:
truncate the table dual.
then insert value 'X' in sys.dual.
Then check for the result of both queries:
select * from dual;
&
select count(*) from dual;

Regds
Girish

Report message to a moderator

Re: select sysdate from dual [message #125377 is a reply to message #125114] Fri, 24 June 2005 15:26 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
@sneha_84

Since there should be only 1 row in dual, sql*plus is optimized for it. It just knows there is no need to fetch more than one row in case you issue a 'select * from dual'.
This is the reason a 'select * from dual' seems to return only one row, whereas a 'select count(*) from dual' returns the actual number of rows contained in dual. This is why Bill suggested to use both methods to test for the validity of your dual table.

hth

Report message to a moderator

Re: select sysdate from dual [message #125388 is a reply to message #125377] Fri, 24 June 2005 16:22 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
@Frank: Actually, it's the optimizer that 'knows' there should only be one row in DUAL. Therefore, if you do a SELECT from dual, you get one row back, even if dual contains more rows. By doing a SELECT COUNT(*) you force the optimizer to pass the entire table.

SQL*Plus is just another command line interpreter.

@girish.rohini: Art Metzer just told not to play with the dual table. In fact, you're not supposed to play with the dictionary at all. Not with any part of it, not even a table like dual is to be messed with. That's why I found it hilarious: instead of helping the original poster, you joined him in his troubles...

You're not supposed to perform ANY direct DML nor DDL with ANY part of the dictionary unless Oracle support advised you to. SYS is the maintenance account. No regular user should mess with it, as you have showed us.

MHE

Report message to a moderator

Re: select sysdate from dual [message #125429 is a reply to message #125114] Sat, 25 June 2005 02:14 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
If I would insert another row in dual (not that I ever would try this Wink) then sqlplus returns 1 row on a select * from dual, whereas plsql-developer returns 2 rows.
I remember a thread not too long ago about this topic, and I thought it said that it was sqlplus.
Of course, sqlplus is a bit more than just an interpreter, for example it fires extra queries to handle dbms_output.

I have to go now, gotta remove that extra row in dual...

Report message to a moderator

Re: select sysdate from dual [message #125447 is a reply to message #125429] Sat, 25 June 2005 12:37 Go to previous message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
Hi Maaher

Yes, I do agree & will never-ever play around with dictionary tables (unless otherwise advised by Support to do so).

Yes, it was really funny (actually it made me bang my head...) The only solace was I learnt it the easy way. The DB I played around with is the one installed on my desktop, which I can always reinstall.... But learnt something really important...

Thnx once again.

Regds
Girish

Report message to a moderator

Previous Topic: Analytic Functions - Need resultset only in one select
Next Topic: Search CLOB field
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 03 01:23:11 CDT 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.