Re: pl/sql: test if record is empty

On May 27, 5:45 am, steph <stepha..._at_yahoo.de> wrote:
> oracle 10G
>
> I apparently overlooked it in the manuals - so please be patient ...
>
> How can I test if a record is empty?
>
> I expected it to work like this:
>
> declare
>   o_rec mytab%ROWTYPE;
> begin
>   if o_rec.empty then
>     dbms_output.put_line('empty');
>   end if;
> end;
>
> ... but it does not.
>
> Thanks,
> stephan

No, it does not:

ERROR at line 4:

ORA-06550: line 4, column 12:
PLS-00302: component 'EMPTY' must be declared
ORA-06550: line 4, column 3:

You should be checking a known NOT NULL value for NULL instead:

SQL> declare
  2 o_rec emp%ROWTYPE;
  3 begin
  4 if o_rec.empno is null then
  5 dbms_output.put_line('empty');
  6 end if;
  7 end;
  8 /
empty

PL/SQL procedure successfully completed.

SQL> David Fitzjarrell Received on Tue May 27 2008 - 09:03:22 CDT