Oracle function always returns null

Question

I cannot make this function behave as I want. Can someone please indicate 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 calls NO_DATA_FOUND, you get NULL.

The SQLCODE of NO_DATA_FOUND is +100, while the PL / SQL code is -1403. Link

A positive number is not an error, and SQL does not consider the concept of NO_DATA_FOUND as an exception. SQL considers this "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 throw a custom exception (depending on what you want).

Answer 2

If there are no rows, selecting in raises an NO_DATA_FOUND exception.

Add before END comes out

 exception when no_data_found then return CURRENT_TIMESTAMP;