Read a file word by word using DBMS_LOB
Oracle offers several possibilities to process file from within PL/SQL. The most used package is UTL_FILE, but with the disadvantage that the read-buffer is limited to 1023 bytes. If you want to read huge chunks of files you can use the DBMS_LOB package, even for the processing of plain ASCII files.
There are two solutions to read a file with DBMS_LOB
Suppose we want to read a big file word by word directly from PL/SQL without saving the whole file in a table column. The words in the file are separated with a blank. For simplicity we assume, that there is exactly one blank between the words and the file is a stream with a newline at the end of the file.
First we have to create an ORACLE directory as the schema owner. Do not add a trailing "/" at the end of the directory path.
Next we create the procedure READ_FILE_LOB, which reads the file word by word.
CREATE OR REPLACE
-- Input Directory as specified in create directory
-- Input File which is read word by word
-- Separator Character between words is a BLANK (ascii =
-- Character at the end of the file is NEWLINE (ascii =
-- Pointer to the BFILE
-- Current position in the file (file begins at position
-- Amount of characters have been read
-- Read Buffer
-- End of the current word which will be read
-- Return value
-- Mapping the physical file with the pointer
to the BFILE
-- Check if the file exists
-- Open the file in
Calculate the end of the current word
The file testfile.txt has the following content
martin zahn seftigen
Output of the procedure