Solved

Generate Oracle Sequence

Posted on 2004-09-14
17
1,163 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
  • 6
  • 3
  • 2
  • +3
17 Comments
 
LVL 11

Accepted Solution

by:
cjjclifford earned 43 total points
Comment Utility
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
Comment Utility
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
Comment Utility
sorry,

SELECT 'S' || lpad( sec_seq.nextval, 7, 0 ) from dual;
0
 
LVL 8

Assisted Solution

by:sapnam
sapnam earned 41 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
You shoud add more conditions to decode that query.

But the query given by cjjclifford  is apt.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:mahjag
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
split
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now