select into

How come lines 9 and 10 do not execute in the following is_active function? Documentation says that use into_clause only when there is one row in the result. Should the remaining code be ignored when there are no rows. Be sure to have exactly that one row available when using select into structure. Count(*) is a way here to be sure there is the one row.

SQL> CREATE OR REPLACE function is_active(i_is_active number)
 2      return number as
 3      ret number := 0;
 4  begin
 5      dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
 6      select 1 into ret
 7        from dual
 8       where i_is_active = 1;
 9      dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
10      if ret is null then ret := 0;
11      end if;
12      return ret;
13  end;
14  /

Function created.

SQL>
SQL> select is_active(1),is_active(0) from dual
 2  ;

IS_ACTIVE(1) IS_ACTIVE(0)
------------ ------------
          1

-0-before-1-
-1-after -1-
-0-before-0-
SQL>
SQL>
SQL> CREATE OR REPLACE function is_active_usingcount(i_is_active number)
 2      return number as
 3      ret number := 0;
 4  begin
 5      dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
 6      select count(*) into ret
 7        from dual
 8       where i_is_active = 1;
 9      dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
10      return ret;
11  end;
12  /

Function created.

SQL>
SQL> select is_active_usingcount(1),is_active_usingcount(0) from dual
 2  ;

IS_ACTIVE_USINGCOUNT(1) IS_ACTIVE_USINGCOUNT(0)
----------------------- -----------------------
                     1                       0

-0-before-1-
-1-after -1-
-0-before-0-
-0-after -0-

You get "ORA-01422: exact fetch returns more than requested number of rows" if there are more than one row. Should there be an exception also to the case when there are no rows?

Update 4.11.2010:
Well there is a exception thrown. Just not catching it anywhere.

CREATE OR REPLACE function is_active(i_is_active number)
       return number as
       ret number := 0;
  begin
       dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
       select 1 into ret
       from dual
      where i_is_active = 1;
     dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
     if ret is null then ret := 0;
     end if;
     return ret;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN return -1;
    end;
/

select is_active(1),is_active(0) from dual;

IS_ACTIVE(1) IS_ACTIVE(0)
------------ ------------
1            -1