External table question

[jhammer98]

Hi all,

I have a situation where I have 2 different fixed length flat files coming in that I want to load into a single table. The first file contains half the detail for the record and the second has the other half. Can I use an external table to hold the records if the file layouts differ?

Thanks in advance...

~Jake

 

[SantaMufasa]

Jake,

Unless you can formulate some sort of "Least Common Denominator" format that accommodates both differing formats, it will goof up your data.

Why not just create two different external tables...one for each of your two flat files...then join the two external tables to produce the consolidated results you want?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[jhammer98]

That's my next approach...Thanks Santa!

Other that saving on tablespace, what are the advantages to external tables?

 

[SantaMufasa]

Other advantages that come immediately to mind:

* No redo or rollback entries ever (since external tables are read only).

* No time spent on loading flat-file data into physical tables (although one can argue (in)efficiencies of Oracle's algorithms for reading flat-file data versus physical table structures.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[carp]

Not to mention ease of repopulating tables if your files change periodically. Just install the file in the right place with the right name and the table is automatically updated.

 

[jhammer98]

Can anybody think of any disadvantages to external tables? This is the first time I'm working with them and from my experience thus far, this will be the way I do things from now on.

TIA,

~Jake

 

[carp]

There are just a couple of downsides -
1. You cannot perform DML on external tables. This is probably not really a downside - just a constraint on where/how you can use them.

2. You cannot build an index on an external table. Consequently, all queries are full table scans.

As far as I know, that's about it.

 

[jhammer98]

So far so good with external tables as long as I use the /tmp directory.

Now the problem I'm having is my files are landing on an NFS mount point that the Oracle user does not have access to. I'm pretty sure oracle can read the directory but I'm afraid it cannot write the log and bad files to it. I could get write access for oracle, but we use this directory strictly for landing incoming files and I don't want to muddy the water with log files and such. To get around this I tried qualifying the log and bad files with an alternate path:

RECORDS DELIMITED BY NEWLINE
BADFILE '/cfdv/smart/cmd/exttablog':'great_west_stat1.bad'
LOGFILE '/cfdv/smart/cmd/exttablog':'great_west_stat1.log'

This does not work.

I know that I can use the NOLOGFILE and NOBADFILE option but I kinda' like to have them in case things go wrong.

Any suggestions???

TIA,

~Jake

 

[avjoshi]

I haven't tried this, but have you tried creating a link to this file in a directory where you have write access? That way you can create your logfile and bad files in that directory and still be able to read from the flat file.

Anand

 

[jhammer98]

Just to follow-up...

I created the directory for the incoming files:

CREATE DIRECTORY GWEST_in AS '/faprd_in/SMART/GWEST';

Then I created a seperate directory for the log and bad files:

CREATE DIRECTORY EXTTABLOG AS '/cfprd/smart/cmg/exttablog';

Finally I created my external tables using both directories:

CREATE TABLE incoming_file_1(
	blah 		VARCHAR2 (13),
	blah		DATE,
	blah		VARCHAR2 (10),
	blah		VARCHAR2 (5),
	blah	VARCHAR2 (6))
  ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY GWEST_in
      ACCESS PARAMETERS
	(
	  RECORDS DELIMITED BY NEWLINE
	  BADFILE 'EXTTABLOG':'incoming_file_1.bad'
          LOGFILE 'EXTTABLOG':'incoming_file_1.log'
	      fields (blah1	position (1:13)  CHAR,
	      blah2	position (14:22) DATE 'YYYYMONDD',
	      blah3	position (34:43) CHAR,
	      blah4	position (50:54) CHAR,
	      blah5	position (65:70) CHAR)
	)
	LOCATION ('incomingfile1.dat')
    )
REJECT LIMIT 0;

Everything works great!

Thanks for everybody's help.

~Jake