VALUE functions
Translation reference for DBMS_RANDOM.VALUE.
Description
The basic function gets a random number, greater than or equal to 0 and less than 1. Alternatively, you can get a random Oracle number
X
, whereX
is greater than or equal tolow
and less thanhigh
. (Oracle PL/SQL DBMS_RANDOM.VALUE)
This UDF is implemented using the Math.random function of Javascript to replicate the functionality of Oracle DBMS_RANDOM.VALUE function.
Syntax
DBMS_RANDOM.VALUE()
RETURN NUMBER;
DBMS_RANDOM.VALUE(
low NUMBER,
high NUMBER)
RETURN NUMBER;
Custom UDF overloads
Setup data
The DBMS_RANDOM
schema must be created.
CREATE SCHEMA IF NOT EXISTS DBMS_RANDOM
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';
DBMS_RANDOM.VALUE()
Parameters
No parameters.
CREATE OR REPLACE FUNCTION DBMS_RANDOM.VALUE_UDF()
RETURNS DOUBLE
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
return Math.random();
$$;
Usage example
Oracle
SELECT DBMS_RANDOM.VALUE() FROM DUAL;
Snowflake
SELECT
--** SSC-FDM-OR0033 - DBMS_RANDOM.VALUE DIGITS OF PRECISION ARE LOWER IN SNOWFLAKE **
DBMS_RANDOM.VALUE_UDF() FROM DUAL;
DBMS_RANDOM.VALUE(NUMBER, NUMBER)
Parameters
low: The lowest
NUMBER
from which a random number is generated. The number generated is greater than or equal tolow
.high: The highest
NUMBER
used as a limit when generating a random number. The number generated will be less thanhigh
.
CREATE OR REPLACE FUNCTION DBMS_RANDOM.VALUE_UDF(low double, high double)
RETURNS DOUBLE
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
if (LOW > HIGH) {
[LOW, HIGH] = [HIGH, LOW];
}
const MAX_DECIMAL_DIGITS = 38;
return (Math.random() * (HIGH - LOW) + LOW).toFixed(MAX_DECIMAL_DIGITS);
$$;
Usage example
Oracle
SELECT DBMS_RANDOM.VALUE(-10,30) FROM DUAL;
Snowflake
SELECT
--** SSC-FDM-OR0033 - DBMS_RANDOM.VALUE DIGITS OF PRECISION ARE LOWER IN SNOWFLAKE **
DBMS_RANDOM.VALUE_UDF(-10,30) FROM DUAL;
Known Issues
No issues were found.
Related EWIs
SSC-FDM-OR0033: DBMS_RANDOM.VALUE Built-In Package precision is lower in Snowflake.
Related EWIs
SSC-EWI-OR0112: DBMS_RANDOM.VALUE digits of presicion is lower in Snowflake.
Last updated