CASE STUDY (Loading Data from MS-ACCESS to Oracle)
Suppose you have a table in MS-ACCESS by name EMP, running under Windows O/S, with the following structure EMPNO INTEGER NAME TEXT(50) SAL CURRENCY JDATE DATE
This table contains some 10,000 rows. Now you want to load the data from this table into an Oracle Table. Oracle Database is running in LINUX O/S.
Solution
Steps
Start MS-Access and convert the table into comma delimited flat (popularly known as csv) , by clicking on File/Save As menu. Let the delimited file name be emp.csv
1. Now transfer this file to Linux Server using FTP command
a. Go to Command Prompt in windows
b. At the command prompt type FTP followed by IP address of the server running Oracle.
FTP will then prompt you for username and password to connect to the Linux Server. Supply a valid username and password of Oracle User in Linux
For example:-
C:\>ftp 200.200.100.111
Name: oracle
Password:oracle
FTP>
c. Now give PUT command to transfer file from current Windows machine to Linux machine.
FTP>put
Local file:C:\>emp.csv remote-file:/u01/oracle/emp.csv File transferred in 0.29 Seconds
FTP>
d. Now after the file is transferred quit the FTP utility by typing bye command.
FTP>bye
Good-Bye
2. Now come the Linux Machine and create a table in Oracle with the same structure as in MS-ACCESS by taking appropriate datatypes. For example, create a table like this
$sqlplus scott/tiger
SQL>CREATE TABLE emp (empno number(5), name varchar2(50), sal number(10,2), jdate date);
3. After creating the table, you have