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)

This procedure requires to be used in conjunction with:

• UTL_FILE.FOPEN procedure

• UTL_FILE.FCLOSE procedure

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.

UDF

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

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');
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' 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.

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

1. MSCEWI1020: CUSTOM UDF 'UTL_FILE.PUT_LINE' INSERTED.
2. MSCEWI3120: PARAMETERS: 'AUTOFLUSH' UNNECESSARY IN THE IMPLEMENTATION.