Using the sample function in a complex query in PLSQL

Question

I would like to select a random sample of the rows returned by my query. I have read the answer How to get records randomly from the oracle database? but I cannot get the dbms_random.random to work, so I tried with the sample function.

But I am getting an error when I request a sample of my values. A reproducible example would be: The example queries provided are just for reproducability, they have nothing to do with my actual usecase (as in I do not want to select 7 rows or something similar)

select * from (select 1,2,3,4,5,6,7 from dual) sample(0.1)
-- doesn't work
select * from (select 1,2,3,4,5,6,7 from dual)t sample(0.1)
-- doesn't work either

What am I doing wrong here? My goal is to select a random sample of the results of the subquery but I do not have privileges to use dbms_random.value.

Sample clause is for real tables only: https://stackoverflow.com/questions/62866089/randomly-choosing-a-row/62866447#62866447 — Sayan Malakshinov, Jul 14 '20 at 09:42
Are you sure that you have not privileges on `dbms_random.value`? What does return `select dbms_random.value() v from dual` ? — Sayan Malakshinov, Jul 14 '20 at 09:51
Also you can use `((1+mod(ora_hash(sys_guid()),1000))/1000)` instead of dbms_random.value(). Example: `select ((1+mod(ora_hash(sys_guid()),1000))/1000) rand from dual;` — Sayan Malakshinov, Jul 14 '20 at 10:13

score 1 · Accepted Answer · answered Jul 14 '20 at 07:50

1

The basic function VALUE from DBMS_RANDOM gets a random number, greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal (38-digit precision).

Alternatively, when specify low and high limits, it retrieves a random number between. In your case, you can get a random integer value between 1 and 7 using

select round(dbms_random.value(1,7),0) from dual ;

You can get those values even from your table

select round(dbms_random.value(a.min_value,a.max_value),0) from my_table a ;

As you did not explain what exactly you want to select from your table, I can just provide you with a generic answer.

answered Jul 14 '20 at 07:50

Roberto Hernandez

8,231
3
14
43

My problem with dbms_random.value is that when I try to use it I get : "ORA-0094: DBMS_RANDOM.VALUE invalid identifier" – User2321 Jul 14 '20 at 07:55
apparently you don't have privileges over DBMS_RANDOM. Your DBA should be granting EXECUTE privilege to your user. – Roberto Hernandez Jul 14 '20 at 07:58
Yeah this would take some time to change that's why I am trying to work around it. – User2321 Jul 14 '20 at 07:59
Turns out I managed to get the rights so I guess this is the correct answer! – User2321 Jul 21 '20 at 13:09

score 1 · Answer 2 · answered Jul 14 '20 at 07:52

1

Oracle does not support sampling of derived tables or views that are not key-preserving (link).

Also, note that if you want to create 1 row for each of the first 7 integers in Oracle:

select level
from dual
connect by level <= 7

answered Jul 14 '20 at 07:52

vc 74

37,131
7
73
89

Thank you! How can I make my query key-preserving? I am joining two tables in it, I tried selecting the keys of the tables but this did not work either. – User2321 Jul 14 '20 at 08:09
@User2321 You could use a temporary table: `create table x as ...` then select from x using `sample` and drop x – vc 74 Jul 14 '20 at 08:29

Using the sample function in a complex query in PLSQL

2 Answers2