Unloading and Loading Data with Wizards

Example: Unloading Data with the Unload Wizard

Suppose you want to unload the REGIONS table, which is part of the HR sample schema, so that it can be used in another application. Suppose also that you want to create a tab-delimited text file, and you want to save the data in a file called regions.txt.

To unload the REGIONS table:

1. Log in to the Oracle Database XE graphical user interface as the HR user.

   See "Accessing the Database Home Page" for information on getting logged in.

   
   

   Note: An administrator must first unlock the HR account and assign a password. See "Logging In as an Administrator" and "Locking and Unlocking User Accounts" for instructions.

   
   

2. On the Database Home Page, click the Utilities icon, and then click the Data Load/Unload icon.

3. On the Data Load/Unload page, click the Unload icon, and then click the Unload to Text icon.

   The Unload to Text page appears, showing the Schema wizard step. This wizard step displays a Schema list, in which HR is selected. Because you can unload from your own schema only, you cannot change this selection.

4. Click Next.

   The Table Name wizard step appears.

5. From the Table list, select REGIONS, and then click Next.

   The Columns wizard step appears.

   
   Description of the illustration exp_columns.gif
   
   

6. Select all columns by clicking and dragging or by clicking and shift-clicking, and then click Next. (You can also select a subset of columns. Deselected columns are excluded from the unload operation.)

   The Options wizard step appears.

7. Complete the following steps:

   1. In the Separator field, remove the comma if present, and enter a backslash and a lowercase T (\t) to indicate that you want the tab character to be the field delimiter. (You can use any character as the delimiter.)

   2. Select the Include Column Names check box.

      This causes the first row unloaded to be the column names, rather than the first row of data. You can use this first row to set column names when you load.

   3. In the File Character Set list, select Unicode UTF-8.

      
      Description of the illustration exp_options.gif
      
      

8. Click Unload Data.

   A Save As window appears, with the file name regions.txt filled in. Depending on your browser, another window may precede the Save As window, asking you if you want to save or open the file. If so, take the option to save the file to disk.

9. Save the file regions.txt to the Desktop or to a directory of your choice.

10. (Optional) Open the regions.txt file with a text editor or spreadsheet application to verify that the REGIONS table was unloaded properly.

Example: Loading Data with the Load Wizard

Suppose your application calls for a REGIONS table, where each row contains a region number and a region name. Suppose also that you previously unloaded region data from a desktop database system into a tab-delimited text file named regions.txt.

You want to use the region number field in each record as a business key but not as the primary key, and you therefore decide to have the Load wizard generate a numeric primary key for each loaded record.

To load the REGIONS table:

1. Log in to the Oracle Database XE graphical user interface as any user other than SYSTEM or HR.

   To log out first, click the Logout button at the upper right-hand corner of the page. See "Accessing the Database Home Page" for information on getting logged in. If no database user other than SYSTEM or HR exists, create one. See "Creating Users" for instructions.

2. On the Database Home Page, click the Utilities icon, and then click the Data Load/Unload icon.

3. On the Data Load/Unload page, click the Load icon, and then click the Load Text Data icon.

   The Load Data page appears, showing the Target and Method wizard step.

4. Under the Load To heading, select New table, and under the Load From heading, select Upload file (comma separated or tab delimited).

5. Click Next.

   The File Details wizard step appears.

6. Complete the following steps:

   1. Click Browse, select the regions.txt file, and then click Open.

   2. In the Separator field, replace the comma with a backslash and a lowercase T (\t) to indicate that the field delimiter is a tab character.

   3. In the File Character Set list, select Unicode UTF-8.

      
      Description of the illustration imp_fileinfo.gif
      
      

   4. Click Next.

   The Table Properties wizard step appears.

7. Complete the following steps:

   1. In the Table Name field, enter REGIONS.

      
      Description of the illustration imp_tableinfo.gif
      
      

   2. Accept the default (Yes) in all Upload lists.

      Setting Upload to No excludes the column from the load operation.

   3. Click Next.

   The Primary Key wizard step appears.

8. Complete the following steps:

   1. Next to the Primary Key From label, select Create new column.

   2. Next to the Primary Key Population label, select Generated from a new sequence.

      
      Description of the illustration imp_pk.gif
      
      

   These selections cause Oracle Database XE to:

   • Create an additional table column called ID, which is used as the primary key for REGIONS.

   • Create a new sequence called REGIONS_SEQ.

   • Use the values from the sequence to populate the ID field as each new row is added.

   If you did not want to create a new primary key, and wanted to instead use the existing REGION_ID field as the primary key, you would do the following:

   • Select Use an existing column.

   • In the Primary Key list, select REGION_ID(NUMBER).

   • Select Not generated.

   
   Description of the illustration imp_pk_alt.gif
   
   

9. Click the Load Data button.

   The load proceeds, and when it is complete, the Text Data Load Repository page appears, showing the regions.txt file at the top of the list of loaded files.

10. Check the load status by looking under the Succeeded and Failed columns for the regions.txt file.

    The numbers in these columns indicate the number of rows that were successfully loaded or that caused an error.