FOPEN procedure

Translation reference for UTL_FILE.FOPEN.

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

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

• 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;

• 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;

• 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.

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.

UDF

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

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