Oracle function always returning null

Question

I can't get this function to behave as i desire. Can anyone point out why it always returns null instead of CURRENT_TIMESTAMP?

CREATE OR REPLACE FUNCTION nowts RETURN TIMESTAMP IS

vTimestamp TIMESTAMP;

BEGIN
  SELECT type_date
  INTO vTimestamp
  FROM param_table
  WHERE param_table = 3
  AND exists (
     SELECT *
     FROM param_table 
     WHERE param_table = 2
  );

  IF vTimestamp IS NULL THEN
    vTimestamp := CURRENT_TIMESTAMP;
  END IF;

  return vTimestamp;
END nowts;

Right now there is nothing in the table named param_table.

Answer 1

If you call a function from SQL and the function raises a NO_DATA_FOUND, you get a NULL.

The SQLCODE from a NO_DATA_FOUND is +100, whereas the PL/SQL Code is -1403. Ref

A positive number isn't an error, and SQL doesn't treat the concept of a NO_DATA_FOUND as an exception. SQL sees it as "No value" which is null.

create or replace function ret_dt return date is
begin
  raise no_data_found;
end;
/
Elapsed: 00:00:00.26
> select rownum ,ret_dt from user_tables where rownum < 5;
         ROWNUM RET_DT
--------------- -----------------
           1.00
           2.00
           3.00
           4.00

You probably want to catch it and return a specific value, or catch it and raise a user-defined exception (depending on what you want to happen).

Answer 2

If there are no rows, the select into will raise a NO_DATA_FOUND exception.

Add before the END nowts

exception
  when no_data_found then
    return CURRENT_TIMESTAMP;