INSERT into Oracle table as SELECT from MS Access table
I'm trying to pull some data across from an MS Access database. I really need to pick and choose what is coming across, so migration isn't what I need. I can connect to the Access DB from SQL Developer, export data and so forth. However, I can't seem to find a way to directly access data in the MDB file from an Oracle connection. The two options that I can think of which would be perfect (if they are possible) are:
1. If it's possible to SELECT between two open connections in SQL Developer. I can (and have) had both my Oracle conection where I want the data to go and the source Access connection opened simultaneously, but I can't see any way to address a query from one connection to another.
2. If it's possible to create the equivalent of a database link to the Access MDB file from within my Oracle database so that I can address the query via that.
Right now I'm exporting individual tables from Access to files, then doing a bunch of Search and Replace operations to convert them to SQL scripts with INSERT statements in the format required, then running those scripts in my Oracle connection. It works, but is very slow and tedious.