Connecting Oracle to MSACCESS

Hi,

This post summarizes the most important and intresting feature of Oracle which I have tried.

Normally for data entry operators, we would enter the data for internal application in small database like MSACCESS,.... 

but the query feature of Oracle is easy, so if you want to maintain your datasource as MSACCESS and if you want to do any queries using Oracle SQL, this helps you.

Steps

1. Create a MDB file with a username and password

     I think everyone knbows to open access and create an MDB file.

2. Run the following scripts

     \Forms_home\OCA60\SQL\ACCESS  --> *ACCDMBLD.SQL* (Used to create EMP and DEPT table)

       \Forms_home\OCA60\SQL                    --> *DEMODATA.SQL* (Insert demo data into the tables)

3. Create a ODBC data source in the data sources management tab in windows

    1. Open the Data source managment tab

        Control Panel --> Administrative tools ---> Data Sources (ODBC) 

        

     

    2.  Click the Add button,*Create new datasource* screen opens

       

         

    3. Select Microsoft Access Driver(*.mdb) and click Finish.

     

    4. ODBC Microsoft access screen opens, give a name for the data source

        and click the OK button

            

      

    

    5.  Click  the selet button and select the corresponding MDB file

         and click the OK button

          

    6. The datasource would have been created and check the initial screen

      

        

 

4. Open the SQLPLUS.exe under \Forms_HOME\BIN

    

    

5. Connect with the username and password as follows

    username/password@ODBC:DATA_SOURCE_NAME

    

    

      

      

6. Select the data from tables in MSACCESS as we do it in Oracle.

     

    Select * from EMP;

    

    

    

     

 

     

     

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.