Home » SQL & PL/SQL » SQL & PL/SQL » Sequence Trigger (Oracle 10 G Version)
Sequence Trigger [message #578224] Mon, 25 February 2013 20:05 Go to next message
rasi_85
Messages: 9
Registered: May 2008
Junior Member
Hi,

I am using Oracle 10G version. I need a code base for new Sequence Trigger.

Requirement :
As per the request, before INSERT trigger will generate the sequence ID from AA001 to AA999 value. But once the sequence is reached to AA999, the next sequence value will be generated normal (start from AB001 etc..).

Any Help...Please provide the suggestion..

Thanks.
Rasi

[Updated on: Mon, 25 February 2013 20:09]

Report message to a moderator

Re: Sequence Trigger [message #578227 is a reply to message #578224] Tue, 26 February 2013 00:13 Go to previous messageGo to next message
lakshmis
Messages: 101
Registered: November 2008
Location: India
Senior Member
Hi Rasi,

Please refer the below link to generate the alphabet sequence:
https://forums.oracle.com/forums/thread.jspa?threadID=913858

Concatenate the generated sequence with the numbers to generate the sequence ID.

Regards,
Lakshmi.
Re: Sequence Trigger [message #578229 is a reply to message #578227] Tue, 26 February 2013 00:19 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
you can also convert the char to ascii then add 1 and convert it again to character

example

select chr(x), x  ascii FROM (select rownum x from all_tables)
 where x between 65 and 90
Re: Sequence Trigger [message #578235 is a reply to message #578224] Tue, 26 February 2013 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To extend xpact83's answer.
For the first 26000 values, the 3 digits are mod(sequence_value,1000); the second letter is chr(ascii('A')+trunc(sequence_value/1000)).
I let your extend the computation for the first letter.

Note: what happen when you reach and exceed ZZ999?

Regards
Michel
Re: Sequence Trigger [message #578238 is a reply to message #578235] Tue, 26 February 2013 00:49 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
may be they will add again another character ? AZZ999? hehe
Re: Sequence Trigger [message #578283 is a reply to message #578238] Tue, 26 February 2013 09:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2079
Registered: January 2010
Senior Member
xpact83 wrote on Tue, 26 February 2013 01:49
may be they will add again another character ? AZZ999? hehe


Assuming it cycles from ZZ999 back to AA001:

SQL> drop sequence s1
  2  /

Sequence dropped.

SQL> drop table tbl purge
  2  /

Table dropped.

SQL> create sequence s1
  2    start with 0
  3    minvalue 0
  4    maxvalue 675323
  5    cycle
  6  /

Sequence created.

SQL> create table tbl(
  2                   l   number,
  3                   val varchar2(5)
  4                  )
  5  /

Table created.

SQL> create or replace
  2    trigger tbl_bir
  3      before insert
  4      on tbl
  5      for each row
  6      declare
  7          v_seq number := s1.nextval;
  8      begin
  9          :new.val := chr(ascii('A') + trunc(trunc(v_seq / 999) / 26)) ||
 10                      chr(ascii('A') + mod(trunc(v_seq / 999),26)) ||
 11                      to_char(mod(v_seq,999) + 1,'FM000');
 12  end;
 13  /

Trigger created.

SQL> insert
  2    into tbl(l)
  3    select  level l
  4      from  dual
  5      connect by level <= 675400
  6  /

675400 rows created.

SQL> create index tbl_idx on tbl(l)
  2  /

Index created.

SQL> select  *
  2    from  tbl
  3    where l between &1 and &2
  4  /
Enter value for 1: 1
Enter value for 2: 5
old   3:   where l between &1 and &2
new   3:   where l between 1 and 5

         L VAL
---------- -----
         1 AA001
         2 AA002
         3 AA003
         4 AA004
         5 AA005

SQL> /
Enter value for 1: 995
Enter value for 2: 1005
old   3:   where l between &1 and &2
new   3:   where l between 995 and 1005

         L VAL
---------- -----
       995 AA995
       996 AA996
       997 AA997
       998 AA998
       999 AA999
      1000 AB001
      1001 AB002
      1002 AB003
      1003 AB004
      1004 AB005
      1005 AB006

11 rows selected.

SQL> /
Enter value for 1: 1995
Enter value for 2: 2005
old   3:   where l between &1 and &2
new   3:   where l between 1995 and 2005

         L VAL
---------- -----
      1995 AB996
      1996 AB997
      1997 AB998
      1998 AB999
      1999 AC001
      2000 AC002
      2001 AC003
      2002 AC004
      2003 AC005
      2004 AC006
      2005 AC007

11 rows selected.

SQL> /
Enter value for 1: 675320
Enter value for 2: 675329
old   3:   where l between &1 and &2
new   3:   where l between 675320 and 675329

         L VAL
---------- -----
    675320 ZZ995
    675321 ZZ996
    675322 ZZ997
    675323 ZZ998
    675324 ZZ999
    675325 AA001
    675326 AA002
    675327 AA003
    675328 AA004
    675329 AA005

10 rows selected.

SQL> 


SY.
Re: Sequence Trigger [message #578284 is a reply to message #578283] Tue, 26 February 2013 09:55 Go to previous message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what about letting OP find by himself from what I posted him?

Tell me and I'll forget; show me and I may remember; involve me and I'll understand.

Regards
Michel
Previous Topic: error in creating DBMS_SCHEDULER job
Next Topic: ref cursor
Goto Forum:
  


Current Time: Mon Dec 22 14:39:58 CST 2014

Total time taken to generate the page: 0.24036 seconds