School of Information Management and Systems

IS 257 –Database Management

Converting Access Databases to Oracle

 

Saving delimited text files from Access:
  1. Login to your lab account as normal.
  2. Create a new folder to hold your exported data.
  3. Start Access and load your database.
  4. Highlight the table you want to export.
  5. Select Save As/Export from the File menu.
  6. In the "Save As" dialog box be sure that "to an external file or database" is selected, then click OK.
  7. In the "Save Table … In" file browser box, open the new folder and select "Text files" in the "Save as type" pop-up menu, enter a name for the file (or take the default name), then click Export.
  8. In the "Export Text Wizard", select the "Delimited" format, then click Next.
  9. On the next page choose "Comma" as the delimiter and make sure the Text Qualifier is a double quote mark. Click Next to continue.
  10. On the next page change the file name if desired, then click Finish
  11. Repeat these steps for each file you wish to export.

    12.  

    Creating Tables in Oracle:

    I suggest creating an SQL command file that contains multiple CREATE TABLE SQL commands, one for each table you want to create. The command files might look like the following examples from the Diveshop database:

    CREATE TABLE BIOLIFE2 (

    Species_No Number (10),

    Category VARCHAR2(20),

    Common_Name VARCHAR2(30),

    Species_Name VARCHAR2(60),

    Length_cm NUMBER(8,2),

    Length_in NUMBER(8,2),

    Notes VARCHAR2(1000),

    Graphic2 VARCHAR2(20));

     

    CREATE TABLE BIOSITE2 (

    Species_No NUMBER(10),

    Site_No NUMBER(10));

     

    CREATE TABLE DEST2 (

    Destination_No NUMBER(10),

    Destination_Name VARCHAR2(60),

    Avg_Temp_F NUMBER(8,2),

    Avg_Temp_C NUMBER(8,2),

    Spring_Temp_F NUMBER(8,2),

    Spring_Temp_C NUMBER(8,2),

    Summer_Temp_F NUMBER(8,2),

    Summer_Temp_C NUMBER(8,2),

    Fall_Temp_F NUMBER(8,2),

    Fall_Temp_C NUMBER(8,2),

    Winter_Temp_F NUMBER(8,2),

    Winter_Temp_C NUMBER(8,2),

    Accomodations VARCHAR2(40),

    Night_Life VARCHAR2(40),

    Body_of_Water VARCHAR2(40),

    Travel_Cost NUMBER(8,2));

    CREATE TABLE DIVECUST2 (

    Customer_No NUMBER(10),

    Name VARCHAR2(100),

    Street VARCHAR2(100),

    City VARCHAR2(50),

    State_Prov VARCHAR2(30),

    Zip_Postal_Code VARCHAR2(20),

    Country VARCHAR2(30),

    Phone VARCHAR2(20),

    First_Contact DATE);

    Etc…

     

    Be sure each SQL create table statement is terminated by a semicolon. See the earlier handouts for the datatypes permitted in Oracle databases.

    You should probably also create a command file to remove the database, it would look like the following:

    DROP TABLE BIOLIFE2 ;

    DROP TABLE BIOSITE2 ;

    DROP TABLE DEST2 ;

    DROP TABLE DIVECUST2 ;

    Etc…

    This can be used to completely remove the tables and their data when there are problems with loading

    Login to the Unix machine "irony" :

  12. Login to your lab account as normal.
  13. From the Start menu, choose Programs/Internet/SSH – Telnet/SSH - Irony.
  14. In the "password" dialog box, enter your lab password.
  15. At the unix command prompt enter "sqlplus" to start interacting with Oracle
  16. At the "Enter user-name:" prompt enter your email name (usually the same as your lab login).
  17. At the "Enter password:" prompt enter your last name (if that doesn’t work try your first name).
  18. At the "SQL>" prompt enter "@createfilename", where createfilename is the name you saved the command file under – alternatively you could type in the create commands one by one.

    19.  

    Edit the exported Access file for use in SQL*Loader

     

  19. Edit each of the exported files from access to include the following lines (this example is for DIVECUST):

    20. LOAD DATA

    INFILE *

    INTO TABLE DIVECUST2

    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

    (Customer_No , Name, Street , City , State_Prov , Zip_Postal_Code , Country , Phone , First_Contact DATE(8) "MM/DD/YY")

    BEGINDATA

    1480.00,"Louis Jazdzewski","2501 O'Connor","New Orleans", "LA", "60332", "U.S.A.","(902) 555-8888",1/29/91

    1481.00,"Barbara Wright","6344 W. Freeway","San Francisco","CA","95031","U.S.A.","(415) 555-4321",2/2/91

    1909.00,"Stephen Bredenburg","559 N.E. 167 Place","Indianapolis","IN","46241","U.S.A.","(317) 555-3644",1/5/91

    1913.00,"Phillip Davoust","123 First Street","Berkeley","CA","94704","U.S.A.","(415) 555-9184",3/9/91

     

  20. Note that the field names used in the CREATE TABLE command are listed in the order that the fields appear in the data.
  21. The First_Contact field in the data is declared to be a DATE and the input format for the date is given – this must be done for any DATE fields in your data.
  22. Note also that each row from the Access database will appear on a single line in the exported file, it is not shown that way here because the word processor automatically split the lines.
  23. Once you are satisfied with the export file run the sqlload command from the UNIX prompt (not from within sqlplus) as follows:

    24.  

    sqlload userid=youroracleid/youroraclepass control=exportfilename log=somename.log

     

  24. Once the command has completed, examine the log file to see if it was successful.
  25. If there were errors in the data or control file, you will need to correct them and then drop and re-create the tables (if partial data was loaded) and run the sqlload command again.
  26. Repeat these steps for each table of exported data.