Re: pl/sql: test if record is empty

On May 27, 8:06 am, 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,- Hide quoted text -
>
> - Show quoted text -

Steph,
Just set the ROWTYPE variable to null:
o_rec := NULL;

Regards,
Vince Received on Wed May 28 2008 - 11:43:50 CDT