Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    9

    Arrow Unanswered: Creating alphanumeric sequential IDs

    This may have been asked til death .. but I simply cannot find any websites that explains this properly ...

    I am creating a table with Member_ID. And this Member_ID, I want to present as such :

    M0001
    M0002
    M0003
    M0004

    I know Oracle has the SEQUENCE statement, but what I read is it does only 1, 2, 3, 4, etc ... ...

    How can I write the codes for my ID to be generated as such?

    Thx ..!

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    should not be too hard.
    Just create a stored procedure which return a varchar2. When called, this function get the nextval from sequence and concats this value to 'M' and return the string to the calling code.
    You can even use this in triggers.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi again,

    mistype: create a stored function, not a stored procedure.

    Greetz
    Edwin van Hattem
    OCP DBA / System analyst

  4. #4
    Join Date
    Oct 2003
    Posts
    9
    I tink I really need a guide on the codes ... ie, the coding that I should write ...

  5. #5
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    how about :

    create or replace function special_alphanum_seq
    return varchar2
    as
    cursor c_seq is
    select sequence_name.nexval
    from _dual;
    lc_char constant varchar2(1) := 'M';
    lv_return_val varchar2(10);
    ln_nextvak number;
    begin
    open c_seq;
    fetch c_seq into ln_nextval;
    close c_seq;
    lv_return_val := lc_char||ln_nextval;
    return lv_return_val;
    end;
    /

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  6. #6
    Join Date
    Oct 2003
    Posts
    9

    Thumbs up

    Hi evanhattem,

    This looks promising and cool .. I will try it out ..

  7. #7
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    i saw some typos in my code. I might not compile, but just check out the typos.

    greetz
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •