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
On Mon, Nov 26, 2012 at 12:33 PM, Michael Moore <michaeljmo...@gmail.com>wrote:
> 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
