Function for sequence 2

[Pushkin]

Can anybody help me to generate a sequence.nextval in a function, I have no idea how I can do this.

Thanks in advance.

 

[sem]

What is your specific problem?

Just declare some numeric variable and select a nextval to it:

declare
myVar number;
begin

select sequence.nextval into myVar from dual;

end;

 

[Pushkin]

I'm trying to create a function that generates a sequence.
This sequence has to fill a id-field.
I can't generate it in the id-field because I need to use it in Oracle Webdb.

 

[Bimalaggarwal]

First you should create a sequence using a sequence generator
(CREATE SEQUENCE NEW_ID.....)

then you may create a functionas following:

create or replace function get_new_id
return pls_integer
as
l_new_id pls_integer;
begin
select NEW_ID.nextval into l_new_id from dual;

return l_new_id;
end;

then use this function to assign the new id to your id field (say ,l_id_field) as:

l_id_field := get_new_id;

 

[Pushkin]

Thanks for the help.

There is only one problem, in webdb after insert the sequence increases by 2 instead of 1.

Does anybody know how to handle this?

Thanks in advance,

Pushkin

 

[sem]

Check your code. You probably call your function twice. You may also get some gaps due to rollbacked transactions.

 

[Bimalaggarwal]

There is a parameter 'Increment By' in the Sequence script.
Check it out and set it to 1.

 

[Pushkin]

I used the above code to create the function and my sequence is increased by 1.
I only return one times the value of the sequence and it still increases by 2.

Can anybody help me?

Greetz

 

[stefanhei]

Create the sequence with NOCACHE and ORDER Option. Maybe this will help

Stefan

 

[Pushkin]

Thanks a lot to you all.