Re: PL/SQL, UPDATE and SEQUENCES

From: Marcel Claus <Marcel.Claus_at_Informatik.Uni-Oldenburg.DE>
Date: Thu, 07 May 1998 13:25:01 +0100
Message-ID: <6is5ki$315_at_news.Informatik.Uni-Oldenburg.DE>


Emmanuel Baechler 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 statement should work, but it is a bit "dirty". To avoid any complicationtransform it to:

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

ORACLE automatically converts numbers to chars, but it looks better if you say it explicit. Also the "select ... from dual" isn't necessary
'cause you don't need a dummy table in this expression. By the way: Do you have to use chars for the keys? Sequences always generate numbers, so it is easier to use numbers as primary keys. And remember that numbers genarted by sequences are not continguos. They have gaps.

> 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?
>

Maybe the missing to_char is the problem ? The statement I wrote aboveworks in PL/SQL.

> 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?

Uh, yes, no, what? The same as with the statement above: This looks like a really dirty hack!
I know that you can use the ROWTYPE and that there are a hundred ways to code something so that nobody can read it anymore and it is really fast in execution... (There is also a contest for C-programmers ...)
But I would code it that way:

DECLARE
  cursor c1 is

      select rowid from ENTRIES where THIRD_PARTY is not null;   counter number;
  the_row varchar2(20);
BEGIN
  open c1;
  loop
    fetch c1 into the_row;
    exit when c1%notfound;
    SELECT No_piece.NEXTVAL into counter FROM DUAL;     update ENTRIES set REF_NO = '1',

            PIECE_NUMBER:= 'PCE' || to_char(counter) where
            rowid = the_row;

  end loop;
  close c1;
  commit;
END; I haven't compiled it, but I think that should do it and I also think it is very slow in execution.

Marcel Received on Thu May 07 1998 - 14:25:01 CEST

Original text of this message