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.

@OMG Ponies, how do i check if anything is set?

JavaRocky
– JavaRocky

2010-05-23 23:38:43 +00:00
Commented May 23, 2010 at 23:38 — JavaRocky
– JavaRocky, Commented May 23, 2010 at 23:38

Gary Myers · Accepted Answer · 2010-05-24 00:37:19Z

5

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).

answered May 24, 2010 at 0:37

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alohci · Accepted Answer · 2010-05-24 00:02:37Z

5

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;

answered May 24, 2010 at 0:02

Alohci

84.2k16 gold badges120 silver badges164 bronze badges

1 Comment

Alberto Martinez Over a year ago

Thanks for the tip. I also found that if you need to catch the exception before the end of the function you can put the select into a BEGIN..END subblock and put the exception handler inside that subblock (simulating the TRY..CATCH block that you would use in other languages).

Collectives™ on Stack Overflow

Oracle function always returning null

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related