utl-file

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

utl-file [2015/09/23 20:27] (current)
Line 1: Line 1:
 +====== UTL_FILE Package - PL/HQL Reference ======
  
 +UTL_FILE package allows you to read and write HDFS files:
 +
 +**Example:​**
 +
 +<code language="​sql">​
 +DECLARE
 +  f UTL_FILE.FILE_TYPE;​
 +BEGIN
 +  f := UTL_FILE.FOPEN('/​user/​dm',​ '​hello.txt',​ '​w'​);​
 +  UTL_FILE.PUT_LINE(f,​ '​Hello,​ world!'​);​
 +  UTL_FILE.FCLOSE(f);​
 +END;
 +</​code> ​
 +
 +In this example we create ///​user/​dm/​hello.txt//​ in HDFS, write a single line and close the file. 
 +
 +===== UTL_FILE.FILE_TYPE Type =====
 +
 +Before you can work with a file using UTL_FILE package you have to declare a variable of UTL_FILE.FILE_TYPE. You assign a value to this variable using UTL_FILE.FOPEN function and then use it in all other functions as an argument. ​
 +
 +Declaring UTL_FILE.FILE_TYPE variables:
 +
 +<code language="​sql">​
 +DECLARE
 +  f UTL_FILE.FILE_TYPE;​
 +  f2 UTL_FILE.FILE_TYPE;​
 +</​code>​
 +
 +===== FOPEN Function =====
 +
 +FOPEN function opens a file. 
 +
 +**Syntax:**
 +
 +<​code>​
 +file_handle:​ = FOPEN(directory,​ file, mode);
 +</​code>​
 +
 +**Parameters:​**
 +
 +| **Parameter** | **Type** | **Description** |
 +| directory | VARCHAR | Directory name | 
 +| file | VARCHAR | File name | 
 +| mode | VARCHAR | Open mode: '​w'​ | 
 +
 +**File Open Modes:**
 +
 +  * '​r'​ - Open file for reading. ​
 +  * '​w'​ - Open file for writing. If the file does not exist it is created. If the file already exists its content is overwritten. ​
 +
 +**Return Value:**
 +
 +FOPEN returns UTL_FILE.FILE_TYPE file handle that you have to pass to all other functions operating on the file.
 +
 +**Example:​**
 +
 +<code language="​sql">​
 +DECLARE
 +  f UTL_FILE.FILE_TYPE;​
 +BEGIN
 +  f := UTL_FILE.FOPEN('/​user/​dm',​ '​hello.txt',​ '​w'​);​
 +END;
 +</​code>​
 +
 +===== GET_LINE Function =====
 +
 +GET_LINE function reads a text string from an open file. The function reads until a new line (not included to the output string), end of file or the specified maximum length. ​
 +
 +**Syntax:**
 +
 +<​code>​
 +UTL_FILE.GET_LINE(file_handle,​ var [, len]);
 +</​code>​
 +
 +**Parameters:​**
 +
 +| **Parameter** | **Type** | **Description** |
 +| file_handle | UTL_FILE.FILE_TYPE | Open file handle | 
 +| var | VARCHAR | Variable to store the line | 
 +| len | INT | Maximum line length, optional | 
 +
 +**Return Value:**
 +
 +No.
 +
 +**Example:​**
 +
 +<code language="​sql">​
 +DECLARE
 +  f UTL_FILE.FILE_TYPE;​
 +  s VARCHAR(100);​
 +BEGIN
 +  f := UTL_FILE.FOPEN('/​user/​dm',​ '​hello.txt',​ '​r'​);​
 +  UTL_FILE.GET_LINE(f,​ s, 100);
 +END;
 +</​code>​
 +
 +===== PUT_LINE Function =====
 +
 +PUT_LINE function writes a text string to the file. The function appends a line terminator. ​
 +
 +**Syntax:**
 +
 +<​code>​
 +UTL_FILE.PUT_LINE(file_handle,​ text);
 +</​code>​
 +
 +**Parameters:​**
 +
 +| **Parameter** | **Type** | **Description** |
 +| file_handle | UTL_FILE.FILE_TYPE | Open file handle | 
 +| text | VARCHAR | Text string to write | 
 +
 +**Return Value:**
 +
 +No.
 +
 +**Example:​**
 +
 +<code language="​sql">​
 +DECLARE
 +  f UTL_FILE.FILE_TYPE;​
 +BEGIN
 +  f := UTL_FILE.FOPEN('/​user/​dm',​ '​hello.txt',​ '​w'​);​
 +  UTL_FILE.PUT_LINE(f,​ '​Hello,​ world!'​);​
 +END;
 +</​code>​
 +===== PUT Function =====
 +
 +PUT_LINE function writes a text string to the file. Unlike PUT_LINE this function **does not** append a line terminator. ​
 +
 +**Syntax:**
 +
 +<​code>​
 +UTL_FILE.PUT(file_handle,​ text);
 +</​code>​
 +
 +**Parameters:​**
 +
 +| **Parameter** | **Type** | **Description** |
 +| file_handle | UTL_FILE.FILE_TYPE | Open file handle | 
 +| text | VARCHAR | Text string to write | 
 +
 +**Return Value:**
 +
 +No.
 +
 +**Example:​**
 +
 +<code language="​sql">​
 +DECLARE
 +  f UTL_FILE.FILE_TYPE;​
 +BEGIN
 +  f := UTL_FILE.FOPEN('/​user/​dm',​ '​hello.txt',​ '​w'​);​
 +  UTL_FILE.PUT(f,​ '​Hello,​ world!'​);​
 +END;
 +</​code>​
 +
 +===== FCLOSE Function =====
 +
 +FCLOSE function closes an open file identified by a file handle. ​
 +
 +**Syntax:**
 +
 +<​code>​
 +UTL_FILE.FCLOSE(file_handle);​
 +</​code>​
 +
 +**Parameters:​**
 +
 +| **Parameter** | **Type** | **Description** |
 +| file_handle | UTL_FILE.FILE_TYPE | Open file handle | 
 +
 +**Return Value:**
 +
 +No.
 +
 +**Example:​**
 +
 +<code language="​sql">​
 +DECLARE
 +  f UTL_FILE.FILE_TYPE;​
 +BEGIN
 +  f := UTL_FILE.FOPEN('/​user/​dm',​ '​hello.txt',​ '​w'​);​
 +  UTL_FILE.FCLOSE(f);​
 +END;
 +</​code>​