SnowConvert AI - Function References for Oracle

DATEDIFF_UDF(TIMESTAMP, NUMBER)

Definition

This user-defined function (UDF) is used to subtract a number (which is a number of days) from a timestamp.

PUBLIC.DATEDIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM NUMBER)

Parameters

FIRST_PARAM TIMESTAMP

The timestamp that represents the minuend.

SECOND_PARAM NUMBER

The number of days that represents the subtrahend.

Returns

Returns a timestamp with the difference between the timestamp and the number.

Usage example

Input:

SELECT PUBLIC.DATEDIFF_UDF('2024-01-26 22:00:50.708 -0800', 3);

Output:

2024-01-23

DATEDIFF_UDF(TIMESTAMP, DATE)

Definition

This user-defined function (UDF) is used to subtract a date from a timestamp.

PUBLIC.DATEDIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM DATE)

Parameters

FIRST_PARAM TIMESTAMP

The timestamp that represents the minuend.

SECOND_PARAM DATE

The date that represents the subtrahend.

Returns

Returns an integer with the difference between the timestamp and the date.

Usage example

Input:

SELECT PUBLIC.DATEDIFF_UDF('2024-01-26 22:00:50.708 -0800', TO_DATE('2023-01-26'));

Output:

365

DATE_TO_JULIAN_DAYS_UDF

Definition

This user-defined function (UDF) transforms from Gregorian date to Julian date (The number of days since January 1, 4712 BC.).

PUBLIC.DATE_TO_JULIAN_DAYS_UDF(INPUT_DATE DATE)

Parameters

INPUT_DATE DATE

The Gregorian date to transform.

Returns

Returns the date representation of the Julian date.

Migration example

Input:

Select TO_CHAR(SYSDATE, 'J') as A from DUAL;

Output:

Select
PUBLIC.DATE_TO_JULIAN_DAYS_UDF(CURRENT_TIMESTAMP()) as A from DUAL;

Usage example

Input:

SELECT PUBLIC.DATE_TO_JULIAN_DAYS_UDF(DATE '1998-12-25');

Output:

2451173

UTL_FILE.PUT_LINE_UDF

Definition

This user-defined function (UDF) is used to replicate the functionality of the Oracle UTL_FILE_PUT_LINE procedure.

UTL_FILE.PUT_LINE_UDF(FILE VARCHAR,BUFFER VARCHAR)

Parameters

FILE VARCHAR

The file to open and save the new buffer.

BUFFER VARCHAR

The buffer to be saved on the defined file.

Returns

Returns a varchar with the result.

Usage example

Warning

To review the lines in the file, there are two ways: Downloading the file from the Snowflake CLI or briefly review the information with SELECT * FROM UTL_FILE.FOPEN_TABLES_LINES; but only if the file has not been closed.

Input:

CREATE OR REPLACE PROCEDURE PROC()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
    file_data  VARIANT;
   BEGIN

    CALL UTL_FILE.FOPEN_UDF('test2.csv','a');

    SELECT
      *
    INTO
      file_data
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));

    CALL UTL_FILE.PUT_LINE_UDF(:file_data,'New line');


    CALL UTL_FILE.FCLOSE_UDF(:file_data);


   END
$$;

CALL PROC();

Output:

null

UTL_FILE.FOPEN_UDF (VARCHAR,VARCHAR)

Definition

This user-defined function (UDF) is used to replicate the functionality of the Oracle UTL_FILE_FOPEN procedure.

UTL_FILE.FOPEN_UDF(FILENAME VARCHAR,OPEN_MODE VARCHAR)

Parameters

FILENAME VARCHAR

The file to be opened.

OPEN_MODE VARCHAR

Indicates the mode on which the file will be available.

Returns

Returns a varchar with the result.

Usage example

Warning

The UTL_FILE.FOPEN_UDF allows to open a .csv file. To access the file it is required to create a stage for the file and use the Snowflake CLI to upload it.

Input:

CREATE OR REPLACE PROCEDURE PROC()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
    file_data  VARIANT;
   BEGIN

    CALL UTL_FILE.FOPEN_UDF('test2.csv','a');

    SELECT
      *
    INTO
      file_data
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));


   END
$$;

CALL PROC();

Output:

null

JSON_VALUE_UDF

Definition

This user-defined function (UDF) reproduces the JSON_VALUE function to extract a single result out of a JSON variable.

JSON_VALUE_UDF(JSON_OBJECT VARIANT, JSON_PATH STRING, RETURNING_TYPE STRING, ON_ERROR_MESSAGE VARIANT, ON_EMPTY_MESSAGE VARIANT)

Parameters

JSON_OBJECT VARIANT

The JSON variable from which to extract the values.

JSON_PATH STRING

The JSON path that indicates where the values are located inside the JSON_OBJECT.

RETURNING_TYPE STRING

The type to return.

ON_ERROR_MESSAGE VARIANT

The error message to add if needed.

ON_EMPTY_MESSAGE VARIANT

The error message to add in case of empty message.

Returns

