Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Newbie question regarding PL/SQL

Re: Newbie question regarding PL/SQL

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/08/10
Message-ID: <965936661.8957.0.pluto.d4ee154e@news.demon.nl>#1/1

"Lisa Adcock" <ladcock_at_ford.com> wrote in message news:8musan$fqq3_at_eccws12.dearborn.ford.com...
> I have a cursor and I would like to do some data validation on each row in
> the cursor before the "real" processing begins. I would like to organize
 my
> as follows but am having problems finding the right language
> construct/reserved word.
>
> for emp_record in cur_employees loop
>
> if this row has a data problem then
> continue onto next row in cursor
> end if
>
> -- start real processing here
>
> end
>
> I can solve my problem by putting the "real" processing in the elsif part
 of
> the if statement, but the processing logic already involves three nested
 ifs
> and I don't want to indent them another level by putting them in the
 elsif.
>
> Any ideas would be appreciated!
>
>
>
>

If you are not doing anything regarding your 'invalid' rows your select shouldn't fetch them. IMO, it also better to put complex validation in triggers and/or functions, that would at least make sure they are in *one* place. (I'm currently working at a site where they didn't do that resulting in noone having a complete overview of what is validated where, we also have front-end and server validation written). That said, in your current setup the easiest way is to setup this structure declare
invalid_record exception;
for emp_record in cur_employees loop

   begin
   if 'invalid record' then

      raise invalid_record;
   end if;
   <continue normal processing>;
  exception

     when invalid_record then
        dbms_output.put_line('a suitable message');
-- or instead the line above just
        null; -- do nothing

   end;
  end loop;

Hth,

Sybrand Bakker, Oracle DBA Received on Thu Aug 10 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US