Re: ODBC with ACCESS

From: Glen A Stromquist <glen_stromquist_at_no_spam_yahoo.com>
Date: Fri, 06 Feb 2004 15:23:34 GMT
Message-ID: <WjOUb.3$964.2_at_edtnps84>


shizoom_at_msn.com wrote:
> Hi, Everyone:
>
> I tried using ODBC with ACCESS(2002 version) on Windows XP to put a table
> into Oracle(oracle 9i02 for Windows) under USER tablespace. The process
> works fine. The problem is all the columns are saved in varchar type. I
> assume this is inocrrect. Does anyone have the same experience before?
>
> After seeing that I tried logged in as SYSOP and using OEM to change the
> type, I was told that there is data in the column. I cannot change
> datatype. I can see the imported tables under SQL/PLUS using 'select
> table_name from user_tables;'. But 'desc' told me that object was not
> found. Could anyone kindly tell me what is going on here!
>
> Thank you for your help and suggestion.
>
> Shizoom Wang, 2/4/2004
>
>
When you did the desc was it as the same user as the one you logged in as thru ODBC? You may need to do a "desc <owner>.<table_name>" to see it. I have no idea is the varchar type is incorrect or not, you dont give enough information here. You cannot change a data type of a column if there is data in it, but you have several choices:

  1. create a table in Oracle with the datatypes you want then select from the other table into it
  2. export the data from the access table to a flat file and load it with sql loader into your new oracle table.
  3. create a dblink to the access database using ODBC and heterogeneous services, then you can use the table as it sits in Access or select from it into your oracle table, or create a view in oracle table that queries the access table. This option is the most PITA IMHO, but it does work. You can read more about it here: http://download-west.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/advcfg.htm#474076

(this is your lucky day, I was trouble shooting an HS issue and happened to have this page open)

HTH Received on Fri Feb 06 2004 - 16:23:34 CET

Original text of this message