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.
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
.
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
$$;
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;
To run this example, see ORACLE UTL_FILE
Snowflake
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.
SSC-EWI-0020: CUSTOM UDF INSERTED.
SSC-FDM-0015: Data Type Not Recognized.
Last updated