When using SELECT INTO... and have multiple rows returning by query,
if the target variable declared strongly (e.g....%TYPE), it keeps the result of a fetch,
if declared weakly (e.g....NUMBER), it keeps nothing for the same case.

What is going?

Thanks in advance.

Here is the case:

create table emp1 as select * from emp where ename = 'ALLEN'
/

insert into emp1 select * from emp1
/
commit
/

orclbal803/DTOTT/ULOG>DECLARE
2 v_empno emp1.empno%TYPE;
3 --v_empno NUMBER;
4 v_multirow_flag CHAR(1);
5 BEGIN
6 v_multirow_flag := NULL;
7 BEGIN
8 SELECT empno
9 INTO v_empno
10 FROM emp1
11 WHERE ename = 'ALLEN';
12
13 EXCEPTION
14 WHEN TOO_MANY_ROWS THEN
15 v_multirow_flag := 'Y';
16 END;
17 dbms_output.put_line('empno : '||v_empno);
18 dbms_output.put_line('multirow_flag : '||v_multirow_flag);
19
20 END;
21 /
empno : 7499
multirow_flag : Y


orclbal803/DTOTT/ULOG>DECLARE
2 --v_empno emp1.empno%TYPE;
3 v_empno NUMBER;
4 v_multirow_flag CHAR(1);
5 BEGIN
6 v_multirow_flag := NULL;
7 BEGIN
8 SELECT empno
9 INTO v_empno
10 FROM emp1
11 WHERE ename = 'ALLEN';
12
13 EXCEPTION
14 WHEN TOO_MANY_ROWS THEN
15 v_multirow_flag := 'Y';
16 END;
17 dbms_output.put_line('empno : '||v_empno);
18 dbms_output.put_line('multirow_flag : '||v_multirow_flag);
19
20 END;
21 /
empno :
multirow_flag : Y