Oracle function always returning null
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 n开发者_StackOverflow中文版owts;
Right now there is nothing in the table named param_table.
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).
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;
精彩评论