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
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.

UDF

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

Query

IN -> Oracle_01.sql

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

OUT -> Oracle_01.sql

DECLARE
    w_file OBJECT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'UTL_FILE.FILE_TYPE' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/ := 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.

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. SSC-FDM-0015: Data Type Not Recognized.

2. SSC-FDM-OR0036: Unnecessary built-in packages parameters.