PUT_LINE procedure
Translation reference for UTL_FILE.PUT_LINE.
Description
This procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. (Oracle PL/SQL UTL_FILE.PUT_LINE)
Syntax
UTL_FILE.PUT_LINE(
FILE VARCHAR,
BUFFER 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"}}';
Custom UDF
UTL_FILE.PUT_LINE(VARCHAR, VARCHAR)
Parameters
FILE: Active file handler returned from the call to
UTL_FILE.FOPEN
BUFFER: Text buffer that contains the text to be written to the file.
Functionality
This procedure uses the FOPEN_TABLES_LINES
table created in the UTL_FILE.FOPEN
procedure.
If the OPEN_MODE
of the file is write (w) or append (a), it inserts the buffer into FOPEN_TABLES_LINES
, but if the OPEN_MODE
is read (r), it throws the File_is_read_only
exception.
CREATE OR REPLACE PROCEDURE UTL_FILE.PUT_LINE_UDF(FILE VARCHAR,BUFFER VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS $$
DECLARE
openMode VARCHAR;
openModeTemp VARCHAR;
fhandle VARCHAR;
fileParse VARIANT;
File_is_read_only exception;
BEGIN
fileParse:= PARSE_JSON(FILE);
fhandle:= :fileParse:handle;
SELECT OPEN_MODE INTO openModeTemp FROM UTL_FILE.FOPEN_TABLES WHERE FHANDLE = :fhandle;
IF (openModeTemp = 'a' or openModeTemp = 'w') THEN
INSERT INTO UTL_FILE.FOPEN_TABLES_LINES(FHANDLE,LINE) VALUES(:fhandle,:BUFFER);
ELSE
raise File_is_read_only;
END IF;
END
$$;
-- This SELECT is manually added and not generated by Snowconvert
SELECT * FROM UTL_FILE.FOPEN_TABLES_LINES;
Note:
To use this procedure you must open the file with UTL_FILE.FOPEN
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');
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:=
--** SSC-FDM-OR0036 - PARAMETERS: 'LOCATION, MAX_LINESIZE_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
UTL_FILE.FOPEN_UDF('MY_DIR','test.csv','w',1024);
--** SSC-FDM-OR0036 - PARAMETERS: 'AUTOFLUSH_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
call_results := (
CALL UTL_FILE.PUT_LINE_UDF(:w_file,'New line')
);
RETURN call_results;
END;
Known Issues
1. AUTOFLUSH
parameter is not used.
AUTOFLUSH
parameter is not used. The Oracle Built-in package UTL_FILE.PUT_LINE
procedure has the AUTOFLUSH
parameter, but in the Snowscripting procedure it is removed because it is not used.
SSC-EWI-0020: CUSTOM UDF INSERTED.
SSC-FDM-0015: Data Type Not Recognized.
SSC-FDM-OR0036: Unnecessary built-in packages parameters.
Last updated