Problem solve Get help with specific problems with your technologies, process and projects.

Loading data into Oracle

I need a step-by-step procedure for importing text to an Oracle database using a stored procedure.

I need a step-by-step procedure for importing text to an Oracle database using a stored procedure.

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.

  fh UTL_FILE.file_type;

  v_buffer VARCHAR2(1024);

  fh := UTL_FILE.fopen('MY_TEXT_FILES', 'THE_FILE_NAME', 'R', 1024);

   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 ) );


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.

This was last published in March 2006

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.