Operating System - HP-UX
1748171 Members
4635 Online
108758 Solutions
New Discussion юеВ

Re: URGENT !!! ACCESS 2 ORACLE

 
Prabhu_7
Frequent Advisor

URGENT !!! ACCESS 2 ORACLE

Hi,

I got a table in ACCESS (access 2000) and i used Export option and exported to Oracle (8.1.7) using ODBC.

I could see the table in Oracle when i issue
Select * from user_tables where table_name = 'Acc_Tab'

But when i say
Select * from Acc_Tab

I get table or view doesnt exist...

i guess its something to do with GRANT ?

any thought ? can we give full permission when exporting ?

Thanks
9 REPLIES 9
Michael Schulte zur Sur
Honored Contributor

Re: URGENT !!! ACCESS 2 ORACLE

Hi,

I assume, that owner and odbc user aren't the same. The odbc user must have a select right for the object.

greetings,

Michael
Steven E. Protter
Exalted Contributor

Re: URGENT !!! ACCESS 2 ORACLE

To completely migrate the data:

consider ascii export and then sqlloader to do a custom load into the Oracle table.

Otherwise you'll need to work on the ODBC issues and make sure its working properly on both ends and the user role being used has adequate permissions.

I suspect you are on the right track.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Michael Schulte zur Sur
Honored Contributor

Re: URGENT !!! ACCESS 2 ORACLE

One more,

did you login as the owner of the table in oracle?

Michael
Prabhu_7
Frequent Advisor

Re: URGENT !!! ACCESS 2 ORACLE

Thanks All.....

I dont know how much this makes sense.
But this solved my problem.

While exporting from ACCESS...i need to give table name in CAPS. initially i was giving in mixed case. when i gave it in CAPS ...
i was able to query table in ORacle.
I tried exporting with mixed case again...
and got same error. so guess we need to specify name in CAPS.

any thoughts ? if i'm missing my way.
Michael Schulte zur Sur
Honored Contributor

Re: URGENT !!! ACCESS 2 ORACLE

Hi,

oracle keeps many infos in upper case.
If you have to query for a table then you have to specify table_name in upper case.
I find it a bit annoying. In other places it is not necessary. They should have been a bit more consistent.

Michael
Michael Schulte zur Sur
Honored Contributor

Re: URGENT !!! ACCESS 2 ORACLE

like

select * from dba_tables where owner='SYS';

Michael
Supportdesk_3
Advisor

Re: URGENT !!! ACCESS 2 ORACLE

Prabhu,

Oracle expects object names (Tables, views, etc) to be uppercase. When you enter a lowercase table name oracle will convert it to uppercase.

In your case Access forced oracle to use upper and lowercase characters for the table name. Oracle will except this, but when you want to select from this table you have to give the upper&lowercase name. in your case 'Acc_Tab'. If you forget the quotes Oracle is looking for ACC_TAB instead of Acc_Tab.

The way to solve this is create the table in access ACC_TAB(uppercase). Oracle will work the way you would expect.

Hope it helps.

Regards,

Wim
Brian Crabtree
Honored Contributor

Re: URGENT !!! ACCESS 2 ORACLE

You will also be able to access the table with:

select * from "Acc_Tab";

Brian
Michael Schulte zur Sur
Honored Contributor

Re: URGENT !!! ACCESS 2 ORACLE

Brian,

don't you have to use single quotes?

Michael