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, where X is greater than or equal to low and less than high. (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.

UDF

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();
$$;

Note: The UDF only supports approximately between 9 and 10 digits in the decimal part of the number (9 or 10 digits of precision)

Usage example

Oracle

Query

IN -> Oracle_01.sql

SELECT DBMS_RANDOM.VALUE() FROM DUAL;

Result

|DBMS_RANDOM.VALUE()                         |
|--------------------------------------------|
|0.47337471168356406022193430290380483126    |

Note: The function can be called either_DBMS_RANDOM.VALUE()_ or DBMS_RANDOM.VALUE.

Snowflake

Query

OUT -> Oracle_01.sql

SELECT
--** SSC-FDM-OR0033 - DBMS_RANDOM.VALUE DIGITS OF PRECISION ARE LOWER IN SNOWFLAKE **
DBMS_RANDOM.VALUE_UDF() FROM DUAL;

Result

|DBMS_RANDOM.VALUE() |
|--------------------|
|0.1014560867        |

Note: In Snowflake, you must put the parentheses.

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 to low.

• high: The highest NUMBER used as a limit when generating a random number. The number generated will be less than high.

UDF

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);
$$;

Notes:

• The Oracle DBMS_RANDOM.VALUE(low, high) function does not require parameters to have a specific order so the Snowflake UDF is implemented to support this feature by always taking out the highest and lowest number.

• The UDF only supports approximately between 9 and 10 digits in the decimal part of the number (9 or 10 digits of precision).

Usage example

Oracle

Query

IN -> Oracle_02.sql

SELECT DBMS_RANDOM.VALUE(-10,30) FROM DUAL;

Result

|DBMS_RANDOM.VALUE(-10,30)                   |
|--------------------------------------------|
|16.0298681859960167648070354679783928085    |

Snowflake

Query

OUT -> Oracle_02.sql

SELECT
--** SSC-FDM-OR0033 - DBMS_RANDOM.VALUE DIGITS OF PRECISION ARE LOWER IN SNOWFLAKE **
DBMS_RANDOM.VALUE_UDF(-10,30) FROM DUAL;

Result

|DBMS_RANDOM.VALUE(-10,30)   |
|----------------------------|
|-6.346055187                |

Known Issues

No issues were found.

Related EWIs

1. SSC-FDM-OR0033: DBMS_RANDOM.VALUE Built-In Package precision is lower in Snowflake.