Home » Developer & Programmer » Forms » Generating Sequence with conditions
Generating Sequence with conditions [message #653483] Thu, 07 July 2016 02:13 Go to next message
udaykuditipudi
Messages: 8
Registered: July 2016
Location: hyderabad
Junior Member
Hii
---------------
IN ORACLE FORMS
---------------
My requirement is
In front end FORM if i add a data in lines it should generate line numbers a sequence as
1
2
3
4
After saving the form
if i re-query the form and open it
the line numbers should be
1
2
3
4
So here i have the main requirement.
After saving and querying then
IF I DON'T NEED THE LINE 2 IF I DELETE IT AND I WANT TO ADD A NEW LINE TO THAT FORM IT SHOULD GENERATE THE LINE NO AS
1
3
4
5


[Updated on: Thu, 07 July 2016 02:23]

Report message to a moderator

Re: Generating Sequence with conditions [message #653484 is a reply to message #653483] Thu, 07 July 2016 02:26 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

I am not sure that I understand your question. However, I think you are generating rows with a unique line number, and do not wish to re-use line numbers. This sounds like a usage case for a session sequence,
CREATE SEQUENCE S1 SESSION;
described here, http://docs.oracle.com/database/121/SQLRF/statements_6017.htm#sthref5445
Re: Generating Sequence with conditions [message #653485 is a reply to message #653483] Thu, 07 July 2016 02:27 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I see that you have updated your post to say that you are using Oracle Forms. I'll move your topic to the Forms forum.
Re: Generating Sequence with conditions [message #653488 is a reply to message #653485] Thu, 07 July 2016 02:56 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Please do not send me private messages unless you are looking for consulting services.

Re: Generating Sequence with conditions [message #653492 is a reply to message #653488] Thu, 07 July 2016 03:17 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
@John - I assume session sequences are new in 12, in which case you should probably point that out when suggesting it, most people won't be on 12 yet.
Re: Generating Sequence with conditions [message #653496 is a reply to message #653492] Fri, 08 July 2016 01:28 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This looks like a "MAX + 1" situation. As several users can do it simultaneously, beware of duplicate values issue. Consider creating an autonomous transaction function which would take care of it.

Anyway: a sequence looks like the most appropriate solution. Just note that you can't "reuse" already fetched value if you, for example, exit a form and return later to finish the job. So, if you don't care about possible gaps, I'd go with the sequence as you don't have to program anything.
Re: Generating Sequence with conditions [message #653501 is a reply to message #653496] Fri, 08 July 2016 03:27 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
How would an autonomous transaction help?
Re: Generating Sequence with conditions [message #653509 is a reply to message #653501] Fri, 08 July 2016 15:13 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's what I meant: I was thinking of another table which would contain MAX number used so far. The next value would then be "stored value + 1", and - once it is fetched - the table would be updated to reflect that new MAX value. For example, if it was 5, the new value would be 6, and 6 would be then stored in that table.

In order to prevent two (or more) users to select the same value, a function would calculate the next value, store it into a table, COMMIT, and return that value to the caller. Something like this:
SQL> CREATE TABLE test_1 (id NUMBER);

Table created.

SQL>
SQL> CREATE OR REPLACE FUNCTION f_max_1
  2     RETURN NUMBER
  3  AS
  4     PRAGMA AUTONOMOUS_TRANSACTION;
  5     l_next   NUMBER;
  6  BEGIN
  7     UPDATE test_1
  8        SET id = NVL (id, 0) + 1;
  9
 10     IF SQL%NOTFOUND
 11     THEN
 12        INSERT INTO test_1 (id)
 13             VALUES (1);
 14     END IF;
 15
 16     COMMIT;
 17
 18     SELECT id INTO l_next FROM test_1;
 19
 20     RETURN l_next;
 21  END;
 22  /

Function created.

SQL>
SQL> SELECT f_max_1 FROM DUAL;

   F_MAX_1
----------
         1

SQL> SELECT f_max_1 FROM DUAL;

   F_MAX_1
----------
         2

SQL> SELECT f_max_1 FROM DUAL;

   F_MAX_1
----------
         3

SQL>
Re: Generating Sequence with conditions [message #653556 is a reply to message #653509] Mon, 11 July 2016 03:27 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You might as well use a sequence. As soon as a user calls that function and then doesn't save the main insert it'll stop reflecting the data in the main table.
The only way max works is if you take a lock that prevents more than one user at a time from inserting into the main table.
Re: Generating Sequence with conditions [message #653559 is a reply to message #653556] Mon, 11 July 2016 05:24 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes. That's why I said (actually, agreed with what John previously had said)

LF

Anyway: a sequence looks like the most appropriate solution.
Previous Topic: Forms 6i
Next Topic: Have one block update another
Goto Forum:
  


Current Time: Fri Mar 29 02:09:05 CDT 2024