Select And Allow Ref(null) Result From Object Table

Question

I'd like to run a function that will select a ref into an attribute from an object table, and if the table returns null then the attribute is set to null. To achieve this I tried the code.

select ref(t) into self_ref from my_table where objectID = self.objectID;

But this fails anytime that the where fails to find a match. In place I have the following

select count(*)
    into l_result
    from my_table
 where companyID = self.companyID;
if l_result > 0 then
    select ref(t)
        into self_ref
        from my_table t
     where objectID = self.objectID;
else
    self_ref := null;
end if;

But I'd like if there was a better solution that didn't involve so much redundancy.

Any suggestions?

score 1 · Accepted Answer · answered Oct 01 '15 at 00:41

The conventional approach (whether you're dealing with object references or any other local variable) would be

begin
  select ref(t)
    into self_ref
    from my_table t
   where objectID = self.objectID;
exception
  when no_data_found
  then
    self_ref := null;
end;

This avoids the potential race condition where the count(*) returns a different result than the subsequent select. Technically, assuming you've never assigned a value to self_ref previously, it would already be null so your exception handler could simply ignore the no_data_found exception. I prefer to do the explicit assignment of the variable to null even though it's technically redundant, however, because it tends to make the code clearer. Subsequent maintainers don't have to question whether you intended to set (or leave) the local variable null if you do it explicitly.

You could construct this various other ways as well, for example

self_ref := null;
for r in (select ref(t) my_ref
            from my_table t
           where objectID = self.objectID)
loop
  self_ref := r.my_ref;
end loop;

but the select into with an exception handler would be the most common and would most accurately express your intention.

Select And Allow Ref(null) Result From Object Table

1 Answers1