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.
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 theutlfile_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.
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
.
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
$$;
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
DECLARE
w_file OBJECT /*** SSC-FDM-0015 - DATA TYPE 'UTL_FILE.FILE_TYPE' NOT RECOGNIZED ***/ := OBJECT_CONSTRUCT();
BEGIN
w_file:=
--** SSC-FDM-OR0036 - PARAMETERS: 'LOCATION, MAX_LINESIZE_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
UTL_FILE.FOPEN_UDF('MY_DIR','test.csv','w',1024);
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
parameter is not used.
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 an updated 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.
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.
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.
SSC-EWI-0020: CUSTOM UDF INSERTED.
SSC-FDM-0015: Data Type Not Recognized.
SSC-FDM-OR0036: UnnecessaryBuiltInPackagesParameters
Last updated