• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1181
  • Last Modified:

Generate Oracle Sequence

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
mahjag
Asked:
mahjag
  • 6
  • 3
  • 2
  • +3
3 Solutions
 
cjjcliffordCommented:
create seqence social_sec_seq;
select 'S' || lpad( social_sec_seq.nextval, 0, 7 ) from dual;
0
 
dnarramoreCommented:
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
 
cjjcliffordCommented:
sorry,

SELECT 'S' || lpad( sec_seq.nextval, 7, 0 ) from dual;
0
Industry Leaders: 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!

 
sapnamCommented:
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
 
mahjagAuthor Commented:
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
 
KaarthickCommented:
Try this,

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

0
 
KaarthickCommented:
You shoud add more conditions to decode that query.

But the query given by cjjclifford  is apt.
0
 
mahjagAuthor Commented:
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
 
cjjcliffordCommented:
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
 
cjjcliffordCommented:
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
 
mahjagAuthor Commented:
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
 
cjjcliffordCommented:
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
 
oratimCommented:
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
 
cjjcliffordCommented:
split
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now