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
default directory log_dir
access parameters (records delimited by newline
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.