Solved

Generate Oracle Sequence

Posted on 2004-09-14
17
1,165 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
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
 
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
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
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

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.

Question has a verified solution.

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

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

911 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

16 Experts available now in Live!

Get 1:1 Help Now