RE: Importing Access tables in Oracle
I other words the table names are case sensitive. But the issue you are running into is this: You currently try and receive an ora-942 select * from tablename; When you should do: select * from "TableName"; MicroSoft products (whether access or sql server) will cause this. Good luck trying to find the exact case sensitivity and naming convention. The suggestion below seems to be the most efficient one. Thanks, C- -Original Message- Sent: Wednesday, December 19, 2001 12:51 PM To: Multiple recipients of list ORACLE-L You are learning the hard way. NEVER, NEVER, NEVER create tables in Oracle by exporting from MS Access via ODBC. Access creates non-SQL compliant objects. To access these tables you will need to select "Field", "Field" from "Table Name"; using the exact spelling and case as displayed in Access. To get rid of them you will need to drop them in the same manner. The correct way to do what you are doing is to create the objects in oracle with the names, columns, etc as they ought to be created. ie: correctly. Then ATTACH those tables into your Access database. Then write sql append queries that select from your Access tables and append into the Oracle tables. This is a little more cumbersome to do, but at least the structures will be correct. On Wed, 2001-12-19 at 13:12, [EMAIL PROTECTED] wrote: Dear all, I have a lot of tables built in MS Access. Recently, I am trying to export the database from MS Access to Oracle 8i.From Access, I could export the tables to Oracle through Oracle ODBC driver. The good thing is that this process is relatively fast as Table struture is created automatically and I can actually see these tables and data in Toad. But if I try to execute any SQL, Oracle responds ORA-00942: table or view does not exist. I cannot figure out what is happenning. I welcome any suggestion (including not exporting through ODBC driver) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] -- Rodd Holman Enterprise Data Systems Engineer LodgeNet Entertainment Corporation [EMAIL PROTECTED] (605) 988-1373 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christine Turner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Importing Access tables in Oracle
You are learning the hard way. NEVER, NEVER, NEVER create tables in Oracle by exporting from MS Access via ODBC. Access creates non-SQL compliant objects. To access these tables you will need to select "Field", "Field" from "Table Name"; using the exact spelling and case as displayed in Access. To get rid of them you will need to drop them in the same manner. The correct way to do what you are doing is to create the objects in oracle with the names, columns, etc as they ought to be created. ie: correctly. Then ATTACH those tables into your Access database. Then write sql append queries that select from your Access tables and append into the Oracle tables. This is a little more cumbersome to do, but at least the structures will be correct. On Wed, 2001-12-19 at 13:12, [EMAIL PROTECTED] wrote: Dear all, I have a lot of tables built in MS Access. Recently, I am trying to export the database from MS Access to Oracle 8i.From Access, I could export the tables to Oracle through Oracle ODBC driver. The good thing is that this process is relatively fast as Table struture is created automatically and I can actually see these tables and data in Toad. But if I try to execute any SQL, Oracle responds ORA-00942: table or view does not exist. I cannot figure out what is happenning. I welcome any suggestion (including not exporting through ODBC driver) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] -- Rodd Holman Enterprise Data Systems Engineer LodgeNet Entertainment Corporation [EMAIL PROTECTED] (605) 988-1373 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Importing Access tables in Oracle
Ensure all table names and column names in Access are in uppercase. If not they will be created in your Oracle data dictionary as lower case which is bad. To drop the tables you will need to do drop table "tblFoo"; as opposed to the normal drop table foo or drop table FOO not the use of the "" to get Oracle to see the table. - Ethan -Original Message- Sent: Wednesday, December 19, 2001 1:13 PM To: Multiple recipients of list ORACLE-L Dear all, I have a lot of tables built in MS Access. Recently, I am trying to export the database from MS Access to Oracle 8i.From Access, I could export the tables to Oracle through Oracle ODBC driver. The good thing is that this process is relatively fast as Table struture is created automatically and I can actually see these tables and data in Toad. But if I try to execute any SQL, Oracle responds ORA-00942: table or view does not exist. I cannot figure out what is happenning. I welcome any suggestion (including not exporting through ODBC driver) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
