we can control the Oracle's decision also by caching object_id via scalar subquery

View All Public Scripts Login to Run Script

Script Name we can control the Oracle's decision also by caching object_id via scalar subquery
Visibility Unlisted - anyone with the share link can access
Description see discussion on https://connor-mcdonald.com/2019/07/03/same-dog-different-leash-functions-in-sql
Area SQL General / Functions
Contributor Sokrates https://twitter.com/MatthiasRogel
Created Sunday July 07, 2019

Statement 1

with 
  function 
    getrand(pval in number) return number is 
  begin 
    return round(dbms_random.value(0,20)); 
  end; 
my_rnd as 
    ( select object_id, getrand((select object_id from dual)) getrnd from all_objects  ) 
select * 
from my_rnd 
where getrnd = 10;


OBJECT_ID GETRND
1779 10
1887 10
1911 10
1987 10
1991 10
2055 10
2069 10
2089 10
2109 10
2179 10
2191 10
2210 10
2244 10
2284 10
2304 10
2326 10
2398 10
2424 10
2440 10
2468 10
2476 10
2502 10
2510 10
2592 10
2664 10
2716 10
2772 10
2858 10
2924 10
3073 10
3133 10
3195 10
3261 10
3281 10
3301 10
3327 10
3439 10
3512 10
3596 10
3614 10
3802 10
3840 10
3902 10
3934 10
3960 10
3968 10
3988 10
4010 10
4016 10
4054 10

Rows 1 - 50. More rows exist.

OBJECT_ID	GETRND
1779	10
1887	10
1911	10
1987	10
1991	10
2055	10
2069	10
2089	10
2109	10
2179	10
2191	10
2210	10
2244	10
2284	10
2304	10
2326	10
2398	10
2424	10
2440	10
2468	10
2476	10
2502	10
2510	10
2592	10
2664	10
2716	10
2772	10
2858	10
2924	10
3073	10
3133	10
3195	10
3261	10
3281	10
3301	10
3327	10
3439	10
3512	10
3596	10
3614	10
3802	10
3840	10
3902	10
3934	10
3960	10
3968	10
3988	10
4010	10
4016	10
4054	10

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database