hey folks,

what's the best way to transfer data from Access to Oracle DB? the DB designs for the databases are not identical, meaning that i can't just copy the data from a table in the Access DB to one in the Oracle DB. first i have to figure out what fields from what tables in the Access DB go in what fields/tables in the Oracle DB and then do the transfer.

i am thinking Perl script, but that'd take a long time. any suggestions?

thanks all.

If I were you, I'll go with Oracle SQL*Loader.

- Rohit.

never heard of it. what is it and where can i get it from?

??

use an ODBC connection

totally easy, painless, and worry free

Since this is MS Access you are talking about, it probably is not a whole heck of a lot of data, so The Duck is right. You will have to link the Oracle tables in the Access Database, then create Insert queries to populate the data just so. To warn you, I tried this once. It will take you several tries to get all the queries right. The only alternatives to that are buying some expensive ETL tool. Good luck.

The easiest way would be to dump your Access data in a csv formatted file.
Transfer the file to your oracle server, and finally use SQL*Loader as krohit said.

It may take you few hours to get this thing to work, but with the help of Oracle Documentation you should be able to figure out out to load your data!

I would use SQL Loader too. Its in your oracle binn file. You need to get the syntax right, but it is a good way to do it. The syntax is pretty to sort out, and its readily available.

I will definitely use SL Loader to do this.

First save the Access tables as text files and then create a .CTL using a text editor. The .CTL file specifies the location of the table saved as a textfile and the tabel in oracle to which you want the data exported. Meaning you must have the table already created in Oracle with the same number of fields as you have it coming from access.

The syntax for .CTL file should be thus

Load Data
Infile "Mytextfile.txt" -- file containing table data -- specify paths correctly
Append or Truncate (-- I guess you should be using append) into oracle tablename
Separated by "," (or whatever) optionally enclosed by
(Field1, field2, field3 etc)

Hope this helps

You use SQL loader from your command prompt thus

(sqlldr) (username/password) (.CTL filename)

Note the spaces in between. Brackets are for clarity. Not required

well, i could do that if the tables in my Oracle DB corresponded 1:1 to the tables in the Access DB, but alas, they do not. different fields from tables in the Access DB will go to who knows what fields in the Oracle DB, it's a big mess i tell ya.

In that case use staging tables which are an exact copy of your access tables. Use linked tables in Access to copy the data over.

And then use sql or plsql to transform the data from your staging tables to your final tables.

Alan

I would think the easiest thing to do would be to create a linked table in Access that points to the Target Oracle table(s). Then write update queries in Access that will update/insert the link table with the necessary transformations in Access.

ie.

Access table: invoices
Access table: inv_day
Oracle table linked to Access: invoice

Update query

Insert into invoice
Select i.invoice_id, i.customer_id, id.invoice_date, id.ship_date
From invoices i, inv_day id
Where id.invoice_id = i.invoice_id;

Another way would be Microsofts DTS package if you have that.

hmm, linking the Oracle tables to the Access ones sounds like a good idea. how exactly do i do that though? i have the Oracle client installed on my machine, but i just installed the developer tools. would i need to reinstall with all the management tools and everything in order to be able to do table linking?

no no no no.


You can load ALL tables from Access into Oracle, then manipulate the data anyway you want.

TOTALLY simplistic.

Get the data into Oracle, then writing scripts to load your real Oracle tables is a snap.

USE ODBC!!

Takes two seconds to connect to your test-schema, then load all access tables into there. Write simple scripts to load the access tables you just moved into oracle into your real DB-tables and PRESTO! All set.

The_Duck,
your solution sounds good, but you are being too vague for my level of Access/Oracle knowledge. assume i am a novice in the DB field and give me step by step instructions please.
thanks much.