Message-Id: <10687.122656@fatcity.com> From: MTPConsulting@aol.com Date: Tue, 21 Nov 2000 15:33:35 EST Subject: Re: Reading records from Access to Oracle -HELP!! --part1_31.ce70a27.274c361f_boundary Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit I haven't tried this myself, but from an old message off this list, you should be able to use the sqlplus copy command to copy from an ODBC source to an Oracle table. You use @ODBC:sourcename for the ODBC side of the copy command after you've defined your ODBC source. Something like: copy from userid/password@odbc:source to userid/password@connectstring insert oracletable using select * from accesstable Marc Perkowitz MTP Systems Consulting In a message dated 11/21/00 1:41:55 PM Central Standard Time, Mary.Ruiz@gecapital.com writes: > Fred: > If your table has all text columns, the best way is to create an ODBC > datasource for your Oracle database, Highlight the table, click on File .. > Save As/Export and in the export Wizard, there is a pull down for Files of > Type ... Choose ODBC Datasources and then specify the name of your oracle > database. Be sure all of the columns in the table are upper case, with no > blanks in them. Same for the name of the table. > If the MS Access table has non-text columns, this method does not work very > well. For example, columns that are double precision get converted to > varchar2(40) in the export process. In this case, you can export the > Microsoft access table to a comma delimited file and run sqlldr. > > If the table is small, then you can get away with writing an "Append" query > to append the contents of your MS Access table to the empty table residing > in the Oracle database. > > hope this helps, > > Mary Ruiz / Atlanta GA > > -----Original Message----- > Sent: Tuesday, November 21, 2000 12:05 PM > To: Multiple recipients of list ORACLE-L > > > List, > > I was looking for anyone that has an idea of the best way to be able to > select records that exist in a table in Microsoft Access into a table in > Oracle. Has anyone done this? What is the best method of doing this? > > I'm on Oracle 8.0.5.2.1 on Windows NT. > > Thanks, > -Fred S. > --part1_31.ce70a27.274c361f_boundary Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: 7bit I haven't tried this myself, but from an old message off this list, you
should be able to use the sqlplus copy command to copy from an ODBC source to
an Oracle table.  You use @ODBC:sourcename for the ODBC side of the copy
command after you've defined your ODBC source.

Something like:

copy from userid/password@odbc:source to userid/password@connectstring insert
oracletable using select * from accesstable

Marc Perkowitz
MTP Systems Consulting

In a message dated 11/21/00 1:41:55 PM Central Standard Time,
Mary.Ruiz@gecapital.com writes:


Fred:
 If your table has all text columns, the best way is to create an ODBC
datasource for your Oracle database, Highlight the table, click on File ..
Save As/Export and in the export Wizard, there is a pull down for Files of
Type ... Choose ODBC Datasources and then specify the name of your oracle
database.  Be sure all of the columns in the table are upper case, with no
blanks in them.  Same for the name of the table.   
If the MS Access table has non-text columns, this method does not work very
well.  For example, columns that are double precision get converted to
varchar2(40) in the export process.  In this case, you can export the
Microsoft access table to a comma delimited file and run sqlldr.

If the table is small, then you can get away with writing an "Append" query
to append the contents of your MS Access table to the empty table residing
in the Oracle database.

hope this helps,

Mary Ruiz / Atlanta GA

-----Original Message-----
Sent: Tuesday, November 21, 2000 12:05 PM
To: Multiple recipients of list ORACLE-L


List,

  I was looking for anyone that has an idea of the best way to be able to
select records that exist in a table in Microsoft Access into a table in
Oracle.  Has anyone done this?  What is the best method of doing this?

I'm on Oracle 8.0.5.2.1 on Windows NT.

Thanks,