By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

problem exporting access table in oracle database

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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


Nov 13 '05 #2

P: n/a

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

Nov 13 '05 #3

P: n/a
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


Nov 13 '05 #4

P: n/a

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

Nov 13 '05 #5

P: n/a

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

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.