Systems Engineering and RDBMS

UTL_FILE

Posted by decipherinfosys on April 9, 2007

There are various ways to load the data from a flat file. Most of the time, SQL*Loader is used to load the data because of the flexibility and performance. But at times, you can run into situations where using SQL*Loader may not be the right choice because of any transformations that might be required on the data, security etc.. In those scenarios, we can load the data using PL/SQL. There are few different ways to load data using PL/SQL – UTL_FILE, external tables etc.. We will go through each of them one by one.  In this blog, we will talk about UTL_FILE package.

UTL_FILE package

PL/SQL programs use UTL_FILE package to read and write operating system text files. It provides a version of standard file operations open, close, get and put.

In order to use UTL_FILE package, we have to make sure that utl_file_dir parameter is set correctly.  Login to SQL*Plus and check the value of the parameter using following command.

SQL> show parameter utl_file

NAME                       TYPE        VALUE
————————– ———– ——————————
utl_file_dir               string

It indicates that value of the parameter is not set. Value can be either ‘*’ or a specific path on the database server where file can be put for subsequent use. When value is defined as ‘*’, you can put file in any folder on the database server. Procedure or any other program unit has to refer to the path correctly.  For our testing purpose, we will put it under user_dump_dest. In our case, location is D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP.

Since parameter is not set, we will set it first. We are using spfile so we will use following syntax. Once change is done, we will have to bounce the database instance to make this change permanent.

SQL> ALTER SYSTEM SET UTL_FILE_DIR=’D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP’ SCOPE=SPFILE;

Let us first create the fixed length format text file. Following is the structure of the file. It contains four fields City, State, Zip and Country. Save following content in the file and save it as city.txt in D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP folder.

ATLANTA     GA 30318 USA
MARIETTA    GA 30067 USA
DULUTH      GA 30097 USA
JOHNS CREEK GA 30005 USA
ROSWELL     GA 30021 USA

Now let us create table to hold the data.

CREATE TABLE INPT_CITY
(
INPT_CITY_ID    NUMBER(9) NOT NULL,
CITY            VARCHAR2(30) NOT NULL,
STATE            VARCHAR2(20),
ZIP            VARCHAR2(10),
COUNTRY        VARCHAR2(30),
CONSTRAINT PK_INPT_CITY PRIMARY KEY(INPT_CITY_ID)
)
/

CREATE SEQUENCE INPT_CITY_SEQ ICREMENT BY 1
START WITH 1
/

Create following stored procedure. We will use stored procedure to read data from the text file and load it into the table.

CREATE OR REPLACE PROCEDURE TEST_UTL_FILE
(
P_FileName IN VARCHAR2
)
AS
v_file UTL_FILE.FILE_TYPE;
v_data_string VARCHAR2(100);
v_filename      VARCHAR2(25);
v_flag   BOOLEAN := TRUE;
v_path   VARCHAR2(512);
BEGIN

SELECT value
INTO v_path
FROM v$parameter
WHERE name = ‘utl_file_dir’;

IF (NVL(v_path,’ ‘) = ‘ ‘) OR v_PATH=’*’ THEN
RAISE_APPLICATION_ERROR(-20020,’File Path Not Specified in UTL_FILE_DIR parameter.’);
END IF;

BEGIN
v_file := utl_file.fopen(v_path,p_filename,’r’);
EXCEPTION
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20005,’Invalid Path : ‘ || v_path);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20009,SQLERRM);
END;

WHILE (v_flag)
LOOP
BEGIN
utl_file.get_line(v_file,v_data_string);
INSERT INTO INPT_CITY (INPT_CITY_ID,CITY,STATE,ZIP,COUNTRY)
VALUES(INPT_CITY_SEQ.NEXTVAL, SUBSTR(v_data_string,1,12),
SUBSTR(v_data_string,13,2), SUBSTR(v_data_string,16,5),
SUBSTR(v_data_string,22,3));
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_flag := FALSE;
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20010,SQLERRM);
END;
END LOOP;

utl_file.fclose(v_file);

COMMIT;

END;
/

Please make sure that you have appropriate permission on dynamic performance views (V$) otherwise you may run into error of ‘Table or view does not exist’.

In above procedure, we are using
fopen: to open the specific file.
get_line: to read from the file one line at a time.
fclose: to close the file.

Similarly, we can use put_line to write to the flat file. Let us execute procedure now to load the data. Command is followed by select statement. Select statement should return data from the inpt_city table.

SQL>exec TEST_UTL_FILE(‘city.txt’);

SQL>SELECT city,state,zip,country FROM inpt_city;

Sorry, the comment form is closed at this time.

 
%d bloggers like this: