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: Validation in a Stored Proc

Re: Validation in a Stored Proc

From: William O'Neill <wfoneill_at_mindspring.com>
Date: Tue, 19 Jun 2001 20:48:16 -0700
Message-ID: <9gorvu$akb$1@slb0.atl.mindspring.net>

Thanks John, I'll try it out tomorrow at work. Bill....

"john sprague" <bearpig_at_attglobal.net> wrote in message news:3B2FEC63.45862801_at_attglobal.net...
>
>
> William O'Neill wrote:
>
> > Am using Powerbuilder 7.0.3 with Oracle 8.1.7 on WINNT; have been
 instructed
> > to create a stored procedure that will validate newly entered data(into
 a
> > Powerbuilder Datawindow). If everything passes validation, then, the
 stored
> > procedure would send this data to a mainframe system. My question - how
> > does the stored procedure know the variables? eg. if i code Select *
 from
> > tablexxxx where state = :is_state and city = :is_city, and the SQLCode
> > returns 'not found,' then I must return the user back, and he/she cannot
> > proceed until the error is corrected. How does the SP know what
 is_state
> > or :is_city is? This is how I would code the Select statement if I
 could
> > put the code in one of the events of the dw, but I've been instructed to
 do
> > everything in the SP. Any answers?
>
> Try one of the olsen books on plsql - good illustrations...
>
> You'll have a defined stored proceedure with variable mapping as in
> create or replace package body
> myvalidation
> as
> proceedure teststuff(teststate in varchar2,
> testcity in varchar2 ,
> successvar out varchar )
> v1 varchar2(24):=null;
> v1 varchar2(24):=null;
>
> cursor getvalidation is
> select
> state,
> zip
> from
> tablexxxx where state = teststate and city = testcity;
>
> begin
> open getvalidation;
>
> fetch getvalidation into v1, v2;
>
> if getvalidation%found then successvar := 'YEA';
> if getvalidation%notfound then successvar := '%$##@*';
>
> end teststuff;
> end myvalidation;
>
> You'll have a couple of parts to formally declaring the package - a header
 and a
> body . I just show you the body for the idea. I use packages instead of
 mere
> functions because I often want the result of -say - a calculation and a
 success
> indicator rather than a success indicator alone. Nevetheless - a function
 can
> work as well. Still need the package stuff stored.
>
> Now - your PW code will at some pont need to invoke and manage the package
 we
> just made - and it's lne will look like :
>
> myvalidation.teststuff(:is_state,
> :is_city,
> :is_success);
>
> then - an if/then evaluation to see if the value held in the var
:is_success is
> your success or your failure indicator - and the code passes on...
>
> The advantage ( IMHO) of the use of stored proeedures for validation is
 that
> they themselves can be managed as class requirements, codified, and passed
 out
> as requisite operations to be invoked whenever tablexxx or tableyyy are
 I/U
> actioned. Thus - garbage control becomes a write once use everywhere
 proceedure
> with 1 step maintence. Even contractors ( grin) can handle this one. I do
 have
> natives construct the validation requirements - however.
>
> jds
>
>
Received on Tue Jun 19 2001 - 22:48:16 CDT

Original text of this message

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