Solved

Generate Oracle Sequence

Posted on 2004-09-14
17
1,174 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 43 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 41 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Assisted Solution

by:sapnam
sapnam earned 41 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

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 …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

728 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