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;

DBMS_RANDOM.VALUE_UDF()

Parameters

• No parameters.

UDF

CREATE OR REPLACE FUNCTION DBMS_RANDOM.VALUE_UDF()
RETURNS DOUBLE
LANGUAGE JAVASCRIPT
AS
$$  
  return Math.random();
$$;

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

Usage example

Oracle

Query

SELECT DBMS_RANDOM.VALUE() FROM DUAL;

Result

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

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

Snowflake

Query

SELECT
--** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_RANDOM.VALUE_UDF' INSERTED. **
--** MSC-WARNING - MSCEWI3112 - DBMS_RANDOM.VALUE DIGITS OF PRECISION IS 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_UDF(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
AS
$$  
  return LOW === HIGH? LOW : LOW < HIGH? Math.random() * (HIGH-LOW) + LOW: Math.random() * (LOW-HIGH) + HIGH;
$$;

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 or 10 digits in the decimal part of the number (9 or 10 digits of precision)

Usage example

Oracle

Query

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

Result

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

Snowflake

Query

SELECT
--** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_RANDOM.VALUE_UDF' INSERTED. **
--** MSC-WARNING - MSCEWI3112 - DBMS_RANDOM.VALUE DIGITS OF PRECISION IS 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. MSCEWI1020: CUSTOM UDF 'DBMS_RANDOM.VALUE' INSERTED.

2. MSCEWI3112: DBMS_RANDOM.VALUE DIGITS OF PRECISION IS LOWER IN SNOWFLAKE.