Syntax errors while trying to use a select statement as the condition for a while loop in SQL

I have a simple loop I wrote to check if a random id is already in the table, else regenerate it:

random_id := dbms_random.value(1,100);
WHILE (SELECT COUNT(*) FROM ANIMAL WHERE ANIMAL_ID = random_id) <> 0 
LOOP
    random_id := dbms_random.value(1,100);
END LOOP;

Running the code above gives the error:

Error(40,12): PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:     ( - + case mod new not null <an identifier>    <a double-quoted delimited-identifier> <a bind variable>    continue avg count current exists max min prior sql stddev    sum variance execute forall merge time timestamp interval    date <a string literal with character set specification>    <a number> <a single-quoted SQL string> pipe    <an alternatively-quoted string literal with character set specification>    <an alternat

and

Error(40,67): PLS-00103: Encountered the symbol ")" when expecting one of the following:     . ( * @ % & - + ; / at for mod remainder rem    <an exponent (**)> and or group having intersect minus order    start union where connect || multiset 

As far as I can tell, no error in the editor.

Answer

The better approach is to collect all the id’s into a PL/SQL collection, so that you only make one context switch from PL/SQL to SQL (and back). Something like this; note that I use empno from table emp in schema scott for testing, since I don’t have your table. The employee numbers are all between 7350 and 7950; I will pretend that I am looking for a number in that range that is not already assigned to an existing employee. (Similar to your attempt, if I got it right.)

The code begins by defining a local collection type, as a table of numbers (to hold the id’s), and an instance of this local collection type. Then a single select using the bulk collect into clause collects the existing id’s (employee numbers) into this collection instance, so the rest of the code is pure PL/SQL.

Also, please pay attention to what I told you in a comment: without rounding or truncating, dbms_random.value() produces non-integers in general, and that is very unlikely to be what you intended.

declare
  type id_list_t is table of number;
  id_list id_list_t;
  random_id number;
begin
  select empno bulk collect into id_list from scott.emp;
  random_id := round(dbms_random.value(7350, 7950));
  while random_id member of id_list loop
    random_id := round(dbms_random.value(7350, 7950));
  end loop;
  dbms_output.put_line(random_id);
end;
/