I want to use Oracle 9i to get information from an Access database on another machine in the enterprise. I was thinking I could use ODBC and SQL*Plus ion a stored procedure but it isn't real clear from the documention how this is done. Any ideas?

You could use the following procedure:

- Create the table tblOracle in the Oracle9i database
- In Access, make a linked table tblOracleAccess, linking to tblOracle via ODBC
- Make an insert query in Access, inserting records from the original Access-table into tblOracleAccess

Ad Dieleman.

OK I can see that pushing it in from Access would work but What I want to do is pull it in from Oracle then do another read to check that the data has been read successfully then I want to delete the records from Access because Access can become unstable after it gets too many record. Access is just going to be used as a temporary repository for Data until it can be written and verified into Oracle.

I'm not so familiar with what Oracle tools can do. You could check on otn.oracle.com through all the products and see if there's something you can use.

Another method, if you have the possibility of installing Microsoft SQL Server on some PC: use its DTS tool, it can transfer data from Access to Oracle (and a whole lot of other source and target databases). I use this tool for ad-hoc data transfers because it's so convenient.

Ad Dieleman.

Oracle 9i has a feature where you can work with external tables. I know it works with ASCII csv files, but I am not sure if it works with ODBC data sources. Search for External Tables at tahiti.oracle.com