Re: PL/SQL, UPDATE and SEQUENCES

On Thu, 07 May 1998 09:04:25 +0200, Emmanuel Baechler <ebaechle_at_hospvd.ch> wrote:

[...]

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

Hi Emmanuel,

there are some restrictions on how sequences may be used. One of these is that you cannot use a sequence in a subquery of a delete, select or update statement. You can find details about other restrictions in the Oracle7 Server SQL Reference, have a look at the section about pseudocolumns. But you could code it this way:

UPDATE table

   SET col1 = <expr>,
       col2 = <constant>,
       col3 = your_sequence.nextval

[...]

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

Agree ;-)

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

No, not quite. It's actually only a local variable of a record type into which you are fetching from your cursor, one complete record at a time. Modifying this variable does not modify your table.

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

Yes, you should issue an update statement like this:

UPDATE entries

   SET ref_no = '1',

       piece_number = counter
 WHERE CURRENT OF occ_cur;

This will update using the rowid from the record you fetched which is the most effecient way to access a specific row.

Note that you don't need to retrieve any real data from your table, it would also work if the cursor was declared with something like:

SELECT 'UPDATE_ME'
  FROM ...
   FOR UPDATE; because with the FOR UPDATE clause, Oracle will retrieve the rowid (and lock the record).

But as you already said, this whole approach is very inefficient because it will issue multiple update statements, i.e. one for each record, against the database where you could achieve the same effect with one statement updating all qualifying records.

BTW, if you declare a cursor with FOR UPDATE NOWAIT, it would be a good idea to have an exception handler for ORA-00054 which will be raised if a lock on a row cannot be obtained because it's already locked in another session.

HTH,
Peter

-- 
Peter Schneider
peter.schneider_at_okay.net