Converting from Access to Oracle

How to export Access tables to Oracle

The biggest problems are (going from Access to Oracle):

Hidden Columns

• Make sure you unhide all columns, they may cause problems

  Names

• mixed case names- these go over to mixed case names in Oracle which then cause problems so one has to rename them to upper case (have to use quotes around field names and table names to be able to access them) eg select * from people will not find the table in Oracle one has to do select * from "People". To get round this one has to do things like: rename "People" to people or even create table people as select "Person_code" as person_code,... from "People"

  This is a useful script to run from sqlplus to rename your fields. When you run it remember to put the name of the table exactly as it appears in Access since it is case sensitive. When the new file is created then it must be edited and can then be run to create the new table

  rem remember to change the comma on last line to 'from temp;'
  set pagesize 0
  undefine what_table
  set feedback off
  set trimspool on
  set verify off
  spool &&what_table..sql
  select 'drop table temp;' from sys.dual;

  select 'rename "&&what_table" to temp;' from sys.dual;
  select 'create table &&what_table as select ' from sys.dual;
  select '"'||column_name||'" '
  ||upper(column_name) ||','
  from user_tab_columns where table_name='&&what_table'
  /
  spool off
  

• ORACLE field and table names can not contain spaces or symbols. Anything except for underline is ignored when exported.
• comments: NEVER call a field date in Access especially if you will be exporting to Oracle, Access lets you and somehow Oracle leaves it with that name- it can be intermittently disastrous in Access and is a reserved word in Oracle (so you have to stick the name in capitals and double quotes to even access it) eg
  select "DATE" from tablename
• Names are limited to 30 characters (Access 64).
  You may not have more than 240 fields in an ORACLE table (Access 255).
  There are also more reserved words in ORACLE that you may not call a table or field, such as 'comment', 'level', 'current', so if you are thinking of creating a table in Access and exporting it to ORACLE later, you should bear this in mind.
  Access names beginning with a digit will have the digit replaced by '_' and this causes more problems due to duplicated columns if for example one had columns called 2a and 3a then these will both try to be exported as _a and the table cannot be exported. Oracle then has problems with a fieldname begining with an underscore anyway.

  data types

  Access type	maps to Oracle type
  yes/no	number(5,0)
  number(byte)	number(5,0)
  number(integer)	number(5,0)
  number(long integer)	number(10,0)
  number(single)	float
  number(double)	float
  currency	float
  date/time	date
  counter	number(10,0)
  text(n)	char(20)
  memo	long(v7,blob v8)
  OLE object	long raw

  Oracle type	maps to Access type
  number(1-4,0)	number(integer)
  number(5-9,0)	number(long integer)
  number(10-15,0)	number(double)
  number(16-38)	text
  number(1-15,n)	number(double)
  number(16-38)	text
  float	number(double)
  date	date/time
  char(n)	text(n)
  raw(n)	binary(n)
  long	memo
  longraw	OLE object

