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.
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
————————– ———– ——————————
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,
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
v_flag BOOLEAN := TRUE;
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.’);
v_file := utl_file.fopen(v_path,p_filename,’r’);
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20005,’Invalid Path : ‘ || v_path);
WHEN OTHERS THEN
INSERT INTO INPT_CITY (INPT_CITY_ID,CITY,STATE,ZIP,COUNTRY)
WHEN NO_DATA_FOUND THEN
v_flag := FALSE;
WHEN OTHERS THEN
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>SELECT city,state,zip,country FROM inpt_city;
Sorry, the comment form is closed at this time.