Returns a single value specified by the JSON_PATH inside the JSON_OBJECT. If the result is not a single value, returns a default error message or an error message defined in the input parameters.

Usage example

Input:

   SELECT
     JSON_VALUE_UDF(

     PARSE_JSON('{
  "iceCreamOrders": [
    {
      "customerID": "CUST001",
      "orderID": "ORD001",
      "productID": "PROD001",
      "quantity": 2
    }
  ]
}'),

JSON_EXTRACT_PATH_TEXT('{
  "iceCreamOrders": [
    {
      "customerID": "CUST001",
      "orderID": "ORD001",
      "productID": "PROD001",
      "quantity": 2
    }
  ]
}', 'iceCreamOrders'), 'VARIANT', TO_VARIANT('There was an error'), TO_VARIANT('Empty message'));

Output:

"Empty message"

DATEADD_UDF (FLOAT, TIMESTAMP)

Definition

This user-defined function (UDF) is used in cases when there is an addition between a float number and a timestamp.

PUBLIC.DATEADD_UDF(FIRST_PARAM FLOAT, SECOND_PARAM TIMESTAMP)

Parameters

FIRST_PARAM FLOAT

The timestamp number that is going to be added with the second float parameter.

SECOND_PARAM DATE

The float number to be added with the timestamp in the first parameter.

Returns

Returns a timestamp with the addition between the timestamp and the float number specified.

Usage example

Input:

SELECT DATEADD_UDF(1, current_timestamp);

Output:

2024-01-30 18:47:16.988

FETCH_BULK_COLLECTIONS_UDF (OBJECT)

Definition

This user-defined function (UDF) is used to replicate the functionality of fetching bulk for collections in Oracle. This function version receives the cursor only.

FETCH_BULK_COLLECTIONS_UDF(CURSOR OBJECT)

Parameters

CURSOR OBJECT

The cursor that is processed and filled with the data in the fetch bulk collection.

Returns

Returns an object with information related to the logic of fetching bulk collections.

Usage example

Input:

CREATE OR REPLACE TABLE MY_TABLE (test VARCHAR(100));
INSERT INTO MY_TABLE VALUES ('TEST_A');

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      MY_TABLE');

    BEGIN
        MY_CURSOR := (
            CALL OPEN_BULK_CURSOR_UDF(:MY_CURSOR)
        );
        MY_CURSOR := (
            CALL FETCH_BULK_COLLECTIONS_UDF(:MY_CURSOR)
        );

        Return MY_CURSOR;
    END;
$$;

CALL MY_PROCEDURE();

Output:

{
  "FOUND": true,
  "ISOPEN": true,
  "NAME": "MY_CURSOR",
  "NOTFOUND": false,
  "QUERY": "   SELECT * FROM\n      MY_TABLE",
  "RESULT": [
    [
      "TEST_A"
    ]
  ],
  "ROWCOUNT": 1
}

DATEADD_UDF (DATE, FLOAT)

Definition

This user-defined function (UDF) is used in cases when there is an addition between a date and a type as float or timestamp.

PUBLIC.DATEADD_UDF(FIRST_PARAM DATE, SECOND_PARAM FLOAT)

Parameters

FIRST_PARAM DATE

The date to be added with the number in the second parameter.

SECOND_PARAM FLOAT

The float number that is going to be added with the first date parameter.

Returns

Returns the addition between the date and the float number specified.

Migration example

Input:

SELECT TO_DATE('05/11/21', 'dd/mm/yy') + 3.4 from dual;

Output:

SELECT
PUBLIC.DATEADD_UDF( TO_DATE('05/11/21', 'dd/mm/yy'), 3.4) from dual;

Usage example

Input:

SELECT DATEADD_UDF('2022-02-14',6);

Output:

2022-02-20

DATEDIFF_UDF(DATE, TIMESTAMP)

Definition

This user-defined function (UDF) is used to subtract a timestamp from a date.

PUBLIC.DATEDIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM TIMESTAMP)

Parameters

FIRST_PARAM DATE

The date over the subtraction is done.

SECOND_PARAM TIMESTAMP

The timestamp to subtract from the first parameter.

Returns

Returns an integer with the days between the first and the second parameter.

Usage example

Input:

SELECT PUBLIC.DATEDIFF_UDF(TO_DATE('2024-01-26'), '2022-02-14 15:31:00');

Output:

711

DBMS_RANDOM.VALUE_UDF

Definition

This user-defined function (UDF) is to replicate the functionality of the Oracle DBMS_RANDOM.VALUE function.

DBMS_RANDOM.VALUE_UDF()

Parameters

No input parameters.

Returns

Returns a double number with a random number.

Usage example

Input:

SELECT DBMS_RANDOM.VALUE_UDF();

Output:

0.6666235896

DBMS_RANDOM.VALUE_UDF (DOUBLE, DOUBLE)

Definition

This user-defined function (UDF) is to replicate the functionality of the Oracle DBMS_RANDOM.VALUE function.

DBMS_RANDOM.VALUE_UDF(low DOUBLE, high DOUBLE)

Parameters

low DOUBLE

The initial limit to be considered.

high DOUBLE

The delimiting limit that coordinates with the first parameter.

Returns

Returns a double number with a random number between the limits specified.

Usage example

Input:

SELECT DBMS_RANDOM.VALUE_UDF(1.1, 2.2);

Output:

1.637802374

FETCH_BULK_RECORD_COLLECTIONS_UDF (OBJECT, ARRAY)

Definition

This user-defined function (UDF) is used to cover the functionality of fetch bulk records with different input parameters that determine the information added or the behavior of the cursor.

FETCH_BULK_RECORD_COLLECTIONS_UDF(CURSOR OBJECT, COLUMN_NAMES ARRAY)

Parameters

CURSOR OBJECT

The cursor that is being processed.

COLUMN_NAMES ARRAY

The column names that are associated with the cursor.

Returns

Returns an object with the processed information.

Usage example

Input:

CREATE OR REPLACE TABLE BULKCOLLECTTABLE(test VARCHAR(100));
INSERT INTO BULKCOLLECTTABLE VALUES ('TEST_A');

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      BULKCOLLECTTABLE');

    BEGIN
        MY_CURSOR := (
            CALL OPEN_BULK_CURSOR_UDF(:MY_CURSOR)
        );
        MY_CURSOR := (
            CALL FETCH_BULK_RECORD_COLLECTIONS_UDF(:MY_CURSOR, NULL)
        );

        Return MY_CURSOR;
    END;
$$;

CALL MY_PROCEDURE();

Output:

{
  "FOUND": true,
  "ISOPEN": true,
  "NAME": "MY_CURSOR",
  "NOTFOUND": false,
  "QUERY": "   SELECT * FROM\n      BULKCOLLECTTABLE",
  "RESULT": {
    "TEST": [
      "TEST_A"
    ]
  },
  "ROWCOUNT": 1
}

FETCH_BULK_COLLECTION_RECORDS_UDF (OBJECT, ARRAY)

Definition

This user-defined function (UDF) is used to replicate the functionality of FETCH in Oracle. This is the variation where it receives the cursor and the column names.

FETCH_BULK_COLLECTION_RECORDS_UDF(CURSOR OBJECT, COLUMN_NAMES ARRAY)

Parameters

CURSOR OBJECT

The cursor that is processed and filled with the data in the fetch bulk.

COLUMN_NAMES ARRAY

The name associated with the column is not the initial name.

Returns

Returns an object with the records from the fetch bulk.

Usage example

Input:

CREATE OR REPLACE TABLE MY_TABLE (test VARCHAR(100));
INSERT INTO MY_TABLE VALUES ('TEST_A');

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      MY_TABLE');

    BEGIN
        MY_CURSOR := (
            CALL OPEN_BULK_CURSOR_UDF(:MY_CURSOR)
        );
        MY_CURSOR := (
            CALL FETCH_BULK_COLLECTION_RECORDS_UDF(:MY_CURSOR, NULL)
        );

        Return MY_CURSOR;
    END;
$$;

CALL MY_PROCEDURE();

Output:

{
  "FOUND": true,
  "ISOPEN": true,
  "NAME": "MY_CURSOR",
  "NOTFOUND": false,
  "QUERY": "   SELECT * FROM\n      MY_TABLE",
  "RESULT": [
    {
      "TEST": "TEST_A"
    }
  ],
  "ROWCOUNT": 1
}

JULIAN_TO_GREGORIAN_DATE_UDF

Definition

This user-defined function (UDF) is used to transform a Julian date into the formats: JD Edwards, YYYYDDD (astronomical), and YYYYDDD (ordinal).

JULIAN_TO_GREGORIAN_DATE_UDF(JULIAN_DATE CHAR(7), FORMAT_SELECTED CHAR(1))

Parameters

JULIAN_DATE CHAR

The Julian date to transform.

FORMAT_SELECTED CHAR

The format required for the logic. E.g. 'E', 'J', 'R'. Astronomy standardized or 'J' is the default format.

Returns

Returns a variant with the date representation of the Julian date.

Usage example

Input:

SELECT JULIAN_TO_GREGORIAN_DATE_UDF('098185');

Output:

'1998-07-04' --(a.k.a Sat Jul 04 1998)

TIMESTAMP_DIFF_UDF

Definition

This user-defined function (UDF) is used for the timestamps arithmetic operations and the equivalence functionality in Snowflake.

TIMESTAMP_DIFF_UDF(LEFT_TS TIMESTAMP, RIGHT_TS TIMESTAMP )

Parameters

LEFT_TS TIMESTAMP

The minuend value.

RIGHT_TS TIMESTAMP

The subtrahend value.

Returns

Returns a varchar with the resulting difference between timestamps.

Usage example

Input:

SELECT TIMESTAMP_DIFF_UDF(TO_TIMESTAMP('2024-01-31 11:47:20.532 -0800'), TO_TIMESTAMP('2024-01-31 11:47:20.532 -0800'));

Output:

-000000000  00:00:00.00000000

REGEXP_LIKE_UDF (STRING, STRING, STRING)

Definition

This user-defined function (UDF) is

REGEXP_LIKE_UDF(COL STRING, PATTERN STRING, MATCHPARAM STRING)

Parameters

COL STRING

The string to be evaluated with the pattern.

PATTERN STRING

The pattern to be checked.

MATCHPARAM STRING

The match parameter that will determine whether the case-sensitive or not.

Returns

Returns

Usage example

Input:

SELECT REGEXP_LIKE_UDF('san Francisco', 'San* [fF].*', 'i');

Output:

TRUE

FETCH_BULK_COLLECTIONS_UDF (OBJECT, FLOAT)

Definition

This user-defined function (UDF) is used to replicate the functionality of fetching bulk for collections in Oracle. This function version receives the cursor and the limit value for the row count.

FETCH_BULK_COLLECTIONS_UDF(CURSOR OBJECT, LIMIT FLOAT)

Parameters

CURSOR OBJECT

The cursor that is processed and filled with the data in the fetch bulk collection.

LIMIT FLOAT

The limit for the records to call.

Returns

Returns an object with information related to the logic of fetching bulk collections.

Usage example

Input:

CREATE OR REPLACE TABLE MY_TABLE (test VARCHAR(100));
INSERT INTO MY_TABLE VALUES ('TEST_A');

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      MY_TABLE');

    BEGIN
        MY_CURSOR := (
            CALL OPEN_BULK_CURSOR_UDF(:MY_CURSOR)
        );
        MY_CURSOR := (
            CALL FETCH_BULK_COLLECTIONS_UDF(:MY_CURSOR, 1.0)
        );

        Return MY_CURSOR;
    END;
$$;

CALL MY_PROCEDURE();

Output:

{
  "FOUND": true,
  "ISOPEN": true,
  "NAME": "MY_CURSOR",
  "NOTFOUND": false,
  "QUERY": "   SELECT * FROM\n      MY_TABLE",
  "RESULT": [
    [
      "TEST_A"
    ]
  ],
  "ROWCOUNT": 1
}

INIT_CURSOR_UDF

Definition

This user-defined function (UDF) is to initialize a cursor object with the equivalent functionality.

INIT_CURSOR_UDF(NAME VARCHAR, QUERY VARCHAR)

Parameters

NAME VARCHAR

The name of the cursor.

QUERY VARCHAR

The query that is associated with the cursor.

Returns

Returns an object with the cursor information.

Usage example

Input:

CREATE OR REPLACE TABLE BULKCOLLECTTABLE(test VARCHAR(100));

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      BULKCOLLECTTABLE');

    BEGIN
        Return MY_CURSOR;
    END;
$$;

CALL MY_PROCEDURE();

Output:

{
  "ISOPEN": false,
  "NAME": "MY_CURSOR",
  "QUERY": "   SELECT * FROM\n      BULKCOLLECTTABLE",
  "ROWCOUNT": -1
}

UPDATE_PACKAGE_VARIABLE_STATE_UDF

Definition

This user-defined function (UDF) updates the given package variable values. It is a wrapper for the Snowflake SETVARIABLE() function.

UPDATE_PACKAGE_VARIABLE_STATE_UDF (VARIABLE VARCHAR, NEW_VALUE VARCHAR)

Parameters

VARIABLE VARCHAR

The variable name to set the value.

NEW_VALUE VARCHAR

The value that will be stored.

Returns

Returns a varchar with the information of the updated variable.

Usage example

Warning

Please, review the existence of the variable.

Input:

CALL PUBLIC.UPDATE_PACKAGE_VARIABLE_STATE_UDF('MY_LOCAL_VARIABLE', '1');

Output:

1

OPEN_BULK_CURSOR_UDF (OBJECT)

Definition

This user-defined function (UDF) is used to pen a cursor without bindings.

OPEN_BULK_CURSOR_UDF(CURSOR OBJECT)

Parameters

CURSOR OBJECT

The cursor to process as open.

Returns

Returns an object with the current information of the cursor.

Usage example

Input:

CREATE OR REPLACE TABLE BULKCOLLECTTABLE(test VARCHAR(100));

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      BULKCOLLECTTABLE');

    BEGIN
        MY_CURSOR := (
            CALL OPEN_BULK_CURSOR_UDF(:MY_CURSOR)
        );
        Return MY_CURSOR;
    END;
$$;

CALL MY_PROCEDURE();

Output:

{
  "ISOPEN": true,
  "NAME": "MY_CURSOR",
  "QUERY": "   SELECT * FROM\n      BULKCOLLECTTABLE",
  "ROWCOUNT": 0
}

DATEADD_UDF (TIMESTAMP, FLOAT)

Definition

This user-defined function (UDF) is used in cases when there is an addition between a timestamp and a float number.

PUBLIC.DATEADD_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM FLOAT)

Parameters

FIRST_PARAM TIMESTAMP

The timestamp number that is going to be added with the second float parameter.

SECOND_PARAM FLOAT

The float number to be added with the timestamp in the first parameter.

Returns

Returns a timestamp with the addition between the timestamp and the float number specified.

Usage example

Input:

SELECT DATEADD_UDF(current_timestamp, 1);

Output:

2024-01-26 13:22:49.354

DATEDIFF_UDF(TIMESTAMP, TIMESTAMP)

Definition

This user-defined function (UDF) subtracts a timestamp from another timestamp.

PUBLIC.DATEDIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM TIMESTAMP)

Parameters

FIRST_PARAM TIMESTAMP

The timestamp that represents the minuend.

SECOND_PARAM TIMESTAMP

The timestamp that represents the subtrahend.

Returns

Returns an integer with the difference of days between the first and the second timestamps.

Usage example

Input:

SELECT PUBLIC.DATEDIFF_UDF('2024-01-26 22:00:50.708 -0800','2023-01-26 22:00:50.708 -0800');

Output:

365

UTL_FILE.FCLOSE_UDF

Definition

This user-defined function (UDF) is used to replicate the functionality of the Oracle UTL_FILE_FCLOSE procedure.

UTL_FILE.FCLOSE_UDF(FILE VARCHAR)

Parameters

FILE VARCHAR

The file to process and close.

Returns

Returns a varchar with the result.

Usage example

Warning

The UTL_FILE.FCLOSE_UDF closes the file that is being processed. To review the result or handle files, it is required to use the Snowflake CLI console. The Snowflake CLI console allows the upload or download of a file.

Input:

CREATE OR REPLACE PROCEDURE PROC()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
    file_data  VARIANT;
   BEGIN

    CALL UTL_FILE.FOPEN_UDF('test2.csv','a');

    SELECT
      *
    INTO
      file_data
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));

    CALL UTL_FILE.PUT_LINE_UDF(:file_data,'New line');


    CALL UTL_FILE.FCLOSE_UDF(:file_data);


   END
$$;

CALL PROC();

Output:

null

FETCH_BULK_RECORD_COLLECTIONS_UDF (OBJECT)

Definition

This user-defined function (UDF) is used to cover the functionality of fetch bulk records with different input parameters that determine the information added or the behavior of the cursor.

FETCH_BULK_RECORD_COLLECTIONS_UDF(CURSOR OBJECT)

Parameters

CURSOR OBJECT

The cursor that is being processed.

Returns

Returns an object with the processed information.

Usage example

Input:

CREATE OR REPLACE TABLE BULKCOLLECTTABLE(test VARCHAR(100));
INSERT INTO BULKCOLLECTTABLE VALUES ('TEST_A');

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      BULKCOLLECTTABLE');

    BEGIN
        MY_CURSOR := (
            CALL OPEN_BULK_CURSOR_UDF(:MY_CURSOR)
        );
        MY_CURSOR := (
            CALL FETCH_BULK_RECORD_COLLECTIONS_UDF(:MY_CURSOR)
        );

        Return MY_CURSOR;
    END;
$$;

CALL MY_PROCEDURE();

Output:

{
  "FOUND": true,
  "ISOPEN": true,
  "NAME": "MY_CURSOR",
  "NOTFOUND": false,
  "QUERY": "   SELECT * FROM\n      BULKCOLLECTTABLE",
  "RESULT": {
    "TEST": [
      "TEST_A"
    ]
  },
  "ROWCOUNT": 1
}

CAST_DATE_UDF

Definition

The function processes a timestamp in string format to a date. It returns a date with the specified format.

PUBLIC.CAST_DATE_UDF(DATESTR STRING)

Parameters

DATESTR STRING

The date as a string to be formatted. The format should be ‘YYYY-MM-DD"T"HH24:MI:SS.FF' e.g. '2024-01-25T23:25:11.120'.

Please review the following information about formatting here.

Returns

Returns a date with the new format applied.

Usage example

Input:

SELECT PUBLIC.CAST_DATE_UDF('2024-01-25T23:25:11.120');

Output:

2024-01-25

FETCH_BULK_COLLECTION_RECORDS_UDF (OBJECT, FLOAT, ARRAY)

Definition

This user-defined function (UDF) is used to replicate the functionality of FETCH in Oracle. This is the variation where it receives the cursor, the limit, and the column names.

FETCH_BULK_COLLECTION_RECORDS_UDF(CURSOR OBJECT, LIMIT FLOAT, COLUMN_NAMES ARRAY)

Parameters

CURSOR OBJECT

The cursor that is processed and filled with the data in the fetch bulk.

LIMIT FLOAT

The limit for the records to call.

COLUMN_NAMES ARRAY

The name associated with the column is not the initial name.

Returns

Returns an object with the records from the fetch bulk.

Usage example

Input:

CREATE OR REPLACE TABLE MY_TABLE (test VARCHAR(100));
INSERT INTO MY_TABLE VALUES ('TEST_A');

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      MY_TABLE');

    BEGIN
        MY_CURSOR := (
            CALL OPEN_BULK_CURSOR_UDF(:MY_CURSOR)
        );
        MY_CURSOR := (
            CALL FETCH_BULK_COLLECTION_RECORDS_UDF(:MY_CURSOR, 1.0, NULL)
        );

        Return MY_CURSOR;
    END;
$$;

CALL MY_PROCEDURE();

Output:

{
  "FOUND": true,
  "ISOPEN": true,
  "NAME": "MY_CURSOR",
  "NOTFOUND": false,
  "QUERY": "   SELECT * FROM\n      MY_TABLE",
  "RESULT": [
    {
      "TEST": "TEST_A"
    }
  ],
  "ROWCOUNT": 1
}

DATEDIFF_UDF(DATE, INTEGER)

Definition

This user-defined function (UDF) applies a subtraction of days over a date.

PUBLIC.DATEDIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM INTEGER)

Parameters

FIRST_PARAM DATE

The initial date to apply the subtraction.

SECOND_PARAM INTEGER

The number of days to be subtracted from the first date parameter.

Returns

Returns the date after subtracting the indicated number of days.

Usage example

Input:

SELECT PUBLIC.DATEDIFF_UDF(TO_DATE('2024-01-26'), 365);

Output:

2023-01-26

DATE_TO_RR_FORMAT_UDF

Definition

This user-defined function (UDF) transforms from date to oracle RR datetime format date

PUBLIC.DATE_TO_RR_FORMAT_UDF(INPUT_DATE DATE)

Parameters

INPUT_DATE DATE

The date to transform.

Returns

The input date with years adjusted to RR format.

Migration example

Input:

Select TO_DATE('17-NOV-30','DD-MON-RR') as A from DUAL;

Output:

Select
PUBLIC.DATE_TO_RR_FORMAT_UDF( TO_DATE('17-NOV-30', 'DD-MON-YY')) as A from DUAL;

Usage example

Input:

PUBLIC.CONVERT_DATE_WITH_RR_FORMAT_UDF(TO_DATE('17-NOV-30','DD-MON-YY')) as A from DUAL;

Output:

2030-11-17

FETCH_BULK_RECORD_COLLECTIONS_UDF (OBJECT, INTEGER)

Definition

This user-defined function (UDF) is used to cover the functionality of fetch bulk records with different input parameters that determine the information added or the behavior of the cursor.

FETCH_BULK_RECORD_COLLECTIONS_UDF(CURSOR OBJECT, LIMIT INTEGER)

Parameters

CURSOR OBJECT

The cursor that is being processed.

LIMIT INTEGER

The limit of the row count.

Returns

Returns an object with the processed information.

Usage example

Input:

CREATE OR REPLACE TABLE BULKCOLLECTTABLE(test VARCHAR(100));
INSERT INTO BULKCOLLECTTABLE VALUES ('TEST_A');

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      BULKCOLLECTTABLE');

    BEGIN
        MY_CURSOR := (
            CALL OPEN_BULK_CURSOR_UDF(:MY_CURSOR)
        );
        MY_CURSOR := (
            CALL FETCH_BULK_RECORD_COLLECTIONS_UDF(:MY_CURSOR, 0)
        );

        Return MY_CURSOR;
    END;
$$;

CALL MY_PROCEDURE();

Output:

{
  "FOUND": false,
  "ISOPEN": true,
  "NAME": "MY_CURSOR",
  "NOTFOUND": true,
  "QUERY": "   SELECT * FROM\n      BULKCOLLECTTABLE",
  "RESULT": {
    "TEST": []
  },
  "ROWCOUNT": 0
}

DBMS_OUTPUT.PUT_LINE_UDF

Definition

This user-defined function (UDF) is used to replicate the functionality of the Oracle DBMS_OUTPUT_PUT_LINE function.

DBMS_OUTPUT.PUT_LINE_UDF(LOG VARCHAR)

Warning

Notice that performance may be affected by using this UDF. To start logging information uncomment the implementation inside the function.

Parameters

LOG VARCHAR

The information to be shown in the command line.

Returns

Returns a varchar with the information logged.

Usage example

Input:

SELECT DBMS_OUTPUT.PUT_LINE_UDF(to_varchar(123));

Output:

123

DATEDIFF_UDF(DATE, DATE)

Definition

This user-defined function (UDF) is used when there is a subtraction between two dates.

PUBLIC.DATEDIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM DATE)

Parameters

FIRST_PARAM DATE

The date that represents the minuend in the subtraction.

SECOND_PARAM DATE

The date that represents the subtrahen in the subtraction.

Returns

Returns an integer with the number of days between the dates.

Usage example

Input:

SELECT PUBLIC.DATEDIFF_UDF(TO_DATE('2024-01-26'), TO_DATE('2023-01-26'));

Output:

365

OPEN_BULK_CURSOR_UDF (OBJECT, ARRAY)

Definition

This user-defined function (UDF) is used to open a cursor with bindings.

OPEN_BULK_CURSOR_UDF(CURSOR OBJECT, BINDINGS ARRAY)

