problem exporting access table in oracle database

[premmehrotra]

I am using Microsoft Access 2000 and Oracle 9.2.0.5 on Windows 2000.
When I export a table from Access to Oracle using ODBC
I get error:
ORA 972 identifier too long

I think the error is because one or more columns in Access table
are longer than 30 characters. My question is how one can overcome this
problem.

I also see several column names which have /, ? or spaces in name
but I think Oracle somehow takes care of that by putting names inside
quoted string? I am just guessing, I am new to Access to Oracle
mapping.

Any help will be greatly appreciated? Is there anyway I can
create a table in oracle with 30 characters max long name
and then map them to Access long names somehow?

Thanks a lot,

Prem

[Tom van Stiphout]

On 30 Dec 2004 19:04:46 -0800, pr**********@hotmail.com wrote:

A little bit of planning ahead of time goes a long way :-)

You can create your Oracle table with column names that it likes, and
then "map" them in Access using this technique:
- Create table in Oracle
- Create a linked table to it in Access
- Create a query in Access that renames the columns. Something like:
select NeatOracleColumn1 as BigOldColumnNameWithUglyCharacters1,
NeatOracleColumn2 as BigOldColumnNameWithUglyCharacters2 From
tblOracleTable
- Name this query tblOracleTable, and pretend it's a real table.

-Tom.

I am using Microsoft Access 2000 and Oracle 9.2.0.5 on Windows 2000.
When I export a table from Access to Oracle using ODBC
I get error:
ORA 972 identifier too long

I think the error is because one or more columns in Access table
are longer than 30 characters. My question is how one can overcome this
problem.

I also see several column names which have /, ? or spaces in name
but I think Oracle somehow takes care of that by putting names inside
quoted string? I am just guessing, I am new to Access to Oracle
mapping.

Any help will be greatly appreciated? Is there anyway I can
create a table in oracle with 30 characters max long name
and then map them to Access long names somehow?

Thanks a lot,

Prem

[premmehrotra]

Tom van Stiphout wrote:

On 30 Dec 2004 19:04:46 -0800, pr**********@hotmail.com wrote:

A little bit of planning ahead of time goes a long way :-)

You can create your Oracle table with column names that it likes, and
then "map" them in Access using this technique:
- Create table in Oracle
- Create a linked table to it in Access
- Create a query in Access that renames the columns. Something like:
select NeatOracleColumn1 as BigOldColumnNameWithUglyCharacters1,
NeatOracleColumn2 as BigOldColumnNameWithUglyCharacters2 From
tblOracleTable
- Name this query tblOracleTable, and pretend it's a real table.

-Tom.

I am using Microsoft Access 2000 and Oracle 9.2.0.5 on Windows 2000.
When I export a table from Access to Oracle using ODBC
I get error:
ORA 972 identifier too long

I think the error is because one or more columns in Access table
are longer than 30 characters. My question is how one can overcome thisproblem.

I also see several column names which have /, ? or spaces in name
but I think Oracle somehow takes care of that by putting names insidequoted string? I am just guessing, I am new to Access to Oracle
mapping.

Any help will be greatly appreciated? Is there anyway I can
create a table in oracle with 30 characters max long name
and then map them to Access long names somehow?

Thanks a lot,

Prem

Tom:

Thanks a lot. I inherited this Access Datacase. It lareday has several
queries, forms, reports. If I rename columns, I may have to modify
existing code. Is there a transparent way, e.g., like Oracle Views
where names can be changed without impacting existing code.

All I have to do is to move Access Tables into Oracle and still use
existing Access front end.

Prem

[Tom van Stiphout]

On 31 Dec 2004 07:46:24 -0800, pr**********@hotmail.com wrote:

The technique I explained is designed to not affect the rest of the
app. So if you have an existing Access query that joins with the
Access table tblOracleTable, after you move this table to Oracle and
apply the technique, that query will continue to function unchanged.
Same for DAO or ADO code (with very few exceptions).

-Tom.

Tom van Stiphout wrote:

On 30 Dec 2004 19:04:46 -0800, pr**********@hotmail.com wrote:

A little bit of planning ahead of time goes a long way :-)

You can create your Oracle table with column names that it likes, and
then "map" them in Access using this technique:
- Create table in Oracle
- Create a linked table to it in Access
- Create a query in Access that renames the columns. Something like:
select NeatOracleColumn1 as BigOldColumnNameWithUglyCharacters1,
NeatOracleColumn2 as BigOldColumnNameWithUglyCharacters2 From
tblOracleTable
- Name this query tblOracleTable, and pretend it's a real table.

-Tom.

>I am using Microsoft Access 2000 and Oracle 9.2.0.5 on Windows 2000.
>When I export a table from Access to Oracle using ODBC
>I get error:
>ORA 972 identifier too long
>
>I think the error is because one or more columns in Access table
>are longer than 30 characters. My question is how one can overcomethis >problem.
>
>I also see several column names which have /, ? or spaces in name
>but I think Oracle somehow takes care of that by putting namesinside >quoted string? I am just guessing, I am new to Access to Oracle
>mapping.
>
>Any help will be greatly appreciated? Is there anyway I can
>create a table in oracle with 30 characters max long name
>and then map them to Access long names somehow?
>
>Thanks a lot,
>
>Prem

