Home » SQL & PL/SQL » SQL & PL/SQL » Sequence number to be entered by a user (Oracle 12c, O/S: Windows 10 pro)
Sequence number to be entered by a user [message #685207] Fri, 05 November 2021 20:48 Go to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
I have a requirement where in a user is insisting that whatever number (which will act as an input parameter) a user enters (in a reasonable range of 2500 - 4000) will or should be the sequence number.

For example:

At the sql prompt the user should be prompted with:

Enter a sequence number: 2501
so my code has to be something like below:
If the sequence number was already generated with the above number then it should be 2502(nextval)
else if the sequence number 2501 was not generated then that should be the sequence number
I told the user that since the sequence number has NEXTVAL as the pseudo-column there is no need for the user to force enter a sequence number that they want. Instead NEXTVAL will take care of it and generate the next sequence number in order (it could be between 2500-400 or even greater than that).

But the user is not agreeing and wants me to generate a sequence number that the user enters based on the above validations.

In the meantime I followed the code posted on Nimish's blog and tested it out too. I don't believe it is meeting the requirement.
https://nimishgarg.blogspot.com/2014/09/setting-sequence-value-to-specific.html
Is there a way to do that? If so, how? Please help
Re: Sequence number to be entered by a user [message #685208 is a reply to message #685207] Sat, 06 November 2021 07:28 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Your requirements aren't clear:

If the sequence number was already generated with the above number then it should be 2502(nextval)
else if the sequence number 2501 was not generated then that should be the sequence number
What if 2502 was already generated too? Do you mean keep checking 2503, 2504, etc. until reaching 4000? And then what?

SY.
Re: Sequence number to be entered by a user [message #685209 is a reply to message #685208] Sat, 06 November 2021 09:26 Go to previous message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
Solomon Yakobson wrote on Sat, 06 November 2021 07:28
Your requirements aren't clear:

If the sequence number was already generated with the above number then it should be 2502(nextval)
else if the sequence number 2501 was not generated then that should be the sequence number
What if 2502 was already generated too? Do you mean keep checking 2503, 2504, etc. until reaching 4000? And then what?

SY.
You have raised a very good point. After I posed the question here, your thoughts did come to my mind. Seeing the way how demanding this customer is, she could come back with that request.

Is there a way to know if a sequence number has been generated or not (using Sequence_name = object created)? I do not think so (using the SEQUENCE object created), but a word or two from you would help me.
Previous Topic: Select value from multiple dates
Next Topic: Dates integrity
Goto Forum:
  


Current Time: Thu Mar 28 15:37:45 CDT 2024