Dear Kiran, Tell me one thing you want me import the table or data in the
table. ok what ever may be the position, see in oracle apart from oracle import and export we have got something called oracle SQLLOADER using this we can import data from external file to oracle database. Â
According to your question you want to load data from MS-Aceess to Oracle. Here
we can do one thing.
See I am taking about to load the data from ms-access
assuming that tables have been already created in Oracle database.
(if this not correct you tell me context i will help you out)
Let me tell, the steps to migrate data from ms-access to oracle database assuming same tables are been created in
Oracle database:
Step1: Let us try export data from MS-Aceess to local
dump, obviously we can see data in excel format.....open each file and then save it into csv format or diractly you can export into
CSV format, any how your wish..
Step2: Now you have the raw data.
So create a folder and place all the source files into that folder
Step3: In oracle we have got sqlloader which loads data from external file to oracle tables using control as it instructs oracle loader as to waht data we need to load
Step4: Now your job is to write the control file
Step5:
Go to command prompt type the following command sqlldr scott/tiger control=filename.ctl
how to write control file:
let us we have table called sample and columns are x,y
sample x number, y number
sample.ctl
load data infile "d:ms_access_dumpsample.csv" insert into table sample fields terminated by "," optionally enclosed by '"' trailing nullcols(x,y)
after writing the above code save the file in the folder where you have src files, now go to command prompt:
This might help someone if need to get table into Oracle from MS Access table.
Option-1 - Use MS Access (2003) Export 1. Oracle Client and Oracle ODBC driver should be installed on your PC. 2. In ODBC Admin, create System DSN or File DSN for your Oracle database (use Oracle Driver) 3. Open MS Access database 4. Open MS Access table you want to migrate. 5. File Menu | Export 6. Dialog box opens, select ODBC Database () in "Save As Type" Combo/List Box at bottom 7. It will ask for the table name, so give table name as TEMP_YOURTABLENAME 8. Then it will ask for File Datasource or Machine Data Source so select System DSN or File DSN (created in step 2) 9. It will ask for Oracle userid and password. 10. TEMP_YOURTABLENAME table is created.
Sometimes it allows SELECT * from TEMP_YOURTABLENAME but does not allow SELECT Col1, Col2 from TEMP_YOURTABLENAME etc..
Then manually create table YOURTABLE in Oracle appropriately and then use INSERT INTO YOURTABLE (select * from TEMP_YOURTABLE); COMMIT;
Option-2 - Use SQLLoader to import file 1. You need to know how to use SQL*Loader utility. 2. In Oracle first create table manually. 3. Open MS Access database 4. Open MS Access table you want to migrate the data. 5. Dialog box opens, select Text Files (*.txt, *.csv, *.tab...) in "Save As Type" Combo/List Box at bottom 6. Give file name as temp.csv or temp.tab (select appropriate folder to place your file to) 7. Click button "Export All" 8. Now data is available in csv ot tab file 9. Use SQL*Loader to import data from that file to your Oracle table you created in step 2.
0 
1