UREGENT!!! SYSDATE IS RETURNING MULTIPLE RECORDS

[balajiyes]

HI,


WHEN WE ARE SELECTING SYSDATE INSIDE A PL/SQL BLOCK, WE GET THE ERROR EXACT FETCH RETURNS MULTIPLE ROWS...IF WE ARE SELECTING IN THE SQL PROMPT , IT IS RETURNING THE CORRECT DATE..

[akkerend]

Check your thread in DEVELOPMENT forum.
Probably DUAL contains more than one row.

[balajiyes]

NO..THERE IS ONLY ONE ROW IN DUAL TABLE..WE ARE FACING THIS SAME PROBLEM..EVEN WHEN WE ARE SELECTING SEQID.NEXTVAL FROM DUAL INSIDE A PLSQL BLOCK

REGARDS

[mvander2]

You can try ...
select owner,table_name from dba_tables where table_name = 'DUAL';

maybe someone created an extra dual table ?

sorry to keep insisting on the dual table

[akkerend]

How did you check the number of rows in dual?
In 8i in SQL*Plus only a
SELECT COUNT(1) FROM dual;
gives the correct answer. Oracle returns always one row for statements like
SELECT * FROM dual;
In PL/SQL all rows are returned, so you get the ORA-01422: exact fetch returns more than requested number of rows.

[balajiyes]

THIS IS WHAT I GET..

SQL> SELECT COUNT(1) FROM dual;

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

Elapsed: 00:00:01.82
SQL>
SQL> select owner,table_name from dba_tables where table_name = 'DUAL';

OWNER TABLE_NAME
------------------------------ ------------------------------
SYS DUAL

Elapsed: 00:00:00.20
SQL> select * from dual;

D
-
X

DID U FIND ANY PROBS....

REGARDS..

[balajiyes]

HI,


SORRY..FOR THE PREVIOUS ERROR..

I GOT THIS...

SQL> SELECT COUNT(1) FROM DUAL;

COUNT(1)
----------
3


WHAT IS THE SOLUTION


REGARDS

[balajiyes]

HI

THANX GUYS..THERE WERE 3 RECORDS IN THE DUAL TABLE..

REGARDS