EXPERT RESPONSE
There are several ways to get text data into an Oracle database. As usual, which one you use is really determined by what you are trying to do. Three options you can use are SQL*Loader, External Tables and UTL_FILE. Since you specifically asked about doing it in PL/SQL I will talk about UTL_FILE below, but check into both of the others as they might also work for your needs.
UTL_FILE is a utility package available since Oracle 7.3. Until Oracle 10g, UTL_FILE operated on text files only. 10g added all kinds of goodies including the ability to manipulate binary files.
The steps you need to take to use this package are as follows:
- Create a directory (in the database)
- Open the file
- Loop through the file
- Read records
- Insert the data
- Close the file
So let's walk through this showing the syntax at each step.
CREATE OR REPLACE DIRECTORY my_text_files AS 'c:temp';
This creates an internal directory where you will place your files. C:temp is a directory on the server, not on the client. It will, of course, take the format of the host OS, i.e., on Unix it would look like '/tmp'.
Below is a pseudo code listing showing the remaining steps of using UTL_FILE. It will not compile as is but can be easily modified to do so. You can visit tahiti.oracle.com and read the PL/SQL Packages and Types Reference for the exact syntax to these and other procedures in the UTL_FILE package.
DECLARE
fh UTL_FILE.file_type;
v_buffer VARCHAR2(1024);
BEGIN
fh := UTL_FILE.fopen('MY_TEXT_FILES', 'THE_FILE_NAME', 'R', 1024);
LOOP
UTL_FILE.get_line(fh, v_buffer );
INSERT INTO my_table (field1, field2, field3 )
VALUES (substr(v_buffer,1,100), substr(v_buffer,101,250), substr(v_buffer, 351 ) );
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
utl_file.fclose(fh);
END;
We opened our file, looped through the data and substringed it out so that we could insert it. When UTL_FILE hits the end of the file, it raises NO_DATA_FOUND. In the exception handler, we closed the file. That's about it.
|
