DB version: 11.2.0.4
As I understand from googling, a call to DBMS_RANDOM.VALUE will return a value between 0 to 1.
If I use DBMS_RANDOM.VALUE in the ORDER BY clause, it will work fine. But, If I use a value between 0 to 1 directly in the ORDER BY clause, I will get ORA-01785 . Why is that
SQL> select dbms_random.value from dual;
VALUE
----------
.84540888
SQL> /
VALUE
----------
.910487267
SQL> /
VALUE
----------
.817587231
create table emp3 ( emp_id number, emp_name varchar2(15) );
insert into emp3 values ( 1, 'JOHN');
insert into emp3 values ( 2, 'KEITH' );
insert into emp3 values ( 3, 'KATY' );
insert into emp3 values ( 4, 'MAGDA');
insert into emp3 values ( 5, 'ANASTASIA');
insert into emp3 values ( 6, 'URS');
commit;
SQL> select * from emp3 order by dbms_random.value;
EMP_ID EMP_NAME
---------- ---------------
1 JOHN
3 KATY
6 URS
5 ANASTASIA
2 KEITH
4 MAGDA
6 rows selected.
SQL> /
EMP_ID EMP_NAME
---------- ---------------
5 ANASTASIA
4 MAGDA
3 KATY
6 URS
1 JOHN
2 KEITH
6 rows selected.
--- But, I cannot use a value between 0 to 1 directly in the ORDER BY clause
SQL> select * from emp3 order by .205034456 ;
select * from emp3 order by .205034456
*
ERROR at line 1:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression