Re: Need help quick!!

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 27 Jan 2003 23:42:52 -0800
Message-ID: <92eeeff0.0301272342.1d35cebc_at_posting.google.com>


nomadeflea_at_yahoo.fr (Flea) wrote in message news:<24083942.0301260837.513771c1_at_posting.google.com>...
> Hi,
>
> New to pl/sql, I need some help fast!!

Is this a college assignment?

> I would like to create a procedure, simply to make an update of a new
> client:
> I would like to create a new client, adding his name, surname, adresse
> and telephone, and to raise exceptions, if I type the wrong data in
> the wrong field, for instance:

What do you mean by wrong data in the wrong field?

Since I don't see any foreign-key constraints...meaning it does not refernce another table, I assume you are not asking about referntial integrity. Although you should read up on this if you haven't already.

If you are asking about simple datatype check.. meaning numbers in number field etc then that is best handled by your client. Create your datafield etc according to the datatype of your table column... that will throw an error before even getting to the server. You can even specify an input mask for telephone number.

That would leave name, surname and address. For name and surname, again you can only allow e.g. alpha characters which can be handled easily on client side. address can be broken out into street, city, zip/postal code, country.

Let the server handle the important stuff like referential integrity. Let your client do the simple initial check on datatype, input mask etc. Most Windoze development tools allow that more then adequately. Initial client check can prevent lots of unnecessary round trips to the server.

If you still want just a simple procedure to do what you are asking then here is a template,

create or replace procedure foo (

   param1_ number,
   ....... )
is

   var1_ number;
   ....
begin

   if (my data passes the check) then

      insert into ....;
   else

      raise_application_error(-20001, 'My error message');    end if;
end foo;
/

Regards
/Rauf Sarwar Received on Tue Jan 28 2003 - 08:42:52 CET

Original text of this message