TIPS(62) : ORA-1422, ORA-6512 WHEN RETURN SYSDATE THROUGH A FUNCTION
====================================================================

PURPOSE
-------

PL/SQL에서 ORA-1422 에러는 SELECT 문에서 조건에 해당하는 row가 2건 이상
return되었을 때 발생하는 TOO_MANY_ROWS 에러와 동일한 에러이다.

ORA-1422 : exact fetch returns more than requested number of rows.

이 자료는 SYSDATE를 조회하는 DUAL table에는 default로 X라는 dummy
column 하나만 존재하는 데도 불구하고,

SELECT COUNT(*) FROM DUAL;
해보면 2개의 row가 존재한다고 나오는 경우의 해결 방법을 제시하기로 한다.


Problem Description
-------------------

TO_CHAR(SYSDATE, 'YYYYMMDD') function 또는 SYSDATE를 return하는 간단한
function을 수행 시 ORA-1422 에러가 발생한다.

create or replace function beth return date is
now date;
begin
now := SYSDATE;
RETURN(now);
end;
/

SQL> @beth

Function created.

SQL> select beth from dual;
select beth from dual
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.STANDARD", line 648
ORA-06512: at "BETH.BETH", line 6
ORA-06512: at line 1


DUAL table에 몇 개의 row가 있는지 ROWID로 확인해 보면 1개의 row 밖에 없다.

SQL> select rowid, DUMMY from dual;

ROWID D
------------------ -
AAAACsAABAAAAS0AAA X


Problem Explanation
-------------------

그러나, DUAL table에는 실제 2개의 row가 존재한다.

SQL> select count(*) from dual;

COUNT(*)
----------
2


Workaround
----------
none


Solution Description
--------------------

확인한 결과 DUAL table에서는 비록 2개의 ROWID를 볼 수는 없지만,
실제 2개의 row가 DUAL table에 존재하는 상황이다.
따라서, 다음 명령을 이용하여 여분의 필요없는 row를 delete해야 한다.

SQL>delete from dual where rowid != 'ROWID LISTED IN SELECT FROM DUAL';

ie:

SQL> select rowid, DUMMY from dual;

ROWID D
------------------ -
AAAACsAABAAAAS0AAA X

SQL>delete from dual where rowid != 'AAAACsAABAAAAS0AAA';


SQL> select count(*) from dual;

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

Function을 다시 실행해보면 원하는 대로 SYSDATE를 return받을 수 있다.

SQL> select beth from dual;

BETH
---------
29-MAR-99


Note
----

" There seems to be a ghost line in DUAL. "
그러나, 필요없는 line을 delete해주면 원하는 대로 해결되므로,
function은 1개의 row만 return할 수 있다.
만약, 2개보다 더 많은 수의 row가 존재할 경우, 각각 하나씩 위와 같은
DELETE 명령을 수행해 주어야 DUAL table의 필요없는 row들을 모두 제거
할 수 있다.


Reference Documents
-------------------