Tom:

Thanks a lot. I inherited this Access Datacase. It lareday has several
queries, forms, reports. If I rename columns, I may have to modify
existing code. Is there a transparent way, e.g., like Oracle Views
where names can be changed without impacting existing code.

All I have to do is to move Access Tables into Oracle and still use
existing Access front end.

Prem

[premmehrotra]

Tom van Stiphout wrote:

On 31 Dec 2004 07:46:24 -0800, pr**********@hotmail.com wrote:

The technique I explained is designed to not affect the rest of the
app. So if you have an existing Access query that joins with the
Access table tblOracleTable, after you move this table to Oracle and
apply the technique, that query will continue to function unchanged.
Same for DAO or ADO code (with very few exceptions).

-Tom.

Tom van Stiphout wrote:

On 30 Dec 2004 19:04:46 -0800, pr**********@hotmail.com wrote:

A little bit of planning ahead of time goes a long way :-)

You can create your Oracle table with column names that it likes, and then "map" them in Access using this technique:
- Create table in Oracle
- Create a linked table to it in Access
- Create a query in Access that renames the columns. Something like: select NeatOracleColumn1 as BigOldColumnNameWithUglyCharacters1,
NeatOracleColumn2 as BigOldColumnNameWithUglyCharacters2 From
tblOracleTable
- Name this query tblOracleTable, and pretend it's a real table.

-Tom.
>I am using Microsoft Access 2000 and Oracle 9.2.0.5 on Windows 2000. >When I export a table from Access to Oracle using ODBC
>I get error:
>ORA 972 identifier too long
>
>I think the error is because one or more columns in Access table
>are longer than 30 characters. My question is how one can

overcomethis

>problem.
>
>I also see several column names which have /, ? or spaces in name
>but I think Oracle somehow takes care of that by putting names

inside

>quoted string? I am just guessing, I am new to Access to Oracle
>mapping.
>
>Any help will be greatly appreciated? Is there anyway I can
>create a table in oracle with 30 characters max long name
>and then map them to Access long names somehow?
>
>Thanks a lot,
>
>Prem

Tom:

Thanks a lot. I inherited this Access Datacase. It lareday has severalqueries, forms, reports. If I rename columns, I may have to modify
existing code. Is there a transparent way, e.g., like Oracle Views
where names can be changed without impacting existing code.

All I have to do is to move Access Tables into Oracle and still use
existing Access front end.

Prem

Tom:
Thanks a lot. I will give it a try.

Prem

[premmehrotra]

Tom van Stiphout wrote:

On 31 Dec 2004 07:46:24 -0800, pr**********@hotmail.com wrote:

The technique I explained is designed to not affect the rest of the
app. So if you have an existing Access query that joins with the
Access table tblOracleTable, after you move this table to Oracle and
apply the technique, that query will continue to function unchanged.
Same for DAO or ADO code (with very few exceptions).

-Tom.

Tom van Stiphout wrote:

On 30 Dec 2004 19:04:46 -0800, pr**********@hotmail.com wrote:

A little bit of planning ahead of time goes a long way :-)

You can create your Oracle table with column names that it likes, and then "map" them in Access using this technique:
- Create table in Oracle
- Create a linked table to it in Access
- Create a query in Access that renames the columns. Something like: select NeatOracleColumn1 as BigOldColumnNameWithUglyCharacters1,
NeatOracleColumn2 as BigOldColumnNameWithUglyCharacters2 From
tblOracleTable
- Name this query tblOracleTable, and pretend it's a real table.

-Tom.
>I am using Microsoft Access 2000 and Oracle 9.2.0.5 on Windows 2000. >When I export a table from Access to Oracle using ODBC
>I get error:
>ORA 972 identifier too long
>
>I think the error is because one or more columns in Access table
>are longer than 30 characters. My question is how one can

overcomethis

>problem.
>
>I also see several column names which have /, ? or spaces in name
>but I think Oracle somehow takes care of that by putting names

inside

>quoted string? I am just guessing, I am new to Access to Oracle
>mapping.
>
>Any help will be greatly appreciated? Is there anyway I can
>create a table in oracle with 30 characters max long name
>and then map them to Access long names somehow?
>
>Thanks a lot,
>
>Prem

Tom:

Thanks a lot. I inherited this Access Datacase. It lareday has severalqueries, forms, reports. If I rename columns, I may have to modify
existing code. Is there a transparent way, e.g., like Oracle Views
where names can be changed without impacting existing code.

All I have to do is to move Access Tables into Oracle and still use
existing Access front end.

Prem

Tom:
Thanks a lot. I will give it a try.

Prem