importing data from Access97 into Oracle8 1

[dummie2]

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?

 

[JimHorton]

The simplest way is to create an ODBC DSN, link the Oracle table in Access, and do a simple append query in Access.&nbsp;&nbsp;For only 3600 records, this should be a breeze.<br>--Jim

 

[alaskaguru]

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 &quot;undo&quot; and &quot;rollback&quot; files.&nbsp;&nbsp;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 &quot;drag and drop&quot; them into the matching Oracle Table within Access97.&nbsp;&nbsp;<br><br>Hope that helps..<br><br>By the way..<br><br>If you use Oracle 8's Access to Oracle Wizard be careful when appending records.&nbsp;&nbsp;The wizard deletes any records which exist in the destination tables.&nbsp;&nbsp;So change the Access database filename and use a SQL select into statement in Oracle to append the records (oracle appends fast).&nbsp;&nbsp;<br><br>Does anyone have a generic routine to use a cursor to append just a few records at a time and then &quot;Commit&quot; the changes?&nbsp;&nbsp;If so, could you please send me a copy to <A HREF="mailto:alaskan@mail.com">alaskan@mail.com</A>?<br><br>....Ward<br><A HREF="mailto:alaskan@mail.com">alaskan@mail.com</A><br><br><br>

 

[Guest_imported]

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.

 

[ITA]

Here is an update cursor that may help out....<br><br>DECLARE<br>&nbsp;&nbsp;CURSOR sal_cursor IS<br>&nbsp;&nbsp;&nbsp;&nbsp;SELECT sal<br>&nbsp;&nbsp;&nbsp;&nbsp;FROM emp<br>&nbsp;&nbsp;&nbsp;&nbsp;WHERE deptno = 30<br>&nbsp;&nbsp;&nbsp;&nbsp;FOR UPDATE OF sal NOWAIT;<br>BEGIN<br>&nbsp;&nbsp;FOR emp_record IN sal_cursor LOOP<br>&nbsp;&nbsp;&nbsp;&nbsp;UPDATE emp<br>&nbsp;&nbsp;&nbsp;&nbsp;SET sal=emp_record.sal * 1.10<br>&nbsp;&nbsp;&nbsp;&nbsp;WHERE CURRENT OF sal_cursor;<br>&nbsp;&nbsp;END LOOP;<br>&nbsp;&nbsp;COMMIT;<br>END;