FCLOSE procedure

Translation reference for UTL_FILE.FCLOSE.

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

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

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

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

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

UDF

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

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

Query

IN -> Oracle_01.sql

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; 

To run this example, see ORACLE UTL_FILE

Snowflake

Query

OUT -> Oracle_01.sql

DECLARE
    w_file OBJECT /*** SSC-FDM-0015 - DATA TYPE 'UTL_FILE.FILE_TYPE' NOT RECOGNIZED ***/ := OBJECT_CONSTRUCT();
    call_results VARIANT;
BEGIN
    w_file:=
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0120 - PARAMETERS: 'LOCATION, MAX_LINESIZE_UDF' UNNECESSARY IN THE IMPLEMENTATION. ***/!!!
    UTL_FILE.FOPEN_UDF('MY_DIR','test.csv','w',1024);
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0120 - 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;

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.

1. SSC-EWI-0020: CUSTOM UDF INSERTED.
2. SSC-FDM-0015: Data Type Not Recognized.