OraFAQ Forum: SQL & PL/SQL » Start value of sequence 00001

Home » SQL & PL/SQL » SQL & PL/SQL » Start value of sequence 00001 (Oracle 10g, SQL*Plus: Release 10.2.0.1.0, Windows 2003 Server)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Start value of sequence 00001 [message #336828]

Tue, 29 July 2008 03:14

naikjigar
Messages: 51
Registered: July 2008
Location: India

Member

I want my sequence to return 00001 instead of 1

I tried with following but its not happening.

In my actual case i want to generate the primary key of the following format

<2><YYYY><MM><DD><HH><MM><SS><5 DIGIT UNIQUE NUMBER>
LENGTH 20

so i have written the following query to generate primary key.

SELECT '2' || TO_CHAR(SYSDATE,'YYYYMMDDHHMISS') || SEQ_OUTBOUND.NEXTVAL AS PrimaryKey FROM DUAL;

but my sequence is returning 1 instead of 00001. so the total length start with 16 only instead of 20. Sad

Please suggest me the alternative.

Thanks & Regards,
Jigar Naik

Report message to a moderator

Re: Start value of sequence 00001 [message #336832 is a reply to message #336828]

Tue, 29 July 2008 03:20

Frank
Messages: 7901
Registered: March 2000

Senior Member

A sequence returns a number. 00001 is a stringrepresenation of the number 1
Use to_char(your_num, '00000') to add leading zeroes.

[Updated on: Tue, 29 July 2008 03:21]

Report message to a moderator

Re: Start value of sequence 00001 [message #336833 is a reply to message #336828]

Tue, 29 July 2008 03:21

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Use trigger to change value of key while insertion.

Regards,
Rajat

Report message to a moderator

Re: Start value of sequence 00001 [message #336834 is a reply to message #336828]

Tue, 29 July 2008 03:21

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use TO_CHAR.
It seems you know it for a date, use it also for the sequence number.

Regards
Michel

Report message to a moderator

Re: Start value of sequence 00001 [message #336836 is a reply to message #336833]

Tue, 29 July 2008 03:22

Frank
Messages: 7901
Registered: March 2000

Senior Member

rajatratewal wrote on Tue, 29 July 2008 10:21

Use trigger to change value of key while insertion.

Regards,
Rajat

Ouch.
Why would you do that?!

Report message to a moderator

Re: Start value of sequence 00001 [message #336838 is a reply to message #336836]

Tue, 29 July 2008 03:24

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Because he needs a custom primary key.
And he asked for the options.

I had given an option.

Regards,
Rajat

Report message to a moderator

Re: Start value of sequence 00001 [message #336843 is a reply to message #336828]

Tue, 29 July 2008 03:32

naikjigar
Messages: 51
Registered: July 2008
Location: India

Member

How am i going to use TO_CHAR

if my seq.nextval gives me 10 than my total length will be 21 digits. n so on. 22.. 23

i can not even measure the length of the seq's returned value, and append conditional 0

SELECT '2' || TO_CHAR(SYSDATE,'YYYYMMDDHHMISS') || '0000' || TO_CHAR(SEQ_OUTBOUND.NEXTVAL) AS PrimaryKey FROM DUAL

PRIMARYKEY
------------------------
22008072901523100009                   //Length is 20 fine.

SQL> /

PRIMARYKEY
------------------------
220080729015233000010                  //Length is 21 bad.

SQL>

[Updated on: Tue, 29 July 2008 03:33]

Report message to a moderator

Re: Start value of sequence 00001 [message #336845 is a reply to message #336843]

Tue, 29 July 2008 03:34

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Use it the way Frank posted it :

Quote:

Use to_char(your_num, '00000') to add leading zeroes.

(That will also add an additional leading blank, though, try the format model 'fm00000' instead if you don't want that.

Report message to a moderator

Re: Start value of sequence 00001 [message #336846 is a reply to message #336843]

Tue, 29 July 2008 03:45

sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal

Member

Using LPAD(your_number,5,'0') can also help you.

Report message to a moderator

Re: Start value of sequence 00001 [message #336848 is a reply to message #336828]

Tue, 29 July 2008 03:51

naikjigar
Messages: 51
Registered: July 2008
Location: India

Member

I did it in the following way,

Created following sequence

CREATE SEQUENCE SEQ_OUTBOUND
INCREMENT BY 1
START WITH 100001
MAXVALUE 999999
MINVALUE 100001
NOCACHE
CYCLE;

which will give me 6 digit number

applyed substr function which will remove start 1 so i will get 00001

final query to get 20 digit primary key.

SELECT '2' || TO_CHAR(SYSDATE,'YYYYMMDDHHMISS') || SUBSTR(TO_CHAR(SEQ_OUTBOUND.NEXTVAL),2) AS PrimaryKey FROM DUAL;

Report message to a moderator

Re: Start value of sequence 00001 [message #336850 is a reply to message #336848]

Tue, 29 July 2008 03:56

naikjigar
Messages: 51
Registered: July 2008
Location: India

Member

yeah LAP is also working fine.

SELECT '2' || TO_CHAR(SYSDATE,'YYYYMMDDHHMISS') || LPAD(SEQ_OUTBOUND.NEXTVAL,5,'0') AS PrimaryKey FROM DUAL

Report message to a moderator

Re: Start value of sequence 00001 [message #336853 is a reply to message #336848]

Tue, 29 July 2008 04:13

Frank
Messages: 7901
Registered: March 2000

Senior Member

Very well, suit yourself.
Use nonsensical functions to achieve what can be done by using the one and only built-in function that was MADE for doing that.
LPAD is supposed to be used on strings, not on numbers.

I have an even better advise: Split the contents into separate columns. That will save you a lot of headaches later on. (Trust me!)

Report message to a moderator

Re: Start value of sequence 00001 [message #336857 is a reply to message #336853]

Tue, 29 July 2008 04:20

naikjigar
Messages: 51
Registered: July 2008
Location: India

Member

Quote:

Split the contents into separate columns.

Sorry but I didn't get you.

Report message to a moderator

Re: Start value of sequence 00001 [message #336859 is a reply to message #336857]

Tue, 29 July 2008 04:23

Frank
Messages: 7901
Registered: March 2000

Senior Member

You have all thes bits of valuable info: sysdate, order of records per date, etc.
Use separate columns to store these, so you will be able to use the info in that later.

Report message to a moderator

Re: Start value of sequence 00001 [message #336864 is a reply to message #336828]

Tue, 29 July 2008 04:28

naikjigar
Messages: 51
Registered: July 2008
Location: India

Member

yeah that is true, and i really appreciate your advice. but i am not supposed to change the database table structure as it is a 3rd party database.

but anyways.. i can suggest them,

well thanks a lot all.. for your support.

Regards,
Jigar Naik
xx

Report message to a moderator

Re: Start value of sequence 00001 [message #336867 is a reply to message #336864]

Tue, 29 July 2008 04:31

Frank
Messages: 7901
Registered: March 2000

Senior Member

Did you decide on how to handle your initial problem now?

Report message to a moderator

Re: Start value of sequence 00001 [message #336868 is a reply to message #336828]

Tue, 29 July 2008 04:35

naikjigar
Messages: 51
Registered: July 2008
Location: India

Member

yeah most probably i will use the following query

SELECT '2' || TO_CHAR(SYSDATE,'YYYYMMDDHHMISS') || LPAD(SEQ_OUTBOUND.NEXTVAL,5,'0') AS PrimaryKey FROM DUAL

Report message to a moderator

Re: Start value of sequence 00001 [message #336878 is a reply to message #336853]

Tue, 29 July 2008 04:59

sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal

Member

Frank wrote on Tue, 29 July 2008 14:58

Use nonsensical functions to achieve what can be done by using the one and only built-in function that was MADE for doing that.
LPAD is supposed to be used on strings, not on numbers.

Please tell us the disadvantage of using LPAD in this case.

Report message to a moderator

Re: Start value of sequence 00001 [message #336883 is a reply to message #336878]

Tue, 29 July 2008 05:10

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Quote:

Please tell us the disadvantage of using LPAD in this case.

One disadvantage is that you have:

- Executed one implicit conversion from number to char
- Executed one LPAD function.

When you just do an

to_char(SEQ_OUTBOUND.NEXTVAL,'fm00000')

you only have executed one explicit conversion from number to char.

So you have only half the number of function calls running on the DB.

Another disadvantage is that you might get wrap-around duplicate values some day. Consider the difference in results:

SELECT LPad(4444,2,'0')     FROM dual;

SELECT to_char(4444,'fm00') FROM dual;

[Updated on: Tue, 29 July 2008 05:14]

Report message to a moderator

Re: Start value of sequence 00001 [message #336887 is a reply to message #336878]

Tue, 29 July 2008 05:14

Frank
Messages: 7901
Registered: March 2000

Senior Member

sarwagya wrote on Tue, 29 July 2008 11:59

Frank wrote on Tue, 29 July 2008 14:58

Use nonsensical functions to achieve what can be done by using the one and only built-in function that was MADE for doing that.
LPAD is supposed to be used on strings, not on numbers.

Please tell us the disadvantage of using LPAD in this case.

To use LPAD properly, you should first convert your number to a string.
Guess what function you would use for that..

Report message to a moderator

Re: Start value of sequence 00001 [message #336893 is a reply to message #336887]

Tue, 29 July 2008 05:23

sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal

Member

I got my answer from Thomas.
I was taking the implicit conversion for granted.

Thank you.

[Updated on: Tue, 29 July 2008 05:24]

Report message to a moderator

Re: Start value of sequence 00001 [message #337065 is a reply to message #336828]

Tue, 29 July 2008 16:37

Bill B
Messages: 1971
Registered: December 2004

Senior Member

MAKE YOUR SEQUENCE USING THE FOLLOWING COMMAND

CREATE SEQUENCE SEQ_OUTBOUND
INCREMENT BY 1
START WITH 1
MAXVALUE 99999
MINVALUE 1
NOCACHE
CYCLE;

And insert your primary key during the insert, not by making the key first.

insert into my_table(key,col1,col2) 
values('2'||TO_CHAR(SYSDATE,'YYYYMMDDHHMISS')||to_char(SEQ_OUTBOUND.NEXTVAL,'fm0000'),junk1,junk2);

Report message to a moderator

Previous Topic:	Error ORA-01460
Next Topic:	Cursor cache in nested queries ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 11:50:06 CST 2024