Home » SQL & PL/SQL » SQL & PL/SQL » sequence in procedure
sequence in procedure [message #292684] Wed, 09 January 2008 11:03 Go to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
i have to create a sequence which is to be initiated from 1 each time the procedure is executed. And in side the procedure there is a loop in each loop the sequence to be increamented.

how to do it?
Re: sequence in procedure [message #292685 is a reply to message #292684] Wed, 09 January 2008 11:10 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Why do you require a sequence as opposed to just using a local PL/SQL variable inside the procedure?

If you insist on using a sequence then you'll have to (ab)use EXECUTE IMMEDIATE
Re: sequence in procedure [message #292686 is a reply to message #292684] Wed, 09 January 2008 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select myseq.nextval into myvar from dual;

Starting with 11g, we can do "myvar := myseq.nextval;".

Regards
Michel
Re: sequence in procedure [message #292922 is a reply to message #292684] Thu, 10 January 2008 03:10 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
hi
my intension here is can i initiate the sequence inside the procedure? so that each time the procedure is executed the cursor ll start from its initial value?
Re: sequence in procedure [message #292924 is a reply to message #292922] Thu, 10 January 2008 03:13 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the need to do this?
Why don't use a SQL function (rownum or the like) a PL/SQL variable, for instance?

Regards
Michel
Previous Topic: exception DUP_VAL_ON_INDEX
Next Topic: A count query problem.
Goto Forum:
  


Current Time: Thu Dec 08 08:36:06 CST 2016

Total time taken to generate the page: 0.14341 seconds