Re: username/password generation with a trigger

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 17 Sep 2002 10:03:51 -0700
Message-ID: <92eeeff0.0209170903.3da03d9_at_posting.google.com>

rens_at_joxx.nl (|Rens| 0) wrote in message news:<617e9c3f.0209160437.a286a10_at_posting.google.com>...
> Hello,
>
> I'm a total newbie when it comes to (Oracle) databases, so I was
> wondering if someone could help me out with a problem.
>
> I have developed a web-based JSP application. This website uses a
> login-system based on a username/password pair in the database. In
> short, I have one table called 'customer' which is linked to another
> one 'site_customer'. In the latter one, there are two fields,
> username/password.
>
> customer.firstname
> customer.lastname
> customer.customer_num -> site_customer.customer_num
> site_customer.username
> site_customer.password
>
> The customer table also contains a first,- and last name as you can
> see. Now, here comes the problem: I want to generate a username based
> on these names (e.g. 'john smith' would result in 'jsmithxxxx', where
> xxxx is an incremeting number (if there is a 'jonathan smith' or
> something))
>
> How on earth can I do this? I tried to read something about triggers
> that would generate a new record for the site_customer table. I'm
> kinda stuck with this. Also: how do I generate a random
> password/string .. ?
>
> Any help would be greatly appreciated,
>
> Rens van Leeuwen

There are number of ways you can accomplish this. One way is to use a function.

First create a sequence for generating a unique number. e.g. CREATE SEQUENCE username_gen_seq

   INCREMENT BY 1
   START WITH 1
   MAXVALUE 9999999999999
   MINVALUE 1; CREATE OR REPLACE FUNCTION Username_Generator (

   fname_     IN VARCHAR2,
   lname_     IN VARCHAR2 ) RETURN VARCHAR2
IS
   ret_       VARCHAR2(100);
   seq_       NUMBER;

BEGIN
   SELECT username_gen_seq.nextval
   INTO seq_
   FROM dual;
   --
   ret_ := SUBSTR(fname_, 1, 1) || lname_ || TO_CHAR(seq_);    RETURN ret_;
END Username_Generator;
/

This will atleast get you started. For more info, visit http://tahiti.oracle.com //Rauf Sarwar Received on Tue Sep 17 2002 - 19:03:51 CEST