How to transfer Access DB date to Oracle Database 1

[newbieDev]

Hi Everyone,

I have an oracle DB question.
I am trying to convert an access DB to an Oracle DB. What is the best way to transfer my data from access to oracle without having to create admin pages to add data manually?

Do the tables in both DBs have to be identical, that is same name and fields?

Will appreciate any advice or comment.

Thanks!

 

[Turkbear]

Hi,
Access has an export function that can be used to move an Access Table into an Oracle instance, as long as you have sufficient rights in Oracle and you have a functioning ODBC DSN for the Oracle instance you need to receive the table..

Change any lower or mixed-case field names to all upper and eliminate any special characters except _ ..Set MEMO fields to TEXT and give them a maximum length of 4000.( Preferably no longer than needed )





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[newbieDev]

Thanks for the reply!

I have more info and more questions.

The oracle DB has already been created and the table names are not the same. I just need to get the data in the old DB into the related fields/Table in the new Oracle DB.

I read something about using Oracle's SQL loader but didn't really get the details on how to use it.

I also read about creating a .CSV file, but no information on how to create/use the .CSV file (I used access to create a text file of a table, and renamed it with a .CSV extension...is this how to do it?).

How do you use Oracle's SQL loader, and a .CSV file to export data to a table already created in oracle?

Thanks in advance!

 

[Turkbear]

Hi,
Not an easy thing to teach in this forum..Look at the docs
here:

http://tahiti.oracle.com/

The Utilities manual

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/utility.htm

will describe how to use SqlLoader and
it can read .CSV files - Export as a CSV ( Text) file

I would find it easier to export to a interim table and use that to populate the Existing one using the 'Insert into existing_table select f1,f2,... from new_table_I_created' method..

If new to Oracle ask your DBA to assist..




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Beilstwh]

If the column names are not the same, but the columns are then the easiest way is to create an external odbc table link in the access database and then do a simple append query in access that will load all the rows into oracle.

Bill
Oracle DBA/Developer
New York State, USA

 

[bookouri]

the ODBC method from access to the oracle tables is by far the easiest way ive found to get it done. But, the dates and logical data types in Access can be a problem for Oracle. I usually create a couple of extra columns in the target oracle table, load the access dates into that column and then use sql to update the oracle table making the date columns format properly. If you have an access column for example called todays_date create an oracle column todays_date_tmp as a varchar2 and export your date data into that _tmp col. Then use sql to clean it up on the oracle side loading it into the proper oracle date column.

 

[Beilstwh]

I am not sure what the issue is with your dates. I have inserted and read dates from oracle tables in an access database with no problems. As long as the oracle table is defined with a date type and you use date functions in the queries (date(), now(), time()...) everything always works great.

Bill
Oracle DBA/Developer
New York State, USA