Sequence->variable->insert problem with PL/SQL

I have a temporary table with four cols (lopnr number(4),embnr varchar2(10),embkvn number(7),embembkvn number(7)) and a sequence with values between 0 and 9999.

I'm writing a PL/SQL-package where I need to assign the lopnr as a ID for records to be unique for the each instance of the package (to allow concurrent updates on the same table without interference).

That is the temporary table should be filled with several embnr in the package, and all these should have the same lopnr (unique for each package instance).

To solve this I use a variable, lopnummer_ (number(4)), that gets it's value from the sequence by a select similar to the following: SELECT <sequencename>.nextval from dual;

I've checked so that lopnummer_ gets it's number from the sequence as it should, but the problem is that the insert doesn't update the table when I use lopnummer_

(I'm calling the package from Visual Basic 4, and checked the variable lopnummer_ by adding a insert into a debugtable. The value displayed there was concatinated with some other debugtext into a varchar-field, so I've not checked so that the variable-value is numeric, but since I've declared it that way I can't see how and why it would have changed...)

INSERT INTO <tablename> (lopnummer,embnr,embkvn,embembkvn) VALUES(1,'EMB122',2,0);
and
INSERT INTO <tablename> (lopnummer,embnr,embkvn,embembkvn) VALUES(<sequencename>.nextval,'EMB122',2,0); works but not
INSERT INTO <tablename> (lopnummer,embnr,embkvn,embembkvn) VALUES(lopnummer_,'EMB122',2,0);

No exception is triggered when the table isn't filled (by using lopnummer_), the package just continues as if nothing strange happened.

I'm pretty new to both Oracle and PL/SQL, but I can't see what I'm doing wrong here...

Any suggestions would be appreciated.

/Tony Received on Fri Dec 11 1998 - 21:22:26 CET