UTL_FILE.FOPEN_UDF (VARCHAR, VARCHAR, VARCHAR)

Definition

This user-defined function (UDF) is used to replicate the functionality of the Oracle UTL_FILE_FOPEN procedure.

UTL_FILE.FOPEN_UDF(PACKAGE_VARIABLE VARCHAR, FILENAME VARCHAR, OPEN_MODE VARCHAR)

Parameters

PACKAGE_VARIABLE VARCHAR

The variable related to the file opening.

FILENAME VARCHAR

The file to be opened.

OPEN_MODE VARCHAR

Indicates de mode on which the file will be available.

Returns

Returns a varchar with the result.

Usage example

The UTL_FILE.FOPEN_UDF allows to open a .csv file. To access the file it is required to create a stage for the file and use the Snowflakr CLI to upload it.

Input:

  
CREATE OR REPLACE PROCEDURE PROC()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
    file_data  VARIANT;
   BEGIN
   
    CALL UTL_FILE.FOPEN_UDF(NULL, 'test2.csv','a');
    
    SELECT
      *
    INTO
      file_data
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));

    CALL UTL_FILE.PUT_LINE_UDF(:file_data,'New line');


    CALL UTL_FILE.FCLOSE_UDF(:file_data);
 
      
   END
$$;

CALL PROC();

Output:

null