Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

importing data from Access97 into Oracle8

importing data from Access97 into Oracle8

importing data from Access97 into Oracle8

Oracle 8 has just been installed and I'm not real familiar w/it.  I do know a little SQL, but really haven't worked w/ it too much to feel confident.  I have about 3600 records in a flat table in Access97 with columns for 'address', 'po box','cost center','office name', 'zip code', 'city' 'county', and I want to get it all into a one table of about 160 tables in Oracle8.  How can I use either SQL or an Oracle8 GUI feature to do this?

RE: importing data from Access97 into Oracle8

The simplest way is to create an ODBC DSN, link the Oracle table in Access, and do a simple append query in Access.  For only 3600 records, this should be a breeze.

RE: importing data from Access97 into Oracle8

I agree with Jim, but sometimes Access can be pretty slow in adding the data into Oracle--it wants to transfer all of the records at once--which takes a lot of memory and wastes time building big "undo" and "rollback" files.  We've found that after we link the Oracle table into access, it is often faster to simply select a group of 500 to 1,000 records with your mouse and "drag and drop" them into the matching Oracle Table within Access97.  

Hope that helps..

By the way..

If you use Oracle 8's Access to Oracle Wizard be careful when appending records.  The wizard deletes any records which exist in the destination tables.  So change the Access database filename and use a SQL select into statement in Oracle to append the records (oracle appends fast).  

Does anyone have a generic routine to use a cursor to append just a few records at a time and then "Commit" the changes?  If so, could you please send me a copy to alaskan@mail.com?


RE: importing data from Access97 into Oracle8

If you extract the data out of Acce4ss into a space or comma delimited file (my preferences), you can use SQL*Loader to load the data that way. I've been doing this loads with flat files since '86 and it's never failed me.

RE: importing data from Access97 into Oracle8

Here is an update cursor that may help out....

  CURSOR sal_cursor IS
    SELECT sal
    FROM emp
    WHERE deptno = 30
  FOR emp_record IN sal_cursor LOOP
    UPDATE emp
    SET sal=emp_record.sal * 1.10
    WHERE CURRENT OF sal_cursor;

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close