Parameters

CURSOR OBJECT

The cursor to process as open.

BINDINGS ARRAY

The binding that is related to the cursor.

Returns

Returns an object with the current information of the cursor.

Usage example

Input:

CREATE OR REPLACE TABLE BULKCOLLECTTABLE(test VARCHAR(100));

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      BULKCOLLECTTABLE');

    BEGIN
        MY_CURSOR := (
            CALL OPEN_BULK_CURSOR_UDF(:MY_CURSOR, NULL)
        );
        Return MY_CURSOR;
    END;
$$;

CALL MY_PROCEDURE();

Output:

{
  "ISOPEN": true,
  "NAME": "MY_CURSOR",
  "QUERY": "   SELECT * FROM\n      BULKCOLLECTTABLE",
  "ROWCOUNT": 0
}

CLOSE_BULK_CURSOR_UDF

Definition

This user-defined function (UDF) deletes the temporary table that stores the result set of the cursor and resets the cursor properties to their initial state.

CLOSE_BULK_CURSOR_UDF(CURSOR OBJECT)

Parameters

CURSOR OBJECT

The cursor that is checked and closed.

Returns

Returns an object with the cursor properties reset.

Migration example

Input:

-- [procedure initial logic]
CLOSE C1;
-- [procedure ending logic]

Output:

C1 := (
            CALL CLOSE_BULK_CURSOR_UDF(:C1)
        );

Usage example

Input:

CREATE OR REPLACE TABLE BULKCOLLECTTABLE(test VARCHAR(100));

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      BULKCOLLECTTABLE');

    BEGIN
        MY_CURSOR := (
            CALL OPEN_BULK_CURSOR_UDF(:MY_CURSOR)
        );
        MY_CURSOR := (
            CALL CLOSE_BULK_CURSOR_UDF(:MY_CURSOR)
        );

        RETURN MY_CURSOR;
    END;
$$;

Output:

{
  "FOUND": null,
  "ISOPEN": false,
  "NAME": "MY_CURSOR",
  "NOTFOUND": null,
  "QUERY": "   SELECT * FROM\n      BULKCOLLECTTABLE",
  "ROWCOUNT": -1
}

DATEADD_UDF (FLOAT, DATE)

Definition

This user-defined function (UDF) is used in cases when there is an addition between a type as float or timestamp and a date.

PUBLIC.DATEADD_UDF(FIRST_PARAM FLOAT, SECOND_PARAM DATE)

Parameters

FIRST_PARAM FLOAT

The float number that is going to be added with the second date parameter.

SECOND_PARAM DATE

The date to be added with the number in the first parameter.

Returns

Returns the addition between the float number and the date specified.

Usage example

Input:

SELECT DATEADD_UDF(6, '2022-02-14');

Output:

2022-02-20

BFILENAME_UDF

Definition

The function takes the directory name and the filename parameter as a string. Then, it returns a concatenation using the '\'.

Warning

The character '\' must be changed to match the Operating System file concatenation character.

PUBLIC.BFILENAME_UDF (DIRECTORYNAME STRING, FILENAME STRING);

Parameters

DIRECTORYNAME STRING

The directory name to be processed as a string.

FILENAME STRING

The filename to be concatenated.

Returns

Returns a string that contains the directory name and filename concatenated by a '\'.

Migration example

Input:

SELECT BFILENAME ('directory', 'filename.jpg') FROM DUAL;

Output:

SELECT
PUBLIC.BFILENAME_UDF('directory', 'filename.jpg') FROM DUAL;

Usage example

Input:

SELECT PUBLIC.BFILENAME_UDF('directory', 'filename.jpg');

Output:

directory\filename.jpg

REGEXP_LIKE_UDF (STRING, STRING)

Definition

This user-defined function (UDF) is used to support the Oracle REGEXP_LIKE functionality.

REGEXP_LIKE_UDF(COL STRING, PATTERN STRING)

Parameters

COL STRING

The string to be evaluated with the pattern.

PATTERN STRING

The pattern to be checked.

Returns

Returns a boolean expression. True if the pattern matches the string; otherwise, false.

Usage example

Input:

SELECT REGEXP_LIKE_UDF('San Francisco', 'San* [fF].*');

Output:

TRUE

UTL_FILE.FOPEN_UDF (VARCHAR, VARCHAR, VARCHAR)

Definition

This user-defined function (UDF) is used to replicate the functionality of the Oracle UTL_FILE_FOPEN procedure.

UTL_FILE.FOPEN_UDF(PACKAGE_VARIABLE VARCHAR, FILENAME VARCHAR, OPEN_MODE VARCHAR)

Parameters

PACKAGE_VARIABLE VARCHAR

The variable related to the file opening.

FILENAME VARCHAR

The file to be opened.

OPEN_MODE VARCHAR

Indicates the mode on which the file will be available.

Returns

Returns a varchar with the result.

Usage example

Warning

The UTL_FILE.FOPEN_UDF allows to open a .csv file. To access the file it is required to create a stage for the file and use the Snowflake CLI to upload it.

