Re: Validation in a Stored Proc

From: john sprague <bearpig_at_attglobal.net>
Date: Tue, 19 Jun 2001 19:20:51 -0500
Message-ID: <3B2FEC63.45862801@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 - 19:20:51 CDT