sequence.currval

[Pushkin]

I've got an application with a sequence, trigger (before insert that uses this sequence) and a function that calls the currval of this sequence.

When I run the app Oracle gives this error:

ORA-08002: sequence TEST_SEQ2.CURRVAL is not yet defined in this session

So, I removed the function and ran the app, inserted data and closed the app.
Then I added the function again.
When I then run the app everything works properly.

Does anybody know how I can define this currval before opening the app.

Thanks

 

[sem]

You may use .currval only AFTER .nextval was called in this session.

 

[Pushkin]

Do you know how I could do this.

I've absolutely no idea

 

[sem]

Do you call your function before insert? In this case you have no chance to know the value that will be inserted, unless you select .currval in trigger (too bad idea, because this trigger fails if not .nextval selected before). If you need to know the value to be inserted before inserting, you may retrieve this value in function before inserting and then insert IT (not making assignment in trigger if value is provided). Though I need more details about your problem.

 

[Pushkin]

I created this sequence.

create sequence test_seq2
increment by 1
start with 1
nomaxvalue
minvalue 1
nocache
order

Trigger

create or replace trigger t_test_id
before insert
on test
referencing old as old new as new
for each row
begin
select test_seq2.nextval into :new.id from dual;
end;

Function

create or replace function get_new_id
return pls_integer
as
l_new_id pls_integer;
begin
select test_seq2.currval into l_new_id from dual;
return l_new_id;
end;

Now when I open the app the currval must be filled out automaticly even before insert, so you know the current value of your id and not the next one.

The trigger is used to fill out the nextval id before insert

Hope this helps a bit

 

[sem]

I see, but the purpose of get_new_id is a bit unclear. If you need to know a value inserted by trigger BEFORE inserting - just INSERT IT. You can not reserve current sequence value for future use. How do you plan to work if somebody else does the same ? .

P.S.
You may write to me in Russian to sam@eximb.com

 

[Pushkin]

The value before insert must be automaticly filled out in the form.
So I need to use this function.

I work in WebDB and in this form you can't see the nextval of a sequence when you use a trigger.
So I created a function that shows the current value.
So people see the id they need and don't have to fill this out.

 

[sem]

So you may follow my advice:

create or replace function get_new_id
return pls_integer
as
l_new_id pls_integer;
begin
select test_seq2.NEXTVAL into l_new_id from dual;
return l_new_id;
end;


create or replace trigger t_test_id
before insert
on test
referencing old as old new as new
for each row
begin

-- Do not change already provided value
-- use retrieved by get_new_id function

if :new.id is null then
select test_seq2.nextval into :new.id from dual;
end if;
end;

 

[Pushkin]

What do you mean with:

-- Do not change already provided value
-- use retrieved by get_new_id function

 

[sem]

You may not only SHOW the value, returned by get_new_id (my variant with nextval) function, but also INSERT it.