Oracle Utilities - External Tables Environment Setup

Author: Packt Publishing Published on: 4th Jan 2010

Let's setup the environment

Let's create the demonstration user, and prepare its environment to create an External Table. The example that will be developed first refers to the External Table using the ORACLE_LOADER driver.

Ads

Sample Code

create user EXTTABDEMO
identified by ORACLE
default tablespace USERS
alter user exttabdemo
quota unlimited on users
grant CREATE SESSION,
CREATE TABLE,
CREATE PROCEDURE,
CREATE MATERIALIZED VIEW,
ALTER SESSION,
CREATE VIEW,
CREATE ANY DIRECTORY
to EXTTABDEMO

A simple formatted spool from this query will generate the required external table demonstration data. The original source table is the demonstration HR.EMPLOYEES table.

Sample Code

select
EMPLOYEE_ID ','
DEPARTMENT_ID ','
FIRST_NAME ','
LAST_NAME ','
PHONE_NUMBER ','
HIRE_DATE ','
JOB_ID ','
SALARY ','
COMMISSION_PCT ','
MANAGER_ID ','
EMAIL
from HR.EMPLOYEES
order by EMPLOYEE_ID

The above query will produce the following sample data:

The External Table directory is defined inside the database by means of a DIRECTORY object. This object is not validated at creation time, so the user must make sure the physical directory exists and the oracle OS user has read/write privileges on it.

$ mkdir $HOME/external_table_dest SQL> CREATE DIRECTORY EXTTABDIR AS '/home/oracle/external_table_dest';

Ads

The above example was developed in a Linux environment, on Windows platforms the paths will need to be changed to correctly reflect how Oracle has been set up.

Now, the first External Table can be created.

Read Next: Oracle Utilities - A Basic External Table

This tutorial is part of a Oracle Database Management Utilities tutorial series. Read it from the beginning and learn yourself.

Oracle Database Management Utilities