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)
icon9.gif  Start value of sequence 00001 [message #336828] Tue, 29 July 2008 03:14 Go to next message
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
Re: Start value of sequence 00001 [message #336832 is a reply to message #336828] Tue, 29 July 2008 03:20 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Use trigger to change value of key while insertion.

Regards,
Rajat
Re: Start value of sequence 00001 [message #336834 is a reply to message #336828] Tue, 29 July 2008 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use TO_CHAR.
It seems you know it for a date, use it also for the sequence number.

Regards
Michel
Re: Start value of sequence 00001 [message #336836 is a reply to message #336833] Tue, 29 July 2008 03:22 Go to previous messageGo to next message
Frank
Messages: 7880
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?!
icon12.gif  Re: Start value of sequence 00001 [message #336838 is a reply to message #336836] Tue, 29 July 2008 03:24 Go to previous messageGo to next message
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
Re: Start value of sequence 00001 [message #336843 is a reply to message #336828] Tue, 29 July 2008 03:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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.
Re: Start value of sequence 00001 [message #336846 is a reply to message #336843] Tue, 29 July 2008 03:45 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Using LPAD(your_number,5,'0') can also help you.
icon14.gif  Re: Start value of sequence 00001 [message #336848 is a reply to message #336828] Tue, 29 July 2008 03:51 Go to previous messageGo to next message
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;
Re: Start value of sequence 00001 [message #336850 is a reply to message #336848] Tue, 29 July 2008 03:56 Go to previous messageGo to next message
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
Re: Start value of sequence 00001 [message #336853 is a reply to message #336848] Tue, 29 July 2008 04:13 Go to previous messageGo to next message
Frank
Messages: 7880
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!)
Re: Start value of sequence 00001 [message #336857 is a reply to message #336853] Tue, 29 July 2008 04:20 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Quote:
Split the contents into separate columns.


Sorry but I didn't get you.
Re: Start value of sequence 00001 [message #336859 is a reply to message #336857] Tue, 29 July 2008 04:23 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Start value of sequence 00001 [message #336864 is a reply to message #336828] Tue, 29 July 2008 04:28 Go to previous messageGo to next message
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
Re: Start value of sequence 00001 [message #336867 is a reply to message #336864] Tue, 29 July 2008 04:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you decide on how to handle your initial problem now?
Re: Start value of sequence 00001 [message #336868 is a reply to message #336828] Tue, 29 July 2008 04:35 Go to previous messageGo to next message
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
Re: Start value of sequence 00001 [message #336878 is a reply to message #336853] Tue, 29 July 2008 04:59 Go to previous messageGo to next message
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.
Re: Start value of sequence 00001 [message #336883 is a reply to message #336878] Tue, 29 July 2008 05:10 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
Frank
Messages: 7880
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..
Re: Start value of sequence 00001 [message #336893 is a reply to message #336887] Tue, 29 July 2008 05:23 Go to previous messageGo to next message
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 Go to previous message
Bill B
Messages: 1484
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);

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


Current Time: Fri Dec 09 21:33:46 CST 2016

Total time taken to generate the page: 0.10819 seconds