PL/SQL, UPDATE and SEQUENCES

[Quoted] [Quoted] I am facing a problem that I have not been able to solve with my [Quoted] local ressources. I am in charge of several information systems based on ORACLE 7.3.3, running on a Data General 3600 with DG/UX.

I have a case where I must complete some accounting entries with the following rule: if the entry contains a third party, then include a unique piece number into the entry. This is done after the entries have been received from another system.

[Quoted] As the number must be unique, I try to use a sequence. I did finally write the code below (general principle):

UPDATE ENTRIES
SET (REF_NO, PIECE_NUMBER)= (SELECT '1', 'PCE' || No_piece.NEXTVAL FROM DUAL)
WHERE THIRD_PARTY is not null;

[Quoted] This request is inserted into a PL/SQL procedure which is compiled without any problem by my developpment tool (SQL Navigator). It does however not run, and I get the following runtime error message:

ORA-02287 Sequence number not allowed here

I must admit that I have difficulties understanding why. Does anybody know how this request should be written?

[Quoted] Note that I get exactly the same error message even if I do not concatenate
the sequence value with a prefix.

[Quoted] I am thinking of an alternative solution. But, as it is much less elegant (and IMO, much less efficient), I would like to keep if for the case where there's no way to correct the first one:

[Quoted] CURSOR Occ_cur IS Select * from ENTRIES where THIRD_PARTY is not null

       for update of REF_NO, PIECE_NUMBER NOWAIT;
Occ_row     	Occ_cur%ROWTYPE;
Counter 	NUMBER;

...

  OPEN Occ_cur;
  FETCH Occ_cur INTO Occ_row;
  WHILE Occ_Cur%FOUND LOOP
    SELECT No_piece.NEXTVAL into Counter FROM DUAL;     Occ_row.REF_NO:='1';
    Occ_row.PIECE_NUMBER:= 'PCE' || to_char(Counter,99999990);     FETCH Occ_cur INTO Occ_row;
  END LOOP;
  Commit;
  CLOSE Occ_cur;

As I did find very little information about table update through cursors, I am having a serious doubt about the validity of the above schema. I interpret a ROWTYPE variable as a buffer used to examine an occurence Is this interpretation true?

[Quoted] If it is true, there should be an order intended to write the ROWTYPE back into the table. Am I right?

Emmanuel Baechler
Les Hospices Cantonaux
Office Informatique
Bugnon 21
1005 Lausanne Received on Thu May 07 1998 - 09:04:25 CEST