Read a Text File into an Oracle Table using an External Table

Nice Trick for Reading Text Files Into Oracle

Have you ever had the need to read a text file into Oracle? And parse out pieces of information when you do it? Yes, you can do it with SQL Loader, but Oracle external tables offer much more flexibility. The example below shows you how to load an Oracle alert log into an Oracle table using an external table, but you can use this method with any text file.

First, define your directory and grant the required privileges:

create directory log_dir as ‘/u01/app/oracle/admin/orcl/bdump’;
grant read on directory log_dir to dba;
grant write on directory log_dir to dba;

Next, we create our table:

 

create table alert_log
(txt_line varchar2(512))
organization external
(type ORACLE_LOADER
default directory log_dir
access parameters (records delimited by newline
fields
(txt_line char(512)))
location (‘alert_orcl.log’)
);

Note that we set the length of the line to 512. Just set it to the maximum length of the line. Oracle will read to the end of the record as delimited by the new line.

At this point, we can read the table as any oracle table.

Select * from alert_log;

Create table ora_alert_log as (select rownum as line_number, txt_line from alert_log);

If you run the create table as select, you now have a standard Oracle table containing lines of text. At this point, you can do all kinds of queries and data manipulations. You can search for shutdown and startup times, look for Oracle error messages, or anything else you may need. This external table feature is very handy, and there is a lot you can do with it.

2 Responses to “Read a Text File into an Oracle Table using an External Table”

  1. Mauricio Says:

    Hi, I’ve been using SQL Loader and I’m new into External Tables technology. I have a problem with a stored procedure which retrieves data from an external table and then updates oracle tables.
    When I run the sql statements by themselves it works fine, but inside the stored procedure it hangs.
    Could you please tell me what is wrong?
    The sp lives in the same schema as the oracle tables and external tables. I have granted read/write permissions to dba (the schema is a member of dba role).
    Thanks.

  2. dbakerber Says:

    have you figured out which statement it is hanging on? You can use sql developer or Quest SQL Navigator to step through the code and figure out the specific command that is at issue. Without that information, it is hard to debug the code.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: