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:

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;

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

Custom procedure overloads

UTL_FILE.F_OPEN_UDF( 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_MODEDESCRIPTIONSTATUS

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.

UDF
CREATE OR REPLACE PROCEDURE UTL_FILE.FOPEN_UDF(FILENAME VARCHAR,OPEN_MODE VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
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
$$;


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

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;

To run this example, see ORACLE UTL_FILE

Snowflake 

CREATE OR REPLACE PROCEDURE PROC()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
    file_data  VARIANT;
   BEGIN
   /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'UTL_FILE.FOPEN' INSERTED. ***/
   /*** MSC-WARNING - MSCEWI3120 - PARAMETERS: 'LOCATION, MAX_LINESIZE' UNNECESSARY IN THE IMPLEMENTATION. ***/
    CALL UTL_FILE.F_OPEN('test.csv','a');
    SELECT
      *
    INTO
      file_data
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));
   END
$$;

CALL PROC();

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 a new 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

  1. MSCEWI1020: CUSTOM UDF 'UTL_FILE.FOPEN' INSERTED.

  2. MSCEWI3120: PARAMETERS: 'LOCATION, MAX_LINESIZE' UNNECESSARY IN THE IMPLEMENTATION.

PreviousUTL_FILENextPUT_LINE procedure

Last updated