Home » SQL & PL/SQL » SQL & PL/SQL » utl_file and parsing a file
utl_file and parsing a file [message #72] Thu, 10 January 2002 11:31 Go to next message
Jacky
Messages: 3
Registered: January 2002
Junior Member
I need to know how to parse a file read from my UNIX server using utl_file. Am I supposed to do this when I do utl_file.get_line? In a nutshell, I want to read the file on UNIX, which is fixed length (5 fields). I need to read in the first field, then do a select on a table based on this field. Then all 5 fields will be inserted into a different table. I'm new to this so any help would be appreciated.
Re: utl_file and parsing a file [message #74 is a reply to message #72] Thu, 10 January 2002 12:38 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member

look at this example:
  my script reads line by line from text file and queries emp table based on first field of file, 
then inserts retrieved value and second field of text file into emp1 table.

in this example, i used only 2 fields.

My data file contents:

12345          ORACLE 
12222          SQL   

  

EMP table data;

SQL> select * from emp;

     EMPNO ENAME                       SAL HDATE                   DEPTNO
---------- -------------------- ---------- ------------------- ----------
     12345 SURESH                     1000 08/09/2001 17:25:17         10
     12222 john                       1200 08/09/2001 17:25:37         10

emp table structure

SQL> desc emp
 Name                                            Null?    Type
 ----------------------------------------------- -------- --------------------
 EMPNO                                                    NUMBER
 ENAME                                                    VARCHAR2(20)
 SAL                                                      NUMBER
 HDATE                                                    DATE
 DEPTNO                                                   NUMBER(10)

SQL> desc emp1
 Name                                            Null?    Type
 ----------------------------------------------- -------- ----------------
 ENAME                                                    VARCHAR2(15)
 SUBJ                                                     VARCHAR2(15)

my script:

DECLARE
fileHandler UTL_FILE.FILE_TYPE;
l_str varchar2(1000);
l_f1 varchar2(15);
l_f2 varchar2(15);
l_ename varchar2(15);
BEGIN
fileHandler := UTL_FILE.FOPEN('c:suresh', 'emp.txt', 'r');
loop
utl_file.get_line(filehandler,l_str);
dbms_output.put_line(l_str);
l_f1 := substr(l_str,1,15);
l_f2:= substr(l_str,16,15);
execute immediate 'SELECT ename from emp where empno='||l_f1 into l_ename;
dbms_output.put_line(l_ename);
insert into emp1 values (l_ename,l_f2);

end loop;
utl_file.fclose(fileHandler);

EXCEPTION
When no_data_found THEN
UTL_FILE.FCLOSE(fileHandler);
WHEN utl_file.invalid_path THEN
DBMS_OUTPUT.PUT_LINE('Error: invalid path ' || SQLERRM);
UTL_FILE.FCLOSE(fileHandler);
WHEN utl_file.write_error THEN
DBMS_OUTPUT.PUT_LINE('Error: write error ' || SQLERRM);
UTL_FILE.FCLOSE(fileHandler);
WHEN others then
DBMS_OUTPUT.PUT_LINE('Error: other error ' || SQLERRM);
UTL_FILE.FCLOSE(fileHandler);
END;
/

SQL> @c:utl1.sql

PL/SQL procedure successfully completed.

SQL> select * from emp1;

ENAME           SUBJ
--------------- ---------------
SURESH          ORACLE
john            SQL

Previous Topic: error
Next Topic: Basic Question
Goto Forum:
  


Current Time: Tue Mar 31 11:51:50 CDT 2020