Contains information about Teradata,BTEQ,FASTLOAD,FASTEXPORT, Oracle Pl/SQL,forms, JAVA open source related topics and discussions
Oracle forms and reports
  Oracle database 

15 April 2009

How to Create external tables in Oracle

                                             ORACLE EXTERNAL TABLES
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