CODEWITHSUNDEEP
javasqleclipseoracle-database
henryr0923
henryr0923

Reputation: 13

DBMS_RANDOM doesn't seem to work

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.

Upvotes: 1

Views: 2069

Answers (2)

eckes
eckes

Reputation: 10423

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"

Upvotes: 0

void
void

Reputation: 7890

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:

  1. 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;

  2. use sample()

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

Upvotes: 1

Related Questions