On Monday, November 26, 2012 12:28:22 PM UTC-7, Rahil Maknojia wrote:
>
> Hello Experts,
>
> I found this group in google while I was searching for how to auto
> increment strings with the numbers in it.
>
> I am switching database from access to oracle 11g. I have create all the
> required tables, but I am stuck at one point. The previous person who
> created access database had auto increment with SG0101, SG0102,........ In
> oracle, I know we can auto increment primary keys but only with the numbers
> not with characters.
>
> So I have customerid which is a primary key and it automatically
> increments the number, but I have one more column with memberid where I am
> inserting all the ids that start with SG0101 bla bla.....
>
> I already have 800 member ID's that start with SG, but that value doesnt
> automatically increment, because I dont have any sequence or trigger to do
> that.
>
> So how do I create a sequence and trigger that will automatically start
> value with SG and keeps on auto incrementing?
>
> Here is the table info:
>
> CREATE TABLE "CUSTOMERS"
> ( "CUSTID" NUMBER,
> "MEMBERID" VARCHAR2(30),
> "FNAME" VARCHAR2(30),
> "MNAME" VARCHAR2(30),
> "LNAME" VARCHAR2(30),
> "CONTACTNAME" VARCHAR2(30),
> "BNAME" VARCHAR2(255),
> "FEDERALID" VARCHAR2(30),
> "BADDRESS" VARCHAR2(255),
> "BCITY" VARCHAR2(30),
> "BSTATE" VARCHAR2(2),
> "BZIPCODE" VARCHAR2(10),
> "MAILADDRESS" VARCHAR2(100),
> "MCITY" VARCHAR2(30),
> "MSTATE" VARCHAR2(2),
> "MZIPCODE" VARCHAR2(10),
> "TELEPHONE" VARCHAR2(13),
> "CELLPHONE" VARCHAR2(13),
> "FAX" VARCHAR2(13),
> "EMAIL" VARCHAR2(75),
> "JOINDATE" TIMESTAMP (6) WITH LOCAL TIME ZONE,
> "STATUS" VARCHAR2(8),
> "ZONE" VARCHAR2(5),
> CONSTRAINT "CUSTOMERS_PK" PRIMARY KEY ("CUSTID") ENABLE
>
>
> Any help will be appreciate it.
>
You can't create such a sequence -- sequences generate integers. Which
doesn't mean you can't create a trigger to do your bidding:
SQL> CREATE TABLE CUSTOMERS
2 (CUSTID NUMBER,
3 MEMBERID VARCHAR2(30),
4 FNAME VARCHAR2(30),
5 MNAME VARCHAR2(30),
6 LNAME VARCHAR2(30),
7 CONTACTNAME VARCHAR2(30),
8 BNAME VARCHAR2(255),
9 FEDERALID VARCHAR2(30),
10 BADDRESS VARCHAR2(255),
11 BCITY VARCHAR2(30),
12 BSTATE VARCHAR2(2),
13 BZIPCODE VARCHAR2(10),
14 MAILADDRESS VARCHAR2(100),
15 MCITY VARCHAR2(30),
16 MSTATE VARCHAR2(2),
17 MZIPCODE VARCHAR2(10),
18 TELEPHONE VARCHAR2(13),
19 CELLPHONE VARCHAR2(13),
20 FAX VARCHAR2(13),
21 EMAIL VARCHAR2(75),
22 JOINDATE TIMESTAMP (6) WITH LOCAL TIME ZONE,
23 STATUS VARCHAR2(8),
24 ZONE VARCHAR2(5),
25 CONSTRAINT CUSTOMERS_PK PRIMARY KEY (CUSTID) ENABLE)
26 /
Table created.
SQL>
SQL> create sequence custid_seq
2 start with 1 increment by 1 nomaxvalue nocycle noorder;
Sequence created.
SQL>
SQL> create or replace trigger custid_trg
2 before insert on customers
3 for each row
4 begin
5 :new.custid := custid_seq.nextval;
6 end;
7 /
Trigger created.
SQL>
SQL>
SQL> begin
2 for i in 1..800 loop
3 insert into customers (memberid, fname, mname, lname,
contactname, bname, federalid, baddress, bcity, bstate, bzipcode,mailaddres
s, mcity, mstate, mzipcode, telephone, cellphone, fax, email, joindate,
status, zone)
4 values ('SG010'||i, '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',
systimestamp, 'Active','Snood');
5 end loop;
6
7 commit;
8
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> create sequence memberid_seq
2 start with 801 increment by 1 nomaxvalue nocycle noorder;
Sequence created.
SQL>
SQL> create or replace trigger memberid_trg
2 before insert on customers
3 for each row
4 begin
5 :new.memberid := 'SG010'||memberid_seq.nextval;
6 end;
7 /
Trigger created.
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
-------- -----
800 SG010800 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 01.11.23.709000 PM
Active Snood
SQL>
SQL> insert into customers (fname, mname, lname, contactname, bname,
federalid, baddress, bcity, bstate, bzipcode,mailaddress, mcity, mstate,
mzipcode
, telephone, cellphone, fax, email, joindate, status, zone)
2 values ('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',
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 SG010801 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 01.11.23.725000 PM
Active Snood
SQL>
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
