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.
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
c. Now give PUT command to transfer file from current Windows machine to Linux machine.
Local file:C:\>emp.csv remote-file:/u01/oracle/emp.csv File transferred in 0.29 Seconds
d. Now after the file is transferred quit the FTP utility by typing bye command.
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
SQL>CREATE TABLE emp (empno number(5), name varchar2(50), sal number(10,2), jdate date);
3. After creating the table, you have