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
