Re: PL/SQL, UPDATE and SEQUENCES
Date: Thu, 07 May 1998 11:52:09 GMT
Message-ID: <35519f80.2478714_at_newshost.us.oracle.com>
Emmanuel,
UPDATE entries SET ref_no = 1,
piece_number = 'PCE' || TO_CHAR(no_piece.NEXTVAL)
WHERE third_party IS NOT NULL;
...should do the trick.
On Thu, 07 May 1998 09:04:25 +0200, Emmanuel Baechler <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
Thanks!
Joel
Joel R. Kallman Oracle Government, Education, & Health
Columbus, OH http://govt.us.oracle.com jkallman_at_us.oracle.com http://www.oracle.com
The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Received on Thu May 07 1998 - 13:52:09 CEST