Make Oracle SQL Developer execute the SELECT statement every time in a loop

Question

I need to create a table with random values from another table. For that, I tried using a LOOP that collects random FIRST_NAME and LAST_NAME from the other table and puts them in my table.

But using a LOOP does not work as the SELECT statement does not execute more than once.

BEGIN
LOOP
v_counter:=v_counter+1;
SELECT fname INTO v_fname FROM users SAMPLE (5) WHERE some_condition AND ROWNUM=1;
SELECT lname INTO v_lname FROM users SAMPLE (5) WHERE some_condition AND ROWNUM=1;
DBMS_OUTPUT.PUT_LINE(v_fname2);
EXIT WHEN (v_counter > 50);
END LOOP;
END;

I get the same name printed 50 times.

Answer 1

The code in OP:

BEGIN
LOOP
v_counter:=v_counter+1;
SELECT fname INTO v_fname FROM users SAMPLE (5) WHERE some_condition AND ROWNUM=1;  --Line_no a
SELECT lname INTO v_lname FROM users SAMPLE (5) WHERE some_condition AND ROWNUM=1;  --Line_no b
DBMS_OUTPUT.PUT_LINE(v_fname2); --Line_no c
EXIT WHEN (v_counter > 50);
END LOOP;
END;

Issues:

If we see, Line_No a is selecting the data in variable v_fname and Line_no c which is printing is printing another variable v_fname2. I dont understand, a variable which you are not changing the value, why are you expecting to see the result in print changed? You see, v_fname and v_fname2 are TWO DIFFERENT VARIABLES. You are assigning the values to v_fname and not v_fname2, while you are printing v_fname2 and not v_fname.

Answer 2

Rather use an implicit FOR LOOP:

BEGIN
  FOR x in (
     SELECT a.fname, b.lname FROM users SAMPLE (5) a, users SAMPLE (5) b  WHERE some_condition AND ROWNUM < 51
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(x.fname||' - '||x.lname);
  END LOOP;
END;