Input:

CREATE OR REPLACE PROCEDURE PROC()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
    file_data  VARIANT;
   BEGIN

    CALL UTL_FILE.FOPEN_UDF(NULL, 'test2.csv','a');

    SELECT
      *
    INTO
      file_data
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));

    CALL UTL_FILE.PUT_LINE_UDF(:file_data,'New line');


    CALL UTL_FILE.FCLOSE_UDF(:file_data);


   END
$$;

CALL PROC();

Output:

null

FETCH_BULK_COLLECTION_RECORDS_UDF (OBJECT)

Definition

This user-defined function (UDF) is used to replicate the functionality of FETCH in Oracle. This is the variation where it receives the cursor only.

FETCH_BULK_COLLECTION_RECORDS_UDF(CURSOR OBJECT)

Parameters

CURSOR OBJECT

The cursor that is processed and filled with the data in the fetch bulk.

Returns

Returns an object with the records from the fetch bulk.

Usage example

Input:

CREATE OR REPLACE TABLE MY_TABLE (test VARCHAR(100));
INSERT INTO MY_TABLE VALUES ('TEST_A');

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      MY_TABLE');

    BEGIN
        MY_CURSOR := (
            CALL OPEN_BULK_CURSOR_UDF(:MY_CURSOR)
        );
        MY_CURSOR := (
            CALL FETCH_BULK_COLLECTION_RECORDS_UDF(:MY_CURSOR)
        );

        Return MY_CURSOR;
    END;
$$;

CALL MY_PROCEDURE();

Output:

{
  "FOUND": true,
  "ISOPEN": true,
  "NAME": "MY_CURSOR",
  "NOTFOUND": false,
  "QUERY": "   SELECT * FROM\n      MY_TABLE",
  "RESULT": [
    {
      "TEST": "TEST_A"
    }
  ],
  "ROWCOUNT": 1
}

FETCH_BULK_RECORD_COLLECTIONS_UDF (OBJECT, FLOAT, ARRAY)

Definition

This user-defined function (UDF) is used to cover the functionality of fetch bulk records with different input parameters that determine the information added or the behavior of the cursor.

FETCH_BULK_RECORD_COLLECTIONS_UDF(CURSOR OBJECT, LIMIT FLOAT, COLUMN_NAMES ARRAY)

Parameters

CURSOR OBJECT

The cursor that is being processed.

LIMIT FLOAT

The limit of the row count.

COLUMN_NAMES ARRAY

The column names that are associated with the cursor.

Returns

Returns an object with the processed information.

Usage example

Input:

CREATE OR REPLACE TABLE BULKCOLLECTTABLE(test VARCHAR(100));
INSERT INTO BULKCOLLECTTABLE VALUES ('TEST_A');

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      BULKCOLLECTTABLE');

    BEGIN
        MY_CURSOR := (
            CALL OPEN_BULK_CURSOR_UDF(:MY_CURSOR)
        );
        MY_CURSOR := (
            CALL FETCH_BULK_RECORD_COLLECTIONS_UDF(:MY_CURSOR, 1.0, NULL)
        );

        RETURN MY_CURSOR;
    END;
$$;

CALL MY_PROCEDURE();

Output:

{
  "FOUND": true,
  "ISOPEN": true,
  "NAME": "MY_CURSOR",
  "NOTFOUND": false,
  "QUERY": "   SELECT * FROM\n      BULKCOLLECTTABLE",
  "RESULT": {
    "TEST": [
      "TEST_A"
    ]
  },
  "ROWCOUNT": 1
}

FETCH_BULK_COLLECTION_RECORDS_UDF (OBJECT, INTEGER)

Definition

This user-defined function (UDF) is used to replicate the functionality of FETCH in Oracle. This is the variation where it receives the cursor and the limit.

FETCH_BULK_COLLECTION_RECORDS_UDF(CURSOR OBJECT, LIMIT INTEGER)

Parameters

CURSOR OBJECT

The cursor that is processed and filled with the data in the fetch bulk.

LIMIT FLOAT

The limit for the records to call.

Returns

Returns an object with the records from the fetch bulk.

Usage example

Input:

CREATE OR REPLACE TABLE MY_TABLE (test VARCHAR(100));
INSERT INTO MY_TABLE VALUES ('TEST_A');

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      MY_TABLE');

    BEGIN
        MY_CURSOR := (
            CALL OPEN_BULK_CURSOR_UDF(:MY_CURSOR)
        );
        MY_CURSOR := (
            CALL FETCH_BULK_COLLECTION_RECORDS_UDF(:MY_CURSOR, 0)
        );

        Return MY_CURSOR;
    END;
$$;

CALL MY_PROCEDURE();

Output:

{
  "FOUND": false,
  "ISOPEN": true,
  "NAME": "MY_CURSOR",
  "NOTFOUND": true,
  "QUERY": "   SELECT * FROM\n      MY_TABLE",
  "RESULT": [],
  "ROWCOUNT": 0
}