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;
Custom UDF
UTL_FILE.PUT_LINE_UDF(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 SCHEMA IF NOT EXISTS UTL_FILE;
CREATE OR REPLACE PROCEDURE UTL_FILE.PUT_LINE_UDF(FILE VARCHAR,BUFFER VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
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
$$;
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
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' INSERTED. ***/
/*** MSC-WARNING - MSCEWI3120 - PARAMETERS: 'LOCATION, MAX_LINESIZE' UNNECESSARY IN THE IMPLEMENTATION. ***/
CALL UTL_FILE.F_OPEN('test.csv','w');
SELECT
*
INTO
file_data
FROM
TABLE(RESULT_SCAN(LAST_QUERY_ID()));
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'UTL_FILE.PUT_LINE' INSERTED. ***/
/*** MSC-WARNING - MSCEWI3120 - PARAMETERS: 'AUTOFLUSH' UNNECESSARY IN THE IMPLEMENTATION. ***/
CALL UTL_FILE.PUT_LINE(:file_data,'New line');
END
$$;
CALL PROC();
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.
Related EWIs
MSCEWI1020: CUSTOM UDF 'UTL_FILE.PUT_LINE' INSERTED.
MSCEWI3120: PARAMETERS: 'AUTOFLUSH' UNNECESSARY IN THE IMPLEMENTATION.
Last updated
Was this helpful?