topic Re: URGENT !!! ACCESS 2 ORACLE in Operating System - HP-UX

URGENT !!! ACCESS 2 ORACLE

Prabhu_7 — Tue, 09 Mar 2004 10:49:10 GMT

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

Re: URGENT !!! ACCESS 2 ORACLE

Michael Schulte zur Sur — Tue, 09 Mar 2004 11:24:20 GMT

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

Re: URGENT !!! ACCESS 2 ORACLE

Steven E. Protter — Tue, 09 Mar 2004 11:55:07 GMT

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

Re: URGENT !!! ACCESS 2 ORACLE

Michael Schulte zur Sur — Tue, 09 Mar 2004 12:02:43 GMT

One more,

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

Michael

Re: URGENT !!! ACCESS 2 ORACLE

Prabhu_7 — Tue, 09 Mar 2004 12:21:13 GMT

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.

Re: URGENT !!! ACCESS 2 ORACLE

Michael Schulte zur Sur — Tue, 09 Mar 2004 12:35:09 GMT

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

Re: URGENT !!! ACCESS 2 ORACLE

Michael Schulte zur Sur — Tue, 09 Mar 2004 12:37:10 GMT

like

select * from dba_tables where owner='SYS';

Michael

Re: URGENT !!! ACCESS 2 ORACLE

Supportdesk_3 — Wed, 10 Mar 2004 03:33:47 GMT

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

Re: URGENT !!! ACCESS 2 ORACLE

Brian Crabtree — Wed, 10 Mar 2004 16:25:21 GMT

You will also be able to access the table with:

select * from "Acc_Tab";

Brian

Re: URGENT !!! ACCESS 2 ORACLE

Michael Schulte zur Sur — Wed, 10 Mar 2004 17:27:45 GMT

Brian,

don't you have to use single quotes?

Michael