@David :-)
On Mon, Nov 26, 2012 at 1:48 PM, ddf <orat...@msn.com> wrote:
>
>
> On Monday, November 26, 2012 1:47:36 PM UTC-7, Michael Moore wrote:
>>
>> David,
>> won't this .... :new.memberid := 'SG010'||memberid_seq.nextval;
>> give you numbers like this:
>>
>> SG10998
>> SG10999
>> SG101000
>> SG101001
>>
>> Maybe the sort order doesn't matter?
>>
>> Mike
>>
>>
>
> It certainly will, and I wasn't endorsing the practice, simply providing
> an example of how to go about creating such entries from sequence values.
> I'm not a fan of mangling sequences to create character values generated
> by some ... err ... inferior database product however the Original Poster
> did not seem interested in correcting the problem, simply in propagating
> it. The application design is flawed, to say the least, and, yes, I'd be
> doing this in the application rather than a trigger. And even in a
> trigger it's possible to not run afoul of the sort order:
>
> SQL> create or replace trigger memberid_trg
> 2 before insert on customers
> 3 for each row
> 4 begin
> 5 :new.memberid := 'SG010'||lpad(memberid_seq.nextval,25,'0');
> 6 end;
> 7 /
>
> Trigger created.
>
> SQL>
> SQL> insert into customers (fname, mname, lname, contactname, bname,
> federalid, baddress, bcity, bstate,
> , telephone, cellphone, fax, email, joindate, status, zone)
> 2 values ('Smort','Fnarm','Bleezo', 'Harry', 'Bonzo', '9999999','1213
> 14th Street','Smallville','ID','
> ','999-999-9999','888-888-8888','777-777-7777','blimpon...@flomwerter.com',
> systimestamp, 'Active','Snood
>
> 1 row created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select * From customers where custid > 800;
>
> CUSTID MEMBERID FNAME
> ---------- ------------------------------ ------------------------------
> MNAME LNAME
> ------------------------------ ------------------------------
> CONTACTNAME
> ------------------------------
> BNAME
>
> --------------------------------------------------------------------------------
> FEDERALID
> ------------------------------
> BADDRESS
>
> --------------------------------------------------------------------------------
> BCITY BS BZIPCODE
> ------------------------------ -- ----------
> MAILADDRESS
>
> --------------------------------------------------------------------------------
> MCITY MS MZIPCODE TELEPHONE CELLPHONE
> ------------------------------ -- ---------- ------------- -------------
> FAX
> -------------
> EMAIL
> ---------------------------------------------------------------------------
> JOINDATE
> ---------------------------------------------------------------------------
> STATUS ZONE
> -------- -----
> 801 SG0100000000000000000000000801 Smort
> Fnarm Bleezo
> Harry
> Bonzo
> 9999999
> 1213 14th Street
> Smallville ID 44444
> 1415 16th Street
> Tromp DE 55555 999-999-9999 888-888-8888
> 777-777-7777
> blimpon...@flomwerter.com
> 26-NOV-12 02.46.12.097000 PM
> Active Snood
>
>
> SQL>
>
>
> Which also doesn't take into consideration how those memberid values were
> inserted by the Original Poster.
>
>
> David Fitzjarrell
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