• text fields in Access (max length 255) exported to varchar2 (max length 2000) in Oracle version 7, blob version 8. It is better to add an extra varchar2 field in the oracle 8 table and copy the data from the blob field to the varchar2 field using Access since blobs can be difficult to handle in SQL
• remember that searches are case sensitive in Oracle databases but not in Access databases so you need to use string functions in searches
• memo fields cause problems; sequences have to be created; booleans changed to text fields
• yes/no fields are converted to numeric with no set to zero, yes to -1. It is better to change to text before exporting since you cannot change an oracle data type when the field has contents. Check boxes based on Oracle text fields with values yes/no will still work properly
• Long integer fields must be Oracle number(10,0). If you define the Oracle data type as integer it will be set to number(38,0) so that when you look at the attached definition the number field appears to be text length 38 - which is very confusing. Relationships no longer work and Access comes up with the error This expression is typed incorrectly, or is too complex to be evaluated on master-detail forms, or type mismatch on query relationships
• The Oracle ODBC drivers up to and including version 8.1.6 display the following error message:
  ODBC - call failed [Oracle][ODBC][ORA] ORA-01401: Inserted value too large for column (#1401)
  when trying to export an Access table to Oracle. CAUSE: In Microsoft Access 97, the export process converts the Single and Double data type fields to VarChar2(40). However, in Microsoft Access 2000 and later, the fields are converted to VarChar2(4), which it too small to hold the data. This may also happen with long integer- so change long integer to integer (can be a problem if have an auto-counter though!). If this fails you could change numeric fields to text before exporting but this has the problem that you can not directly modify text back to nummeric in Oracle because the field is populated. In this case it is better to create an empty table in Oracle with the correct field definitions (dont forget to add a primary key) and then construct a query in Access to populate the table from the Access table.
• You can not set up automatic counters unless you are using ORACLE ODBC to an Oracle7 database with default values set to a sequence built into the data design. You cannot use the Access counter to generate sequence numbers, and cannot access a sequence from Oracle.
  To get round this, create an query to find max(id)+1, then create a form based on that query, setting scrolbars and navigation selectors to no. Then embed this form on the data entry form. You can then either create a macro to set the id on the main form to this value or set the default value of the id to:
  [Forms]![mainformname]![subformname]![next_id]
  Add a button onto the form to add new record- using the navigation bar is disastrously slow since it puts all the records into memory and thrashes for ever
• Access Fields containing OLE objects will not successfully be imported into the ORACLE longraw datatype since it only holds 65Kb so the picture will come out very fuzzy. Since you can not change the table definition, and ORACLE version 6 does not enforce data validation (except for null values and non-unique primary keys), you will have to build validation and defaults into your Access forms.

  Relationships and Constraints

• Relationships are built automatically between attached tables in Access if you use the same field names and data types, but you can also set up relationships.

  One has to make sure that primary and foreign keys are set up in Oracle when one has exported the data from Access and that data constraints are added

  data content

• case of text ignored in Access queries but significant in Oracle queries

  Queries

• Taking queries from Access to Oracle may or may not be straightforward depending on how standard the SQL is - again I have documented differences between Access SQL and Oracle SQL Comparison of Oracle and Microsoft Access SQL
• ODBC call failed usually indicates missing indexes, primary keys and joins

  Changing an Access database to reflect attachments

• When one changes to using ODBC instead of local tables in Access it means that all queries, forms and reports have to be changed if the names have been changed....

  When you attach or download an Oracle table, a list of Oracle tablenames appears in the format: ownername.tablename.

Summary of speeding up linked table applications

• Avoid jumping to the last record of a large table unless you need to add new records
• View only the data you need and dont scroll up and down unecessarily
• If you frequently add new records, create a special data entry form with Data Entry set to yes. This avoids Access displaying existing records (to see all the records choos Records/Remove filter/sort
• Use queries and filters to limit the number of records you view. Binoculars are very slow
• Avoid using functions, especially aggregates since all the data has to be brought over the network so that Access can process it. It may be worth holding local tables if the data is pretty static and introducing a method of refreshing the values so that it does not get out of step
• If a query contains expressions that can not be evaluated by Oracle, the rest of the query is evaluated locally. Totals, union and distinct queries are evaluated by Access. Where, order by and group by clauses cause at least part of the query to be evaluated by Access.
• Access specific extensions such as top or the topvalues and reports that have multiple levels of grouping and totals are all performed by Access
• Access financial and domain aggregate functions are specific to Access so have to be performed locally
• User defined functions in Visual Basic that have remote fields as arguments will be processed by Access
• Heterogeneous joins between local and linked tables, or between tables from different ODBC sources will be performed locally
• Expressions or union queries that mix text and numeric data types will be performed locally since Access is more lenient than Oracle
• Avoid locking records for longer than you need
• Avoid using drop down boxes based on linked tables