Solution for your Updation Problem

In article <35515CF9.73CE_at_hospvd.ch>,   ebaechle_at_hospvd.ch wrote:
>
> Hi,
>
> I am facing a problem that I have not been able to solve with my
> 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.
>
> 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;
>
> 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?
>
> Note that I get exactly the same error message even if I do not
> concatenate
> the sequence value with a prefix.
>
> 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:
>
> 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?
>
> 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
>

Hi,

Use the following update statement to fix up your problem.

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

By
Kasim.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Mon May 11 1998 - 09:11:35 CEST