How to print random from table in a procedure oracle?

November 26, 2021

Let’s say I have a table child and I want in a procedure to select random from 1 to 5, random values from the table and print them. How can i do it?

create table child(name varchar2(20), age number);

insert into child(name, age) values('A',5);
insert into child(name, age) values('B',12);
insert into child(name, age) values('C',7);
insert into child(name, age) values('D',4);

create or replace procedure random_child
as
 l_name child.name%type;
 l_age child.age%type;
begin
  for i in(select dbms_random.value(1,5) 
           from (select name from child sample(50)))
loop
 DBMS_OUTPUT.put_line(i.name);
end loop;
end;

It gives me a PLS-00302: name must be declared

>Solution :

There’s no i.name there; alias is missing at the end of line #6:

SQL> create or replace procedure random_child
  2  as
  3   l_name child.name%type;
  4   l_age child.age%type;
  5  begin
  6    for i in(select dbms_random.value(1,5)  as name  --> here
  7             from (select name from child sample(50)))
  8  loop
  9   DBMS_OUTPUT.put_line(i.name);
 10  end loop;
 11  end;
 12  /

Procedure created.

SQL> exec random_child
1,30966411991963041689918865935551009464
1,13993832387089615287177388489291237644
3,85292920191145794430114472793297022632

PL/SQL procedure successfully completed.

SQL>