This post summarizes the steps to create an external table in Oracle.
Note
The following changes should be done in the database server workstation
Overview
1. Create a directory
2. Give apropriate access to the accessing user
3. Create the csv file which need to be accessed
4. Create an external table
5. Try selecting the record using the external table
Steps to be followed in creating an external table
1. Create a Directory
1. Log in to a schema using sql * plus
2. You would have placed the csv file in a folder in you local machine
I have placed the csv file in H:\FORMS folder in my local system.
So I would be running the following statement in SQL * PLUS.
create or replace directory forms as 'H:\Forms';
2. Give appropriate access to the accessing user
We need to give the appropriate acess to all the users who is going
to access the following directory
grant read, write on directory forms to store;
3. Create the csv file which need to be accessed
I have created a file named Family.csv and placed it in the H:\Forms folder in my local.
It looks similiar to the following file.
4. Create an external table
We need to create a table to access the records available in the CSV file.
The syntax for creating an external table is as follows.
create TABLE family
(
ID number, -- Use VARCHAR2 eventhough you are using a NUMBER column
name varchar2(100),
comments varchar2(1000)
)
organization external (
type oracle_loader
default directory FORMS
access parameters (
records delimited by newline
fields terminated by ','
missing field values are null
)
location ('Family.csv')
)
reject limit unlimited
/
We would be getting the following output in SQL * PLUS
5. Try selecting the record using the external table
Select * from family;
The output is as follows in SQL * PLUS.
No comments:
Post a Comment