CODEWITHSUNDEEP
oracle-databasefunctionstored-proceduresoracle11goracle10g
user2729205
user2729205

Reputation: 27

Returning Nothing in Oracle Function

I created function in oracle and I've noticed that its returning nothing/empty string.

Oracle Function

CREATE OR REPLACE function UDF_GET_STATUS(P_ID in NUMBER)
return NVARCHAR2 is

  l_status NVARCHAR2(25);

begin

select case
             when exists(select ID, STATUS
                         from  Transaction
                         where ID = P_ID AND STATUS = 1) 
             then 'APPROVED'
             else 'FOR  APPROVAL'
             end into l_status
             from Transaction WHERE ID = P_ID;

  return l_status;

end;

Did I missed something? Please advice..

Thanks,

Upvotes: 0

Views: 472

Answers (1)

Ben
Ben

Reputation: 52853

This is most likely caused by you having no result that matches your query. Oracle will allow your function to return but will return a NULL result.

A NO_DATA_FOUND exception is raised in PL/SQL but it's not propogated through to your SQL. I would guess that the ID you're passing to your function does not exist in your table. Please see my answer here for an explanation.

You function is a little strange anyway. You're doing two scans of TRANSACTION, which isn't required. As you're not returning any data from your table I would use an aggregate query, which always returns one row and so doesn't exhibit the same behaviour:

create or replace function UDF_GET_STATUS( P_ID in number
      ) return nvarchar2 is

  l_status number;

begin

   select count(*) into l_status
     from transaction
    where id = P_ID
      and status = 1
      and rownum < 2
          ;

   if l_status = 0 then
      return 'FOR  APPROVAL';
   else return 'APPROVED';
   end if;

end;
/

As your query is an EXISTS you only need one row that fulfills the condition so you can use ROWNUM to limit the results you might get.

It's also a little curious that you're returning a NVARCHAR2 here...

Upvotes: 2

Related Questions