Help - Search - Members - Calendar
Full Version: incremental field in stored procedures
Oracle DBA Forums > Oracle > Oracle Forum
sudhasrig
Hi All,

I have a requirement in which there is one incremental field based on city.


Based on city the SR No needs to be incremented. We have achieved this by using Rank function and its working fine.



Now the problem is if we run this procedure for the next time the SR No to be incremented.

For Eg: If we run the procedure for the 1íst following is the output.

SR No City
1 Chennai

1 Chennai

2 Mumbai

3 Hyderabad

3 Hyderabad

If we run the procedure for 2índ time the output should be as given below.



SR No City
4 Chennai

4 Chennai

4 Chennai

5 Hyderabad

6 Yamuna nagar

6 Yamuna nagar



If we run the procedure for the next time the SR No should start from 7.



Please help
burleson

>> If we run the procedure for the next time the SR No should start from 7.

Well, you will have to "remember" that the SQL is being re-run . . .

You might store a flag in a PL/SQL variable, maybe use a sequence, who knows?
sudhasrig
I have a incremental field in a procedure, which needs to be incremented for each time when we execute the procedure.

for example: if you run the procedure for the first time

SRNO
1
2
3
4

if you run the procedure for the second time

SRNO
5
6
7
8
harryp
QUOTE (sudhasrig @ Mar 9 2010, 02:56 PM) *
I have a incremental field in a procedure, which needs to be incremented for each time when we execute the procedure.

for example: if you run the procedure for the first time

SRNO
1
2
3
4

if you run the procedure for the second time

SRNO
5
6
7
8


Agree, use a sequence or store your SRNO in a temporary table so each time you run the procedure, get max SRNO+1 from temporary table right at the beginning before you do any processing.
sudhasrig
we need to implement this with in the same procedure with out sequence.
It is not depends on the user ,it depends on the procedure.
each time it is running the SRNO should be increment based on previous SRNO.



burleson
>> we need to implement this with in the same procedure with out sequence.

You didn't say that before.

If you are not allowed to do it the right way, I would quit and find a better job . . .


**************************************************
>> each time it is running the SRNO should be increment based on previous SRNO.

Sounds easy, programming 101, what's the problem?

You don't know how to increment a counter in PL/SQL?

CODE
SRNO := SRNO + 1;


There you go, problem solved . . .
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.