Re: Reading records from Access to Oracle -HELP!!

From: <MTPConsulting_at_aol.com>
Date: Tue, 21 Nov 2000 15:33:35 EST
Message-Id: <10687.122656@fatcity.com>

--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_at_odbc:source to userid/password_at_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_at_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

<HTML>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_at_odbc:source to userid/password_at_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_at_gecapital.com writes:
 
 
 <BLOCKQUOTE TYPE=CITE style="BORDER-LEFT: #0000ff 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px">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,
Received on Tue Nov 21 2000 - 14:33:35 CST