On Nov 8, 12:36 pm, VIGNESH PRAJAPATI <[email protected]> wrote:
> We know that id of any table is of integer type and auto-increment in that
> table is fine. But my problem is that i want to increment a combination of
> string and integer. For example String : ABC is the starting code for the
> unique id.
>
> First Id of my table should be primary key and
>
> ie ABC10001, ABC10002,..... and so on.
>
> Without using another key as auto-increment. Please do not use two type of
> unique key.
>
> --
>
> *Vignesh Prajapati*
> Tel: 9427415949 |
> [email protected] |www.vipras.com.co.in
> MYTK [image: Facebook] <https://www.facebook.com/vigs143> [image:
> Twitter]<http://twitter.com/#%21/vigs143> [image:
> LinkedIn] <http://www.linkedin.com/pub/vignesh-prajapati/37/756/46a> [image:
> about.me]
> <http://www.way4fun.tk><http://r1.wisestamp.com/r/landing?promo=7&dest=http%3A%2F%2Fwww.wises...>
This is an Oracle group , not Sybase or SQL Server, so the
'autoincrement' mechanism is far different. It is possible to use a
sequence value with a string to create a character-based, auto-
incrementing primary key:
SQL> create table keytest(
2 mykey varchar2(40) not null,
3 mydata varchar2(100),
4 constraint keytest_pk
5 primary key (mykey)
6 );
Table created.
SQL>
SQL> create sequence myseq
2 start with 1 increment by 1 nocycle nomaxvalue;
Sequence created.
SQL>
SQL> create trigger pop_key
2 before insert on keytest
3 for each row
4 begin
5 select 'ABC'||lpad(myseq.nextval,10,'0')
6 into :new.mykey
7 from dual;
8 end;
9 /
Trigger created.
SQL>
SQL>
SQL> begin
2 for i in 1..10 loop
3 insert into keytest
4 (mydata)
5 values
6 ('This is record number '||i);
7 end loop;
8
9 commit;
10
11 end;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from keytest order by 1;
MYKEY MYDATA
----------------------------------------
----------------------------------------------------------------------------------------------------
ABC0000000001 This is record number 1
ABC0000000002 This is record number 2
ABC0000000003 This is record number 3
ABC0000000004 This is record number 4
ABC0000000005 This is record number 5
ABC0000000006 This is record number 6
ABC0000000007 This is record number 7
ABC0000000008 This is record number 8
ABC0000000009 This is record number 9
ABC0000000010 This is record number 10
10 rows selected.
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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
