OraFAQ Forum: SQL & PL/SQL » sequence in procedure

Home » SQL & PL/SQL » SQL & PL/SQL » sequence in procedure

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sequence in procedure [message #292684]

Wed, 09 January 2008 11:03

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?

Report message to a moderator

Re: sequence in procedure [message #292685 is a reply to message #292684]

Wed, 09 January 2008 11:10

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: sequence in procedure [message #292686 is a reply to message #292684]

Wed, 09 January 2008 11:28

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

select myseq.nextval into myvar from dual;

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

Regards
Michel

Report message to a moderator

Re: sequence in procedure [message #292922 is a reply to message #292684]

Thu, 10 January 2008 03:10

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?

Report message to a moderator

Re: sequence in procedure [message #292924 is a reply to message #292922]

Thu, 10 January 2008 03:13

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	exception DUP_VAL_ON_INDEX
Next Topic:	A count query problem.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 17:14:23 CST 2024