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;

• 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_UDF(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
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

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

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_UDF' INSERTED. ***/
    /*** MSC-WARNING - MSCEWI3120 - PARAMETERS: 'LOCATION, MAX_LINESIZE' UNNECESSARY IN THE IMPLEMENTATION. ***/
    CALL UTL_FILE.FOPEN_UDF('test.csv','w');
    SELECT
      *
    INTO
      file_data
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'UTL_FILE.PUT_LINE_UDF' INSERTED. ***/
    /*** MSC-WARNING - MSCEWI3120 - PARAMETERS: 'AUTOFLUSH' UNNECESSARY IN THE IMPLEMENTATION. ***/
    CALL UTL_FILE.PUT_LINE_UDF(:file_data,'New line');
    /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'UTL_FILE.FCLOSE_UDF' INSERTED. ***/
    CALL UTL_FILE.FCLOSE_UDF(:file_data);
   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 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

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