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

    Requires Free Membership to View

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.

This was first published in March 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: