I suggest that you create an Oracle Sequence to generate a number. You can
append that number to SG and then insert the record. I further suggest that
you do not use triggers to generate the key/sequence number. Doing so will
create problems when you want to migrate data from your production
environment into your development and test environments. It is better to
control the keys at the application level for the sake of data migration.
If you never plan on copying data from one db to another, then use triggers
if you like.
We have a convention for our oracle sequences. Each table has a
corresponding sequence. Always increment the sequence by 10. The last digit
of the sequence corresponds to a specific environment: 0 = prod, 1 = stage,
2 = test, 3 = dev. This way, if you copy data from one env to another, the
keys do not collide. There are still problems with unique columns and such,
but I am going far astray of the original question.
In short, something like this
SET SERVEROUTPUT ON
DECLARE
my_key VARCHAR2 (11);
BEGIN
SELECT 'SG' || TO_CHAR (demo_ord_seq.NEXTVAL, 'FM0999999')
INTO my_key FROM DUAL;
DBMS_OUTPUT.put_line (my_key);
END;
the result of running this is ...
SG0000054
PL/SQL procedure successfully completed.
On Mon, Nov 26, 2012 at 11:28 AM, Rahil Maknojia <rahilmakno...@gmail.com>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 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
