Xref: alice comp.databases.oracle.tools:12535
Path: alice!news-feed.fnsi.net!news.idt.net!netnews.com!news.maxwell.syr.edu!nntp2.dejanews.com!nnrp1.dejanews.com!not-for-mail
From: masyedkasim@hotmail.com
Newsgroups: comp.databases.oracle.tools
Subject: Solution for your Updation Problem
Date: Mon, 11 May 1998 07:11:35 GMT
Organization: Deja News - The Leader in Internet Discussion
Lines: 89
Message-ID: <6j68b7$fa5$1@nnrp1.dejanews.com>
References: <35515CF9.73CE@hospvd.ch>
X-Article-Creation-Date: Mon May 11 07:11:35 1998 GMT
X-Http-User-Agent: Mozilla/2.0 (compatible; MSIE 3.01; Windows 95)
X-Http-Proxy: nms (MSProxy/1.0) for 192.168.104.83



In article <35515CF9.73CE@hospvd.ch>,
  ebaechle@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