Home » SQL & PL/SQL » SQL & PL/SQL » auto genrated no
auto genrated no [message #259924] Thu, 16 August 2007 19:43 Go to next message
hemu
Messages: 16
Registered: May 2007
Junior Member
plz help
i have a table trs in this table i have column tt which is varchar2(10) now table is blank
i need to genrate number every time new value in column like that

01ph000001
01ph000002
01ph000003
when i will go to insert


thanks in advance
Re: auto genrated no [message #259927 is a reply to message #259924] Thu, 16 August 2007 19:53 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
use a sequence
Re: auto genrated no [message #259929 is a reply to message #259927] Thu, 16 August 2007 19:59 Go to previous messageGo to next message
hemu
Messages: 16
Registered: May 2007
Junior Member
no i don't want to use any sequence
on column base
select nvl(max(substr(tt,5,10)),0)+1 from trs;
but it's not working properly
Re: auto genrated no [message #259930 is a reply to message #259924] Thu, 16 August 2007 20:22 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
select nvl(max(substr(tt,5,10)),0)+1 from trs;
but it's not working properly

You showed just a SELECT statement. It does not INSERT anything into trs table.
Without its context, I may just guess what 'not working properly' means (as you did not specify it too).

Do you want to fill the table in one shot or is it supposed to be filled continually?
If the second case, sequence is the only reasonable solution.
Re: auto genrated no [message #259934 is a reply to message #259924] Thu, 16 August 2007 21:05 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>select nvl(max(substr(tt,5,10)),0)+1 from trs;
You intend to build a non-scalable solution.
Re: auto genrated no [message #259950 is a reply to message #259929] Thu, 16 August 2007 23:20 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
If you can't/won't use a sequence then study the two solutions at SQL Snippets: SQL Techniques Tutorials - Serial Numbers .

--
Joe Fuda
SQL Snippets
Re: auto genrated no [message #259968 is a reply to message #259929] Fri, 17 August 2007 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
no i don't want to use any sequence

Why?

Regards
Michel
Re: auto genrated no [message #260034 is a reply to message #259968] Fri, 17 August 2007 04:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If I understand you correctly, you are proposing to generate new sequence numbers in this fashion:

1) Query the table TRS to see what the current MAX value of the sequence is.
2) Add one to that value
3) Use this new value as te new sequence number.

This approach has a couple of flaws:
1) It will be slow - each attempt to insert a new row will require at best a Fast Full index scan - as you are taking the last 5 digits and getting the MAX of them, an Index Range Scan is not an option.

2) It will fail repeatedly and spectaculaly in a multi-user environment whenever two people try to get a record at once. Consider:
Time Event
1 User A Requests a new value
2 User B Requests a new value
3 User A session gets the max value - say 100
4 User B session gets the max value - it gets the same as A
5 User A inserts a new record with this value and commits
6 User B gets an ORA-0001 Unique constraint violated.
Re: auto genrated no [message #260045 is a reply to message #260034] Fri, 17 August 2007 04:34 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Khm, right, but OP never said there was a primary/unique key constraint on this column ./fa/1599/0/
Re: auto genrated no [message #260054 is a reply to message #260045] Fri, 17 August 2007 05:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's a fair point.

I can't see why you'd want to generate a new number each time you inserted a row if you didn't want it to be unique, but perhaps I'm too highly trained Cool

Point 1 still stands - it'll be slow.

I wonder o the OP will ever come back with a reason for not wanting to use a sequence?
Re: auto genrated no [message #260058 is a reply to message #260054] Fri, 17 August 2007 05:34 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I was just kidding; all the reasons you have mentioned are so true.

It looks like yet another "how to insert without INSERT / select without SELECT / generate sequence without sequence / etc." questions. Reason is usually "it was an interview question".

You know, this kind of questions:

Q: What to do if a tiger attacks a mother-in-law?
A: If he was the first to attack, let him defend himself on his own.
Previous Topic: How to insert new record into Table A using data from Table B (merged)
Next Topic: Fast database generation
Goto Forum:
  


Current Time: Thu Dec 08 20:34:32 CST 2016

Total time taken to generate the page: 0.18662 seconds