VALUE functions

Translation reference for DBMS_RANDOM.VALUE.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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

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

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

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

Known Issues

No issues were found.

Related EWIs

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