Check a record IS NOT NULL in plsql

Question

I have a function which would return a record with type my_table%ROWTYPE, and in the caller, I could check if the returned record is null, but PL/SQL complains the if-statement that

PLS-00306: wrong number or types of arguments in call to 'IS NOT NULL'

Here is my code:

v_record my_table%ROWTYPE;
v_row_id my_table.row_id%TYPE := 123456;
begin
    v_record := myfunction(v_row_id)
    if (v_record is not null) then
        -- do something
    end if;
end;

function myfunction(p_row_id in my_table.row_id%TYPE) return my_table%ROWTYPE is
    v_record_out my_table%ROWTYPE := null;
begin
    select * into v_record_out from my_table
    where row_id = p_row_id;
    return v_record_out;
end myfunction;

Thanks.

Peter Lang · Accepted Answer · 2011-08-26T17:16:40.357

36

As far as I know, it's not possible. Checking the PRIMARY KEY or a NOT NULL column should be sufficient though.

You can check for v_record.row_id IS NULL.

Your function would throw a NO_DATA_FOUND exception though, when no record is found.

edited Aug 26 '11 at 17:16

answered Aug 26 '11 at 16:42

Peter Lang

54,264
27
148
161

1

Thanks for your answer. Is that the only way to check a if a record is null? It's weird to me that we can assign null to a record, but can't check if a record is null. – Sapience Aug 26 '11 at 16:48
3

As far as I know, it's not possible. Checking the `PRIMARY KEY` or a `NOT NULL` column should be sufficient though. – Peter Lang Aug 26 '11 at 16:58
1

Good workaround! I use assignment of `NULL` to record and this is the way to make check! +1 – gavenkoa Jun 25 '13 at 11:24
A decent workaround in a lot of cases, but for what it's worth, doing it this way gets rid of some of the convenience and safety of using the `%ROWTYPE` attribute. If you do this, you might as well define the `RECORD` the long way and `SELECT INTO` each by hand. – Alan Hensley Feb 20 '15 at 21:40

score 4 · Answer 2 · answered Aug 27 '11 at 08:06

You can't test for the non-existence of this variable so there are two ways to go about it. Check for the existence of a single element. I don't like this as it means if anything changes your code no longer works. Instead why not just raise an exception when there's no data there:

I realise that the others in the exception is highly naughty but it'll only really catch my table disappearing when it shouldn't and nothing else.

v_record my_table%ROWTYPE;
v_row_id my_table.row_id%TYPE := 123456;

begin
    v_record := myfunction(v_row_id)
exception when others then
        -- do something
end;

function myfunction(p_row_id in my_table.row_id%TYPE) return my_table%ROWTYPE is
    v_record_out my_table%ROWTYPE := null;

cursor c_record_out(c_row_id char) is
 select * 
   from my_table
  where row_id = p_row_id;

begin
   open c_record_out(p_row_id);
   fetch c_record_out into v_record_out;

   if c_record_out%NOTFOUND then
      raise_application_error(-20001,'no data);
   end if;
   close c_record_out;
return v_record_out;
end myfunction;

Check a record IS NOT NULL in plsql

2 Answers2

Linked