Can dbms_random.value return null? If so, in what cases?

  • Cannot of course - Mike
  • I wouldn't do it right away from the shoulder) - daydark
  • @Mike immediately answer to write or give time for reflection? - daydark
  • 2
    I doubt both the usefulness of the question and the answer for anyone. If value is used as a function, then, like most functions, it will return NULL if one of the parameters is null. But this is the generally accepted behavior of functions, specifically to dbms_random that has nothing to do. And if you call it without parameters, it is clear that if it returns null, then it will be a bug because the documentation says that it returns a random value between 0 and 1. NULL is not a number in this range - Mike

1 answer 1

In fact, this is an explanation of why this function is used in connect by to parse strings. prior dbms_random.value is not null - the boundary condition for recursion / parsing. It is because of the absence of a boundary condition that a loop occurs. How to prove it:

 select PRIOR dbms_random.value from dual x where PRIOR dbms_random.value is null connect by level <= length(regexp_replace('some text. more', '[^!?.]+')) + 1 AND PRIOR dbms_random.value IS NOT NULL`

This query returns the string in which value is null.

  • And where does dbms_random? This is the PRIOR behavior declared in the documentation - it returns NULL in the event that there was no previous recursion for this string. Similarly, NULL will return instead of 1 in the select X, PRIOR X from (select 1 X from DUAL) connect by level<3 query select X, PRIOR X from (select 1 X from DUAL) connect by level<3 for the first line - Mike
  • As for the use of connect by, nobody cares about the value. the main thing is that it is unique for each new iteration. And it is checked for is null only by the fact that it had to be checked for something. in the same way one could write that it is not equal to 2, for example (because as 2 it cannot be just like NULL) - Mike
  • And even the cooler example is select prior 1 from DUAL connect by level<3 in the first line 1 becomes NULL ... - Mike
  • Yes, but we cannot use the knowledge that 1 turns into null. And at least I had a question, what else do we use random in connect by and why the condition is not null for us, if random does not return null - daydark
  • Maybe I incorrectly asked a question, but the answer contains practical knowledge. Who uses dbms_random.value to parse strings, must understand how this works - daydark