Re: pl/sql: test if record is empty

On May 27, 5:06 pm, steph <stepha..._at_yahoo.de> wrote:
> On 27 Mai, 16:03, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>
>
>
> > 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:
> > PL/SQL: Statement ignored
>
> > 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
>
> Thanks, but this is my workaround already - thought there must be
> something more elegant out there.
>
> Similarily, how can I empty a record? Do I need to empy all of it's
> components like:
>
> declare
> o_rec mytab%ROWTYPE;
> begin
> o_rec.empno:=null;
> o_rec.empname:=null;
> ...
> end;
>
> cheers,

I do not know any alternative approach. If your definition of "empty" means "all fields are NULL" then you have to code it that way. If you need this more often you can easily make a stored procedure from this (same holds true for the emptiness check).

Btw, what do you need that for? The requirement does not seem to come up very often so this makes me curious why you need this.

Kind regards

robert Received on Wed May 28 2008 - 03:46:15 CDT