SnowConvert: Oracle Built-In packages¶

Description¶

Oracle supplies many PL/SQL packages with the Oracle server to extend database functionality and provide PL/SQL access to SQL features. (Oracle PL/SQL Built-in Packages)

DBMS_RANDOM¶

Description¶

The DBMS_RANDOM package provides a built-in random number generator. DBMS_RANDOM is not intended for cryptography. (Oracle PL/SQL DBMS_RANDOM)

VALUE functions¶

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;

Copy

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"}}';

Copy

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

Copy

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

SELECT DBMS_RANDOM.VALUE() FROM DUAL;

Copy

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

Copy

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

Snowflake

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

Copy

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

Copy

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.

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

Copy

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

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

Copy

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

Copy

Snowflake

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

Copy

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

Copy

Known Issues¶

No issues were found.

DBMS_OUTPUT¶

Description¶

The DBMS_OUTPUT package is especially useful for displaying PL/SQL debugging information. (Oracle PL/SQL DBMS_OUTPUT)

PUT_LINE procedure¶

Description¶

This procedure places a line in the buffer. (Oracle PL/SQL DBMSOUTPUT.PUT_LINE)

This UDF is implemented using a temporary table to insert the data to be displayed to replicate the functionality of Oracle DBMS_OUTPUT.PUT_LINE function.

Syntax¶

DBMS_OUTPUT.PUT_LINE(LOG VARCHAR);

Copy

Custom procedure¶

Setup data¶

The DBMS_OUTPUT schema must be created.

CREATE SCHEMA IF NOT EXISTS DBMS_OUTPUT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';

Copy

DBMS_OUTPUT.PUT_LINE(VARCHAR)¶

Parameters

LOG: Item in a buffer that you want to display.

CREATE OR REPLACE procedure DBMS_OUTPUT.PUT_LINE_UDF(LOG VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS $$
   
  //Performance may be affected by using this UDF.
  //If you want to start logging information, please uncomment the implementation.
  //Once the calls of DBMS_OUTPUT.PUT_LINE have been done, please use
  //the following query to read all the logs:
  //SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG.

  //snowflake.execute({sqlText:`
  //CREATE TEMPORARY TABLE IF NOT EXISTS DBMS_OUTPUT_LOG 
  //(
  //  WHEN TIMESTAMP,
  //  DATABASE VARCHAR,
  //  LOG VARCHAR
  //);`});

  //snowflake.execute({sqlText:`INSERT INTO DBMS_OUTPUT_LOG(WHEN, DATABASE, LOG) VALUES (CURRENT_TIMESTAMP,CURRENT_DATABASE(),?)`, binds:[LOG]});
  return LOG;
$$;

Copy

Note:

Note that this is using a temporary table, if you want the data to persist after a session ends, please remove TEMPORARY from the CREATE TABLE.
The temporary tables store non-permanent transitory data. They only exist within the session in which they were created and persist only for the rest of the session. After the session ends, the data stored in the table is completely removed from the system and is therefore not recoverable, either by the user who created the table or by Snowflake.

If you do not use the temporary table, keep in mind that you may need another column in the table where the USER running DBMS_OUTPUT.PUT_LINE UDF is inserted to avoid confusion.

Usage example¶

Oracle

CREATE OR REPLACE PROCEDURE PROC 
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Test');
END;

CALL PROC();

Copy

|DBMS_OUTPUT.PUT_LINE('test') |
|-----------------------------|
|test                         |

Copy

Snowflake

CREATE OR REPLACE PROCEDURE PROC ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
        CALL DBMS_OUTPUT.PUT_LINE_UDF('Test');
    END;
$$;

CALL PROC();

Copy

|ROW |WHEN                    |DATABASE    |LOG      |
|----|------------------------|------------|---------|
| 1  |2022-04-25 11:16:23.844 |CODETEST    |test     |

Copy

Known Issues¶

The UDF code will remain commented out because it can affect performance, if the user decides to use it, they just need to uncomment the code.
The user can modify the UDF so that the necessary information is inserted into the DBMS_OUTPUT.PUT_LINE table.

Related EWIs¶

SSC-FDM-OR0035: Check UDF implementation for DBMS_OUTPUT.PUT_LINE_UDF.

DBMS_LOB¶

Description¶

The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs. You can use DBMS_LOB to access and manipulate specific parts of a LOB or complete LOBs. (Oracle PL/SQL DBMS_LOB)

SUBSTR Function¶

Description¶

This function returns amount bytes or characters of a LOB, starting from an absolute offset from the beginning of the LOB. (Oracle PL/SQL DBMS_LOB.SUBSTR)

This built-in function is replaced with Snowflake SUBSTR function. However, there are some differences.

Note: The amount and offset parameters are inverted in Snowflake

Syntax¶

DBMS_LOB.SUBSTR (
   lob_loc     IN    BLOB,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
  RETURN RAW;

DBMS_LOB.SUBSTR (
   lob_loc     IN    CLOB   CHARACTER SET ANY_CS,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
  RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;

DBMS_LOB.SUBSTR (
   file_loc     IN    BFILE,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
  RETURN RAW;

Copy

Function overloads¶

DBMS_LOB.SUBSTR(‘string’, amount, offset)¶

Usage example

Oracle

SELECT 
-- 1. "some magic here"
DBMS_LOB.SUBSTR('some magic here', 15, 1) "1",
-- 2. "some"
DBMS_LOB.SUBSTR('some magic here', 4, 1) "2",
-- 3. "me magic here"
DBMS_LOB.SUBSTR('some magic here', 15, 3) "3",
-- 4. "magic"
DBMS_LOB.SUBSTR('some magic here', 5, 6) "4",
-- 5. "here"
DBMS_LOB.SUBSTR('some magic here', 20, 12) "5",
-- 6. " "
DBMS_LOB.SUBSTR('some magic here', 250, 16) "6"
FROM DUAL;

Copy

1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |

Copy

Snowflake

SELECT
-- 1. "some magic here"
SUBSTR('some magic here', 1, 15) "1",
-- 2. "some"
SUBSTR('some magic here', 1, 4) "2",
-- 3. "me magic here"
SUBSTR('some magic here', 3, 15) "3",
-- 4. "magic"
SUBSTR('some magic here', 6, 5) "4",
-- 5. "here"
SUBSTR('some magic here', 12, 20) "5",
-- 6. " "
SUBSTR('some magic here', 16, 250) "6"
FROM DUAL;

Copy

1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |

Copy

DBMS_LOB.SUBSTR(BLOB, amount, offset)¶

Usage example

Note: Result values in Oracle and Snowflake are being converted from bytes to strings for easier understanding of the function.

For Snowflake consider using:

hex_decode_string( to_varchar(SUBSTR(blob_column, 1, 6), ‘HEX’));

and for Oracle consider using:

utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(blob_column, 1, 6));

to obtain the result as a string.

Oracle

-- Create Table
CREATE TABLE blobtable( blob_column BLOB );

-- Insert sample value
INSERT INTO blobtable VALUES (utl_raw.cast_to_raw('some magic here'));

-- Select different examples
SELECT 
-- 1. "some magic here"
DBMS_LOB.SUBSTR(blob_column, 15, 1) "1",
-- 2. "some"
DBMS_LOB.SUBSTR(blob_column, 4, 1) "2",
-- 3. "me magic here"
DBMS_LOB.SUBSTR(blob_column, 15, 3) "3",
-- 4. "magic"
DBMS_LOB.SUBSTR(blob_column, 5, 6) "4",
-- 5. "here"
DBMS_LOB.SUBSTR(blob_column, 20, 12) "5",
-- 6. " "
DBMS_LOB.SUBSTR(blob_column, 250, 16) "6"
FROM BLOBTABLE;

Copy

1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |

Copy

Snowflake

-- Create Table
CREATE OR REPLACE TABLE blobtable ( blob_column BINARY
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

-- Insert sample value
INSERT INTO blobtable
VALUES (
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'utl_raw.cast_to_raw' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS cast_to_raw);

-- Select different examples
SELECT
-- 1. "some magic here"
SUBSTR(blob_column, 1, 15) "1",
-- 2. "some"
SUBSTR(blob_column, 1, 4) "2",
-- 3. "me magic here"
SUBSTR(blob_column, 3, 15) "3",
-- 4. "magic"
SUBSTR(blob_column, 6, 5) "4",
-- 5. "here"
SUBSTR(blob_column, 12, 20) "5",
-- 6. " "
SUBSTR(blob_column, 16, 250) "6"
FROM
BLOBTABLE;

Copy

1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |

Copy

Note: UTL_RAW.CAST_TO_RAW() is currently not being transformed to TO_BINARY(). The function is used to show the functional equivalence of the example.

DBMS_LOB.SUBSTR(CLOB, amount, offset)¶

Usage example

Oracle

-- Create Table
CREATE TABLE clobtable(clob_column CLOB);

-- Insert sample value
INSERT INTO clobtable VALUES ('some magic here');

-- Select
SELECT 
-- 1. "some magic here"
DBMS_LOB.SUBSTR(clob_column, 15, 1) "1",
-- 2. "some"
DBMS_LOB.SUBSTR(clob_column, 4, 1) "2",
-- 3. "me magic here"
DBMS_LOB.SUBSTR(clob_column, 15, 3) "3",
-- 4. "magic"
DBMS_LOB.SUBSTR(clob_column, 5, 6) "4",
-- 5. "here"
DBMS_LOB.SUBSTR(clob_column, 20, 12) "5",
-- 6. " "
DBMS_LOB.SUBSTR(clob_column, 250, 16) "6"
FROM clobtable;

Copy

1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |

Copy

Snowflake

-- Create Table
CREATE OR REPLACE TABLE clobtable (clob_column VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

-- Insert sample value
INSERT INTO clobtable
VALUES ('some magic here');

-- Select
SELECT
-- 1. "some magic here"
SUBSTR(clob_column, 1, 15) "1",
-- 2. "some"
SUBSTR(clob_column, 1, 4) "2",
-- 3. "me magic here"
SUBSTR(clob_column, 3, 15) "3",
-- 4. "magic"
SUBSTR(clob_column, 6, 5) "4",
-- 5. "here"
SUBSTR(clob_column, 12, 20) "5",
-- 6. " "
SUBSTR(clob_column, 16, 250) "6"
FROM
clobtable;

Copy

1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |

Copy

Note: UTL_RAW.CAST_TO_RAW() is currently not being transformed to TO_BINARY(). The function is used to show the functional equivalence of the example.

DBMS_LOB.SUBSTR(BFILE, amount, offset)¶

Usage example

Using DBMS_LOB.SUBSTR() on a BFILE column returns a substring of the file content.

Next example is not a current migration, but a functional example to show the differences of the SUBSTR function on BFILE types.

File Content (file.txt):

some magic here

Copy

Oracle

CREATE OR REPLACE PROCEDURE bfile_substr_procedure
IS
    fil BFILE := BFILENAME('MY_DIR', 'file.txt');
BEGIN
    DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
    DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(fil,9,1)));
    --Console Output:
    -- "some magi"
    DBMS_LOB.FILECLOSE(fil);
END;

Copy

DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(fil,4,1))) |
-------------------------------------------------------------------------|
some magic                                                               |

Copy

Snowflake

BFILE columns are translated into VARCHAR columns, therefore applying a SUBSTR function on the same column would return a substring of the file name, not the file content.

CREATE OR REPLACE PROCEDURE bfile_substr_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        fil VARCHAR := PUBLIC.BFILENAME_UDF('MY_DIR', 'file.txt');
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_LOB.FILEOPEN' IS NOT CURRENTLY SUPPORTED. ***/!!!
        DBMS_LOB.FILEOPEN(:fil,
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_LOB.FILE_READONLY' IS NOT CURRENTLY SUPPORTED. ***/!!!
        '' AS FILE_READONLY);
        --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
        CALL DBMS_OUTPUT.PUT_LINE_UDF(
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'UTL_RAW.CAST_TO_VARCHAR2' IS NOT CURRENTLY SUPPORTED. ***/!!!
        '' AS CAST_TO_VARCHAR2);
        --Console Output:
        -- "some magi"
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_LOB.FILECLOSE' IS NOT CURRENTLY SUPPORTED. ***/!!!
        DBMS_LOB.FILECLOSE(:fil);
    END;
$$;

Copy

SUBSTR(bfile_column, 1, 9) |
---------------------------|
MY_DIR\fi                  |

Copy

Known Issues¶

1. Using DBMS_LOB.SUBSTR with BFILE columns¶

The current transformation for BFILE datatypes in columns is VARCHAR, where the name of the file is stored as a string. Therefore applying the SUBSTR function on a BFILE column after transformation will return a substring of the file name, while Oracle would return a substring of the file content.

Related EWIs¶

SSC-EWI-OR0076: Built In Package Not Supported.
SSC-FDM-OR0035: DBMS_OUTPUT.PUTLINE check UDF implementation.

UTL_FILE¶

Description¶

With UTL_FILE package, PL/SQL programs can read and write text files. (Oracle PL/SQL UTL_FILE)

FCLOSE procedure¶

Description¶

This procedure closes an open file identified by a file handle. (Oracle PL/SQL UTL_FILE.FCLOSE)

This procedure is implemented using Snowflake STAGE to store the written text files.

This procedure requires to be used in conjunction with:

UTL_FILE.FOPEN procedure

Syntax¶

UTL_FILE.FCLOSE(
    FILE VARCHAR
    );

Copy

Setup data¶

The UTL_FILE schema must be created.

CREATE SCHEMA IF NOT EXISTS UTL_FILE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';

Copy

If you want to download the file, run the following command.

GET @UTL_FILE.utlfile_local_directory/<filename> file://<path_to_file>/<filename>;

Copy

The GET command runs in Snowflake CLI.

Custom procedure overloads¶

UTL_FILE.FCLOSE(VARCHAR)¶

Parameters

FILE: Active file handler returned from the call to UTL_FILE.FOPEN

Functionality

This procedure uses the FOPEN_TABLES_LINES table created in the UTL_FILE.FOPEN procedure.

This procedure writes to the utlfile_local_directory stage all lines with the same FHANDLE from the file in FOPEN_TABLES_LINES.

CREATE OR REPLACE PROCEDURE UTL_FILE.FCLOSE_UDF(FILE VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
     DECLARE
        fhandle VARCHAR;
        fileParse VARIANT;
        File_is_read_only exception;
        fileNameConcat VARCHAR;
        copyIntoQuery VARCHAR ;
    BEGIN
        fileParse:= PARSE_JSON(FILE);
        fhandle:= :fileParse:handle;
        fileNameConcat:= '@UTL_FILE.utlfile_local_directory/'||:fileParse:name;
        copyIntoQuery:= 'COPY INTO '||:fileNameConcat||' FROM (SELECT LINE FROM UTL_FILE.FOPEN_TABLES_LINES WHERE FHANDLE = ? ORDER BY SEQ) FILE_FORMAT= (FORMAT_NAME = my_csv_format COMPRESSION=NONE)   OVERWRITE=TRUE';
        EXECUTE IMMEDIATE :copyIntoQuery USING (fhandle);
        DELETE FROM UTL_FILE.FOPEN_TABLES_LINES WHERE FHANDLE = :fhandle;
        DELETE FROM UTL_FILE.FOPEN_TABLES WHERE FHANDLE = :fhandle;
    END
$$;

Copy

Note:

Note that this procedure uses the stage that was created previously. For now, if you want to write the file in another stage, you must modify the name.
These procedures are implemented for the internal stages in the COPY INTO

Usage example¶

Oracle

DECLARE 
    w_file UTL_FILE.FILE_TYPE;
BEGIN
    w_file:= UTL_FILE.FOPEN('MY_DIR','test.csv','w',1024);
    UTL_FILE.PUT_LINE(w_file,'New line');
    UTL_FILE.FCLOSE(w_file); 
END; 

Copy

To run this example, see ORACLE UTL_FILE

Snowflake

DECLARE
    w_file OBJECT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'UTL_FILE.FILE_TYPE' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/ := OBJECT_CONSTRUCT();
    call_results VARIANT;
BEGIN
    w_file:=
    --** SSC-FDM-OR0036 - PARAMETERS: 'LOCATION, MAX_LINESIZE_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
    UTL_FILE.FOPEN_UDF('MY_DIR','test.csv','w',1024);
    --** SSC-FDM-OR0036 - PARAMETERS: 'AUTOFLUSH_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
    call_results := (
        CALL UTL_FILE.PUT_LINE_UDF(:w_file,'New line')
    );
    call_results := (
        CALL UTL_FILE.FCLOSE_UDF(:w_file)
    );
    RETURN call_results;
END;

Copy

Known Issues¶

1. Modify the procedure for changing the name of the stage.¶

The user can modify the procedure if it is necessary to change the name of the stage.

2. Location static.¶

The location used to write to this procedure is static. A new version of the procedure is expected to increase its extensibility by using the location that has the FILE parameter.

5. Files supported.¶

This procedure for now, only writes .CSV files.

Related EWIs¶

SSC-FDM-0015: Data Type Not Recognized.
SSC-FDM-OR0036: Unnecessary built-in packages parameters.

FOPEN procedure¶

Description¶

This procedure opens a file. (Oracle PL/SQL UTL_FILE.FOPEN)

This procedure is implemented using Snowflake STAGE to store the text files.

The user is in charge of uploading the local files to the STAGE to be used by the procedure.

This procedure requires to be used in conjunction with:

UTL_FILE.FCLOSE procedure

Syntax¶

UTL_FILE.FOPEN(
    LOCATION VARCHAR,
    FILENAME VARCHAR,
    OPEN_MODE VARCHAR,
    MAX_LINESIZE NUMBER,
    );

Copy

Setup data¶

The UTL_FILE schema must be created.

CREATE SCHEMA IF NOT EXISTS UTL_FILE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';

Copy

Create the stage utlfile_local_directory.

CREATE OR REPLACE FILE FORMAT  my_csv_format TYPE = csv;

CREATE OR REPLACE STAGE utlfile_local_directory
  file_format = my_csv_format;

Copy

If the value in the OPEN_MODE parameter is w or r it is necessary to upload the file in the utlfile_local_directory.

PUT file://<path_to_file>/<filename> @UTL_FILE.utlfile_local_directory auto_compress=false;

Copy

The PUT command runs in Snowflake CLI.

Custom procedure overloads¶

UTL_FILE.FOPEN( VARCHAR, VARCHAR)¶

Parameters

FILENAME: The name of the file, including extension**.**
OPEN_MODE: Specifies how the file is opened.

Open modes

The Oracle Built-in package UTL_FILE.FOPEN procedure supports six modes of how to open the file, but only three of them are supported in the Snowscripting procedure.

OPEN_MODE	DESCRIPTION	STATUS
w	Write mode	Supported
a	Append mode	Supported
r	Read mode	Supported
rb	Read byte mode	Unsupported
wb	Write byte mode	Unsupported
ab	Append byte mode	Unsupported

Functionality

This procedure uses two tables with which the operation of opening a file will be emulated. The FOPEN_TABLES table will store the files that are open and the FOPEN_TABLES_LINES table stores the lines that each file owns.

If the file is opened in write mode, a new file is created, if it is opened in read or append mode, it loads the lines of the file in FOPEN_TABLES_LINES and inserts the file in FOPEN_TABLES.

CREATE OR REPLACE PROCEDURE UTL_FILE.FOPEN_UDF(FILENAME VARCHAR,OPEN_MODE VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS $$
    DECLARE
        fhandle VARCHAR;
        key VARCHAR;
        status VARCHAR;
        File_is_not_loaded_on_stage exception;
        fileNameConcat VARCHAR:= '@UTL_FILE.utlfile_local_directory/'||:FILENAME;
        copyIntoQuery VARCHAR DEFAULT 'COPY INTO UTL_FILE.FOPEN_TABLES_LINES (FHANDLE, LINE) FROM (SELECT ? , stageFile.$1 FROM '||:fileNameConcat||' stageFile)';
    BEGIN
        CREATE TABLE IF NOT EXISTS UTL_FILE.FOPEN_TABLES 
        (
          FHANDLE VARCHAR, 
          FILENAME VARCHAR,
          OPEN_MODE VARCHAR
        );

        CREATE TABLE IF NOT EXISTS UTL_FILE.FOPEN_TABLES_LINES 
        (
          SEQ    NUMBER AUTOINCREMENT,
          FHANDLE VARCHAR, 
          LINE    VARCHAR
        );   
        SELECT FHANDLE INTO fhandle FROM UTL_FILE.FOPEN_TABLES WHERE FILENAME = :FILENAME;
        SELECT UUID_STRING() INTO key;
        IF (OPEN_MODE = 'w') THEN
            INSERT INTO UTL_FILE.FOPEN_TABLES(FHANDLE, FILENAME, OPEN_MODE) VALUES(:key,:FILENAME,:OPEN_MODE);
            RETURN TO_JSON({ 'name': FILENAME, 'handle': key});
        ELSE
            IF (fhandle IS NULL) THEN
                EXECUTE IMMEDIATE :copyIntoQuery USING (key);
                SELECT OBJECT_CONSTRUCT(*):status INTO status FROM table(result_scan(last_query_id()));
                IF (status = 'LOADED') THEN
                    INSERT INTO UTL_FILE.FOPEN_TABLES(FHANDLE, FILENAME, OPEN_MODE) VALUES(:key,:FILENAME,:OPEN_MODE);
                    RETURN TO_JSON({'name': FILENAME, 'handle': key});
                ELSE
                    raise File_is_not_loaded_on_stage;
                END IF;
            ELSE
                UPDATE UTL_FILE.FOPEN_TABLES SET OPEN_MODE = :OPEN_MODE WHERE FHANDLE = :fhandle;
                RETURN TO_JSON({'name': FILENAME, 'handle': fhandle});
           END IF;
        END IF;     
    END
$$;

Copy

Note:

Note that this procedure uses the stage that was created previously. For now, if you want to use another name for the stage, you must modify the procedure.
These procedures are implemented for the internal stages in the COPY INTO

Usage example¶

Oracle

DECLARE 
    w_file UTL_FILE.FILE_TYPE;
BEGIN
    w_file:= UTL_FILE.FOPEN('MY_DIR','test.csv','w',1024);
END;

Copy

To run this example, see ORACLE UTL_FILE

Snowflake

DECLARE
    w_file OBJECT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'UTL_FILE.FILE_TYPE' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/ := OBJECT_CONSTRUCT();
BEGIN
    w_file:=
    --** SSC-FDM-OR0036 - PARAMETERS: 'LOCATION, MAX_LINESIZE_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
    UTL_FILE.FOPEN_UDF('MY_DIR','test.csv','w',1024);
END;

Copy

Known Issues¶

1. Modify the procedure for changing the name of the stage.¶

The user can modify the procedure if it is necessary to change the name of the stage.

2. `LOCATION` parameter is not used.¶

The LOCATION parameter is not used now because the stage used in the procedure is static. It is planned for an updated version of the procedure to increase its extensibility by using this parameter to enter the name of the stage where the file you want to open is located.

3. `MAX_LINESIZE` parameter is not used¶

The Oracle Built-in package UTL_FILE.FOPEN procedure has the MAX_LINESIZE parameter, but in the Snowscripting procedure it is removed because it is not used.

4. `OPEN_MODE` values supported¶

This procedure supports write (w), read (r), and append (a) modes to open files.

5. Files supported¶

This procedure for now, only supports .CSV files.

Related EWIs¶

SSC-FDM-0015: Data Type Not Recognized.
SSC-FDM-OR0036: UnnecessaryBuiltInPackagesParameters

PUT_LINE procedure¶

Description¶

This procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. (Oracle PL/SQL UTL_FILE.PUT_LINE)

This procedure requires to be used in conjunction with:

UTL_FILE.FOPEN procedure
UTL_FILE.FCLOSE procedure

Syntax¶

UTL_FILE.PUT_LINE(
    FILE VARCHAR,
    BUFFER VARCHAR,
    );

Copy

Setup data¶

The UTL_FILE schema must be created.

CREATE SCHEMA IF NOT EXISTS UTL_FILE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}';

Copy

Custom UDF¶

UTL_FILE.PUT_LINE(VARCHAR, VARCHAR)¶

Parameters

FILE: Active file handler returned from the call to UTL_FILE.FOPEN
BUFFER: Text buffer that contains the text to be written to the file**.**

Functionality

This procedure uses the FOPEN_TABLES_LINES table created in the UTL_FILE.FOPEN procedure.

If the OPEN_MODE of the file is write (w) or append (a), it inserts the buffer into FOPEN_TABLES_LINES, but if the OPEN_MODE is read (r), it throws the File_is_read_only exception.

CREATE OR REPLACE PROCEDURE UTL_FILE.PUT_LINE_UDF(FILE VARCHAR,BUFFER VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS $$
    DECLARE 
        openMode VARCHAR;
        openModeTemp VARCHAR;
        fhandle VARCHAR;
        fileParse VARIANT;
        File_is_read_only exception;
    BEGIN
        fileParse:= PARSE_JSON(FILE);
        fhandle:= :fileParse:handle;
        SELECT OPEN_MODE INTO openModeTemp FROM UTL_FILE.FOPEN_TABLES WHERE FHANDLE = :fhandle; 
        IF (openModeTemp = 'a' or openModeTemp = 'w') THEN
            INSERT INTO UTL_FILE.FOPEN_TABLES_LINES(FHANDLE,LINE) VALUES(:fhandle,:BUFFER);
        ELSE  
            raise File_is_read_only;
        END IF;
    END
$$;  

-- This SELECT is manually added and not generated by Snowconvert
SELECT * FROM UTL_FILE.FOPEN_TABLES_LINES;

Copy

Note:

To use this procedure you must open the file with UTL_FILE.FOPEN

Usage example¶

Oracle

DECLARE
    w_file UTL_FILE.FILE_TYPE;
BEGIN
    w_file:= UTL_FILE.FOPEN('MY_DIR','test.csv','w',1024);
    UTL_FILE.PUT_LINE(w_file,'New line');
END;

Copy

To run this example, see ORACLE UTL_FILE

Snowflake

DECLARE
    w_file OBJECT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'UTL_FILE.FILE_TYPE' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/ := OBJECT_CONSTRUCT();
    call_results VARIANT;
BEGIN
    w_file:=
    --** SSC-FDM-OR0036 - PARAMETERS: 'LOCATION, MAX_LINESIZE_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
    UTL_FILE.FOPEN_UDF('MY_DIR','test.csv','w',1024);
    --** SSC-FDM-OR0036 - PARAMETERS: 'AUTOFLUSH_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
    call_results := (
        CALL UTL_FILE.PUT_LINE_UDF(:w_file,'New line')
    );
    RETURN call_results;
END;

Copy

Known Issues¶

1. `AUTOFLUSH` parameter is not used.¶

The Oracle Built-in package UTL_FILE.PUT_LINE procedure has the AUTOFLUSH parameter, but in the Snowscripting procedure it is removed because it is not used.

Related EWIs¶

SSC-FDM-0015: Data Type Not Recognized.
SSC-FDM-OR0036: UnnecessaryBuiltInPackagesParameters

SnowConvert: Oracle Built-In packages¶

Description¶

DBMS_RANDOM¶

Description¶

VALUE functions¶

Description¶

Syntax¶

Custom UDF overloads¶

Setup data¶

DBMS_RANDOM.VALUE()¶

Usage example¶

DBMS_RANDOM.VALUE(NUMBER, NUMBER)¶

Usage example¶

Known Issues¶

Related EWIs¶

DBMS_OUTPUT¶

Description¶

PUT_LINE procedure¶

Description¶

Syntax¶

Custom procedure¶

Setup data¶

DBMS_OUTPUT.PUT_LINE(VARCHAR)¶

Usage example¶

Known Issues¶

Related EWIs¶

DBMS_LOB¶

Description¶

SUBSTR Function¶

Description¶

Syntax¶

Function overloads¶

DBMS_LOB.SUBSTR(‘string’, amount, offset)¶

DBMS_LOB.SUBSTR(BLOB, amount, offset)¶

DBMS_LOB.SUBSTR(CLOB, amount, offset)¶

DBMS_LOB.SUBSTR(BFILE, amount, offset)¶

Known Issues¶

1. Using DBMS_LOB.SUBSTR with BFILE columns¶

Related EWIs¶

UTL_FILE¶

Description¶

FCLOSE procedure¶

Description¶

Syntax¶

Setup data¶

Custom procedure overloads¶

UTL_FILE.FCLOSE(VARCHAR)¶

Usage example¶

Known Issues¶

1. Modify the procedure for changing the name of the stage.¶

2. Location static.¶

5. Files supported.¶

Related EWIs¶

FOPEN procedure¶

Description¶

Syntax¶

Setup data¶

Custom procedure overloads¶

UTL_FILE.FOPEN( VARCHAR, VARCHAR)¶

Usage example¶

Known Issues¶

1. Modify the procedure for changing the name of the stage.¶

2. LOCATION parameter is not used.¶

3. MAX_LINESIZE parameter is not used¶

4. OPEN_MODE values supported¶

5. Files supported¶

Related EWIs¶

PUT_LINE procedure¶

Description¶

Syntax¶

Setup data¶

Custom UDF¶

UTL_FILE.PUT_LINE(VARCHAR, VARCHAR)¶

Usage example¶

Known Issues¶

1. AUTOFLUSH parameter is not used.¶

Related EWIs¶

2. `LOCATION` parameter is not used.¶

3. `MAX_LINESIZE` parameter is not used¶

4. `OPEN_MODE` values supported¶

1. `AUTOFLUSH` parameter is not used.¶