DBMS_RANDOM doesn't seem to work

Question

I am still fairly new to Oracle SQL here, but I have scoured the internet to be able to just randomly pull a row from a table and saw a code like this.

firstNameGen = connection.prepareStatement(
    "SELECT firstName "
        +"FROM (SELECT firstName "
        +"FROM firstNames "
        +"ORDER BY dbms_random.value) "
    +"WHERE ROWNUM = 1");

It is not working for me. Obviously, I did change all the column and table names to match my own database, but it just tells me "ERROR 42X01: Syntax error: Encountered "WHERE" at line 1, column 58."

I'm working in Eclipse. Do I need to import the functionality to use dbms_random or am I just missing something? Any help would be appreciated.

That looks fine, so maybe you lost the problem (like a missing space or parenthesis) while changing the names; can you post your original query instead? That doesn't look like a native Oracle error though, so maybe you have a middle layer that doesn't like the subquery syntax? — Alex Poole, Jan 12 '15 at 10:49
have you waited for the result? some times it takes some minutes to get results when using `ORDER BY dbms_random.value` — void, Jan 12 '15 at 10:49
The statement is syntactically correct. So I guess it is not Oracle complaining about the syntax, but some Java db layer getting it wrong. — Thorsten Kettner, Jan 12 '15 at 10:52
Works fine for me in Oracle 11.1. What version of Oracle are you querying against? — Bob Jarvis - Слава Україні, Jan 12 '15 at 12:09
Waiting for the result is out. The program terminates with the error. I have Oracle version 12c Release 1, which I believe is the most up to date. The only thing I see that might be a problem is on the documentation page, it says that DBMS_RANDOM should be installed by SYS. I don't actually know what that is referring to. Here is a link to the documentation, though. [link](http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_random.htm#ARPLS040) — henryr0923, Jan 12 '15 at 20:12
@henryr0923 you are right, I didn't attend that the program gives error, I only thought that it doesn't work (question title). — void, Jan 12 '15 at 21:07

void · Answer 1 · 2015-01-12T21:10:07.737

1

i'm not sure but order by dbms_random.value may not working properly when using extra layers for example jdbc. also when you order by <expression returning a number>, it orders by the expression, not a "column" however you want only one value to display, I can say two alternate ways:

Just add the dbms_random.value as a column to the query and order by that.

select * from (select
  firstName 
  , dbms_random.value as ran
from firstNames
order by ran)
where rownum=1;

use sample()

select firstName
from firstNames sample (1)
where rownum =1;

edited Jan 12 '15 at 21:10

answered Jan 12 '15 at 11:10

void

7,760
3
25
43

1

Neither alternative will work correctly. The first one will evaluate the `rownum` before the `order by` and will likely return the same value every time. The second one will work a little better, but will still tend to return some values over others because of the way data is retrieved pseudo-ordered by `sample`. Although using `sample` in combination with another random ordering may dramatically improve performance so it's an idea worth considering. – Jon Heller Jan 12 '15 at 19:57
I've seen that syntax before, but whenever I execute, it tells me that dbms_random.value isn't a column in my table. With the second script, using sample, I get a similar error to my original problem: ERROR 42X01: Syntax error: Encountered "1" at line 1, column 41. – henryr0923 Jan 12 '15 at 21:09
@JonHeller Both alternate works correctly, however thanks for the comment, I edited the first one, now it `order` it before evaluating `rownum` – void Jan 12 '15 at 21:13
@henryr0923, for me the both working properly, now I'm sure for you the problem is with the settings of dbms_random, I can't say more and you researched about it more and know more than me, If you found something share it with us also, anyway I hope you'll get it solved. – void Jan 12 '15 at 21:17
#1 is fixed but #2 still doesn't work for me. Try this code, and run the select multiple times; the results are very non-random: `create table test1(a number); insert into test1 select level from dual connect by level <= 100000; select a from test1 sample(1) where rownum = 1;` – Jon Heller Jan 12 '15 at 21:54

score 0 · Accepted Answer · edited May 23 '17 at 10:25

This is not an Oracle DB error code (they start with ORA- or ERR- or TNS-). It does look like a Derby error code. So did you use the wrong driver to access your oracle database, or do you actually mean JavaDB with "Oracle Database"? I guess the syntax is not valid for Derby/JavaDB.

According to this answer, the following syntax would work:

"SELECT firstName FROM firstNames ORDER BY RANDOM() OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY"

DBMS_RANDOM doesn't seem to work

2 Answers2