{9i New Features: external tables}

From: Joe Testa <teci_at_the-testas.net>
Date: Wed, 28 Nov 2001 16:54:00 -0800
Message-ID: <F001.003CFC94.20011128155529@fatcity.com>

Welcome to this week's installment of 9i the continuing saga :)

External Tables.

So just what is an external table? An external table is a complement to SQL*LOADER.
It allows you to access data in some external source as if it were a table in the database.

Thats the good part, now the bad part(aka restrictions):

1. Read-only.
2. No DML operations.
3. No indexing.
4. You must know the structure of the data.

So what do we need to do to make this all work:

1. Create a directory object. create directory external_load_dir as '/tmp';
2. grant read on directory <directory_object> to <user>;
3. create the external table definition(which is more like a pipe for you unix people).

Here is the external data:

[jtesta_at_laptop-jt 9i]$ cat /tmp/emp.dat

123456Joe
000001Rachel
000020Susan
000030Guy
000222Wild man

Here is the code to be able to access the external data via the only access method supported
right now(oracle_loader).

• drop any existing directory object drop directory external_load_dir;
• create the new directory object create directory external_load_dir as '/tmp';
• grant read on directory object if needed, i skipped it
• since i was running as directory object owner
  --grant read on external_load_dir
  
• drop a test table drop table emp_test;
• create the test table that looks like the input data create table emp_test (emp_no char(6), emp_lname char(20));
• drop the external table drop table emp_load;
• create the external table definition
• notice the default directory, we created that with the create directory command
• also this is a very small set of options that you can have in the access
• parameters, from what I can tell almost anything that you can put in a
• sql*loader .ctl file you can put here. create table emp_load(employee_number char(6), emp_last_name char(20)) organization external (type oracle_loader default directory external_load_dir access parameters( records delimited by newline fields(employee_number char(6), emp_last_name char(20))) location ('emp.dat'));

select * from emp_load;

insert into emp_test select * from emp_load;

select * from emp_test;

commit;

What you will see in running that previous set of code, is that the data will pulled from
the flat file in the /tmp directory and will be displayed to the screen.

Check out the 9i utilities manual for further reading.

As always sent hate mail to /dev/null, all good stuff to 9i_at_oracle-dba.com

Joe

--

Joe Testa, Oracle DBA
Want to have a good time with a bunch of geeks? Check out: http://www.geekcruises.com/standard_interface/future_cruises.html I'm presenting, when registering drop my name :)
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Joe Testa
  INET: teci_at_the-testas.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Nov 28 2001 - 18:54:00 CST