CREATE OR REPLACE FUNCTION get_fio2 (empno_in IN emp.empno%TYPE)
    RETURN  emp.ename%TYPE
IS
   --no_valid_fio   EXCEPTION;
   --PRAGMA EXCEPTION_INIT (no_valid_fio, - 20090 );
   fio_out   emp.ename%TYPE;
BEGIN
   SELECT NVL (ename, 'ggg')
     INTO fio_out
     FROM emp
    WHERE empno = empno_in;

    IF  fio_out = '0'
   THEN
      raise_application_error (- 20060 , 'Ф.И.О. недоступно!');
   -- RETURN   0 ;
    ELSE 
       RETURN  fio_out;
   END  IF ;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
       raise_application_error (- 20090 , 'Ф.И.О. недоступно!');
END;
/

Statement st = cn.createStatement();
String sql = "{? = call get_fio2(?)}";
        CallableStatement cst = cn.prepareCall(sql);
        cst.setInt( 2 , 778 );
        cst.registerOutParameter( 1 ,Types.VARCHAR);
        cst.execute();
        System.out.println("Результат запроса: "+cst.getString( 1 ));

CREATE OR REPLACE FUNCTION get_fio2 (empno_in IN emp.empno%TYPE)
    RETURN  emp.ename%TYPE
IS
   --no_valid_fio   EXCEPTION;
   --PRAGMA EXCEPTION_INIT (no_valid_fio, - 20090 );
   fio_out   emp.ename%TYPE;
BEGIN
   SELECT NVL (ename, '0')
     INTO fio_out
     FROM emp
    WHERE empno = empno_in;

    IF  fio_out = '0'
   THEN
      raise_application_error (- 20060 , 'Ф.И.О. недоступно!');
   -- RETURN   0 ;
    ELSE 
       RETURN  fio_out;
   END  IF ;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
       raise_application_error (- 20090 , 'Ф.И.О. недоступно!');
END;
/