ORA-02014- How do I update a randomly selected row from a table?

Question

I'm trying to randomly select a card from a table of cards with columns c_value and c_suit using a procedure. After selecting it, the procedure should update that entry's taken field to be 'Y'.

create or replace procedure j_prc_sel_card(p_value OUT number,
                                           p_suit OUT number)
AS

   CURSOR CUR_GET_RAND_CARD IS SELECT c_value, 
                                      c_suit
                                 FROM (SELECT c_value, 
                                              c_suit, 
                                              taken
                                         FROM jackson_card
                                     ORDER BY dbms_random.value)
                                WHERE rownum = 1
                        FOR UPDATE OF taken;

BEGIN

  OPEN CUR_GET_RAND_CARD;
  FETCH CUR_GET_RAND_CARD into p_value, p_suit;

  UPDATE jackson_card 
     SET taken = 'Y' 
   WHERE c_value = p_value 
     AND c_suit = p_suit;

  CLOSE CUR_GET_RAND_CARD;

END;

Then I am trying to get the selected card and output what it is as a start. With this:

SET serveroutput on;

DECLARE v_value number;
        v_suit number;

BEGIN

  j_prc_sel_card(p_value => v_value,p_suit => v_suit);
  DBMS_OUTPUT.PUT_LINE(v_value);
  DBMS_OUTPUT.PUT_LINE(v_suit);

END;
/

However i got the error stated in the title and it seems my way of selecting a random card is stopping me from doing an update. Thanks in advance!

Answer 1

Here is a different take on the scenario (I did also address your immediate problem in a different answer).

Given that we really are building a card-dealing program (as opposed to working with a test case for a business scenario) I didn't like the TAKEN column. Updating a table column to mark a transitory state seems wrong. What happens when we want to play another game?

The following solution resolves this by populating an array with all the cards in a random order upfront (the shuffle). The cards are dealt by simply taking the next entry off the stack. The package offers a choice of approach for running out of cards: either throw a user-defined exception or just cycle through the deck again.

create or replace package card_deck is

    no_more_cards exception;
    pragma exception_init(no_more_cards, -20000);

    procedure shuffle;

    function deal_one 
        ( p_yn_continuous in varchar2 := 'N')
        return cards%rowtype;

end card_deck;
/

create or replace package body card_deck is

    type deck_t is table of cards%rowtype;
    the_deck deck_t;

    card_counter pls_integer;

    procedure shuffle is
    begin
        dbms_random.seed (to_number(to_char(sysdate, 'sssss')));
        select *
        bulk collect into the_deck
        from cards
        order by dbms_random.value;
        card_counter := 0;
    end shuffle;

    function deal_one
        ( p_yn_continuous in varchar2 := 'N')
        return cards%rowtype
    is
    begin
        card_counter := card_counter + 1;
        if card_counter > the_deck.count() 
        then
            if p_yn_continuous = 'N'
            then
                raise no_more_cards;
            else
                card_counter := 1;
            end if;
        end if;
        return the_deck(card_counter);
    end deal_one;

end card_deck;
/

Here it is in action. Don't use an open LOOP if you set the continuous dealing mode to Y.

SQL> set serveroutput on
SQL>
SQL> declare
  2      my_card cards%rowtype;
  3  begin
  4      card_deck.shuffle;
  5      loop
  6          my_card := card_deck.deal_one;
  7          dbms_output.put_line ('my card is '||my_card.c_suit||my_card.c_value);
  8      end loop;
  9  exception
 10      when card_deck.no_more_cards then
 11          dbms_output.put_line('no more cards!');
 12  end;
 13  /
my card is HA
my card is H7
my card is DJ
my card is CQ
my card is D9
my card is SK
no more cards!

PL/SQL procedure successfully completed.

SQL>

You may think I'm not dealing with a full deck. You wouldn't be the first to think that ;)

Answer 2

You are using an explicit cursor already, so you don't need the ROWNUM = 1 filter. Try this:

create or replace procedure j_prc_sel_card(p_value OUT number,
                                           p_suit OUT number)
AS

   CURSOR CUR_GET_RAND_CARD IS 
         SELECT c_value, 
                c_suit, 
                taken
         FROM jackson_card
         WHERE taken != 'Y'
         ORDER BY dbms_random.value
         FOR UPDATE OF taken;

BEGIN

  OPEN CUR_GET_RAND_CARD;
  FETCH CUR_GET_RAND_CARD into p_value, p_suit;

  UPDATE jackson_card 
     SET taken = 'Y' 
   WHERE CURRENT OF cur_get_rand_card;

  CLOSE CUR_GET_RAND_CARD;

END;

Note the use of WHERE CURRENT OF. This is the most efficient way of locating a row when we are using the FOR UPDATE CLAUSE. Without the use of the NOWAIT clause the cursor will hang if the chosen card is locked by another session. An unlikely scenario perhaps but one worth considering when you move beyond card games and into real scenarios.

Also, remember that for a truly random shuffle you need to call DBMS_RANDOM.SEED() at the start of proceedings.