Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need Help: pl/sql loop using UTL_FILE reading and parsing to text file 2

900395Nov 15 2011 — edited Nov 16 2011
Hello, I am working on a new project using the UTL_FILE utility in oracle 11g. and I was wondering if someone could help me with some pl/sql code I can't figure out.
My goal is to open and and read from a file1.txt file_type then
copy a specific paragraph from that file1.txt where
the first word in a specific line in the file1.txt begins with 'foo' and where the last word in file1.txt ends with 'ZEN' Then
write to file2.txt only the parse section in my where statement.
I am not sure if this is possible but any guidance will be greatly appreciate!

My example code:

CREATE OR REPLACE PROCEDURE my_app2 IS
InFile utl_file.file_type;
OutFile utl_file.file_type;
buffer VARCHAR2(30000);
BEGIN
-- open a file to read
InFile := utl_file.fopen('TEST_DIR', 'mytst.txt','r');
-- open a file to write
OutFile := utl_file.fopen('TEST_DIR', 'out.txt', 'w');

-- check file is opened
IF utl_file.is_open(InFile) THEN
-- loop lines in the file
LOOP
BEGIN
utl_file.get_line(InFile, buffer );
--write to out.txt
utl_file.put_line(OutFile, buffer, FALSE);
utl_file.fflush(OutFile);

EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
END IF;
utl_file.fclose(InFile);
utl_file.fclose(OutFile);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END my_app2l;
/
This post has been answered by Anirudha Dhopate on Nov 15 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 14 2011
Added on Nov 15 2011
10 comments
20,865 views