Database Reference
In-Depth Information
16 text3 position(8001:12000)
17 )
18 )
19 location ('demo1.bad')
20 )
21 /
Table created.
This is just a table that can read any file without failing on a datatype error, as long as the lines in the file consist of
fewer than 12,000 characters. If they are longer than 12,000 characters, then we can simply add more text columns to
accommodate them.
We can clearly see the rejected records via a simple query:
EODA@ORA12CR1> select * from et_bad;
TEXT1 TEXT2 TEXT3
--------------- --------------- ---------------
ABC,XYZ,Hello
A
COUNT(*)
could tell us how many records were rejected. Another external table created on the log file
associated with this external table could tell us why the record was rejected. We would need to go one step further to
make this a repeatable process, however. The reason is that the bad file is not blanked out if there were no errors in
our use of the external table. So, if there were some preexisting bad file with data in it and our external table generated
no errors, we would be misled into thinking there were errors.
I've taken four approaches in the past to resolve this issue:
•
Use
UTL_FILE
and reset the bad file—truncate it, in effect, by simply opening it for write and
closing it.
•
Use
UTL_FILE
to rename any preexisting bad files, preserving their contents, but allowing us to
create a new one.
•
Incorporate the PID into the bad (and log) file names. We'll demonstrate this later in the
“Multiuser Issues” section.
•
Manually use operating system commands to resolve issues (like renaming the file, removing
it, and so on).
In that fashion, we'll be able to tell if the bad records in the bad file were generated by us just recently or if they
were left over from some older version of the file itself and are not meaningful.
prOJeCt reFereNCeD COLUMNS
the
COUNT(*)
earlier in this section made me think about a feature available in oracle 10
g
and above: the ability
to optimize external table access by only accessing the fields in the external file that are referenced in the query.
that is, if the external table is defined to have 100 number fields, but you select out only one of them, you can
direct oracle to bypass converting the other 99 strings into numbers. it sounds great, but it can cause a different
number of rows to be returned from each query. Suppose the external table has 100 lines of data in it. all of the
data for column
C1
is “valid” and converts to a number. none of the data for column
C2
is “valid,” and it does not
convert into a number. if you select
C1
from that external table, you'll get 100 rows back. if you select
C2
from
that external table, you'll get 0 rows back.
