[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Generate Oracle Sequence

Posted on 2004-09-14
17
Medium Priority
?
1,178 Views
Last Modified: 2008-01-09
Hi

I need to generate oracle sequence for Social security numbers to start with S(followed by 7 zero and the last character is 1) like S00000001 and so on..

How can I do this in oracle.
0
Comment
Question by:mahjag
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +3
17 Comments
 
LVL 11

Accepted Solution

by:
cjjclifford earned 172 total points
ID: 12055467
create seqence social_sec_seq;
select 'S' || lpad( social_sec_seq.nextval, 0, 7 ) from dual;
0
 
LVL 3

Assisted Solution

by:dnarramore
dnarramore earned 164 total points
ID: 12055476
Create an Oracle sequence that starts with 1:

CREATE SEQUENCE TEST_SEQ
  START WITH 1
  MAXVALUE 9999999
  MINVALUE 0
  NOCYCLE
  NOCACHE
  NOORDER;

Then create this update trigger on your table:

CREATE OR REPLACE TRIGGER TABLE1_INSERT_TRIG
BEFORE INSERT
ON TABLE1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;

BEGIN
   tmpVar := 0;

   SELECT test_seq.NEXTVAL
   INTO tmpVar
   FROM dual;
   
   :NEW.PK := 'S' || lpad(to_char(tmpVar), 7, '0');


   EXCEPTION
     WHEN OTHERS THEN
       
       RAISE;
END ;
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12055480
sorry,

SELECT 'S' || lpad( sec_seq.nextval, 7, 0 ) from dual;
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 8

Assisted Solution

by:sapnam
sapnam earned 164 total points
ID: 12055500
Well Oracle sequecne numbers are numeric, but we can work around that by using a sequence to generate a number and then prefix it by S in a before insert trigger or in your front end

The sequence gets created with the command

CREATE sequence soc_sec_seq
increment by 1
minvalue 1
maxvalue 99999999
nocycle
cache 50

If you choose a before insert trigger approach your code there will be

create or replace trigger <your trigger name>
 before insert of <your table name>
for each row
declare
  sec_no number(8);
begin
   select soc_sec_seq.nextval
    into sec_no
   from dual;
   :new.your_field_name := 'S'||lpad(to_char(sec_no),8,'0');
end;
/



0
 

Author Comment

by:mahjag
ID: 12055501
Nice to get a quick response..

I wanted to use the sequence to insert a table in PL/SQL, Do I have to store this sequence in a table?
0
 
LVL 4

Expert Comment

by:Kaarthick
ID: 12055511
Try this,

select 'S' || decode(length(to_char(SEQ_NAME.NEXTVAL)),1,'000000'||SEQ_NAME.NEXTVAL) from dual

0
 
LVL 4

Expert Comment

by:Kaarthick
ID: 12055538
You shoud add more conditions to decode that query.

But the query given by cjjclifford  is apt.
0
 

Author Comment

by:mahjag
ID: 12055729
SELECT 'S' || lpad( sec_seq.nextval, 8, 0 ) from dual;

will give me S000000001, but what happens when this exceeds 10? meaning my lpad will not work?
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12055732
Either use a BEFORE INSERT trigger as outlined above, or simply

--
-- Assuming "CREATE TABLE table_name( id varchar2(20) not null, etc varchar2(20) );
--
INSERT INTO table_name VALUES(  'S' || lpad( sec_seq.nextval, 7, 0 ), 'etc' );

in the PL/SQL code

(note that in pl/sql it is not possible to assign the value of a sequence directly to a variable, you have to select it into the var, but if you INSERT directly in the code, the sequence should be available)
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12055756
mahjag, what do you mean "exceeds 10"?

SQL> SELECT 'S' || lpad( 1010010, 20, 0 ) from dual;

'S'||LPAD(1010010,20,
---------------------
S00000000000001010010

there is no size limitation to what is being returned...
0
 

Author Comment

by:mahjag
ID: 12055829
Sorry I take that question back.. my earlier question was if the sequence exceeds a value 10 and I was wondering If I always do lpad(8,0 ) for zeros will that work? but it did work!!!

One more question that I have is I could not store the sequence in PL/SQL

I have this statement

v_soc_sec := 'S' || lpad(soc_sec_seq.nextval,8,0) ;

I am getting error table or view or sequence cannot be used at this context?

How will I store the value into a local variable?
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12055845
you can't store sequence like that in PL/SQL

do the following:

DECLARE
   sec_id VARCHAR(20);
BEGIN
    SELECT 'S' || lpad( soc_seq_seq.nextval, 8, 0 )
    INTO seq_id
    FROM dual;

    -- etc
END;
/

this should do it...
0
 
LVL 3

Expert Comment

by:oratim
ID: 12055860
I believe he thinks that the lpad will add 7 0's to the front of the number no matter what.

Mahjag, all lpad does is ensure the the string is 7 characters long, no matter what length the original string was.
if the sequence is 10, the value returned will be S0000010, if it is 1000, you get S0001000, etc.

0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12582123
split
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question