Re: pl/sql: test if record is empty

On 28 Mai, 10:46, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> 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

some pseudocode:

CREATE OR REPLACE package body XX as
  function bc_vgr_2nd(p_invals in sys_refcursor) return lwps_type_set pipelined
  is
    out_rec LU_WAPS_INSTRUMENTS%ROWTYPE;     in_rec v_bc_vgr_stage%ROWTYPE;
  begin
    loop

      fetch p_invals into in_rec;
      exit when p_invals%notfound;
      out_rec.LWPS_ID:=null;
      ...
      pipe row(out_rec);

as you see, i make use of records here. here it's not necessary, but I have more complicated cases, where it might be useful to easily test a record for emptyness or to empty it easily. I thougt this might be supported by pl/sql in the meantime ... Received on Wed May 28 2008 - 06:34:20 CDT