Importing Delimited Device Files into an Oracle Database using Oracle SQL Loader

Importing Delimited Device Files into an Oracle Database using Oracle SQL Loader

The Pipe-Delimited | device files provide a relational view of the device record data. These files can be imported into a database for easier reading and interaction.

Notice

Be aware of the file size and row limitations of the software you are attempting to import the files into. For information on limitations, look at the software's official documentation

Directions

The following directions are for the Oracle SQL Plus and SQL Loader tool, but similar actions can be taken in other SQL tools and database types.
  1. Extract the Import Scripts to your workarea.
  2. Place your Delimited Device Files into the data directory.
  3. Update and uncomment the runner script with the appropriate parameters for your environment.
    • If you are using a Linux computer to run these scripts, update the oracle/populate_ag_tables.sh file.
    • If you are using a Windows computer to run these scripts, update the oracle/populate_ag_tables.bat file.
    The variables that need to be updated are as follows:
    Parameter Description
    ORACLE_HOME The location of your Oracle software binaries
    user The user name to use to connect to your database schema
    password The password to use to connect to your database schema
    tns_name The server name (or SID) of your database
  4. Execute the runner script
    • If you are using a Linux computer, run the oracle/populate_ag_tables.sh file.
    • If you are using a Windows computer, run the oracle/populate_ag_tables.bat file.
  5. Check the *.log files generated in the oracle directory for a generated report.
    Also check the bad and discard folder for any discarded records.

Download Import Scripts

You can download the Import Scripts here. The following files can be found in the download:
Directory/File Name Description
oracle/populate_ag_tables.sh Shell Script runner for all ctl and sql files in a Linux Environment.
Note: You must update the parameters ORACLE_HOME, user, password, and tns_name to connect to your database and use your SQL Loader and SQL Plus applications
oracle/populate_ag_tables.bat Batch runner for all ctl and sql files in a Windows Environment.
Note: You must update the parameters ORACLE_HOME, user, password, and tns_name to connect to your database and use your SQL Loader and SQL Plus applications
oracle/ag_build_tables.sql Drops and Creates new tables that the ctl scripts will populate
oracle/ag_device_sizes.ctl Control File for the Delimited Device File data/deviceSizes.txt
Populates the AG_DEVICE_SIZES table
oracle/ag_contacts.ctl Control File for the Delimited Device File data/contacts.txt
Populates the AG_CONTACTS table
oracle/ag_sterilization_methods.ctl Control File for the Delimited Device File data/sterilizationMethodTypes.txt
Populates the AG_STERILIZATION_METHODS table
oracle/ag_identifiers.ctl Control File for the Delimited Device File data/identifiers.txt
Populates the AG_IDENTIFIERS table
oracle/ag_product_codes.ctl Control File for the Delimited Device File data/productCodes.txt
Populates the AG_PRODUCT_CODES table
oracle/ag_gmdn_terms.ctl Control File for the Delimited Device File data/gmdnTerms.txt
Populates the AG_GMDN_TERMS table
oracle/ag_environmental_conditions.ctl Control File for the Delimited Device File data/environmentalConditions.txt
Populates the AG_ENVIRONMENTAL_CONDITIONS table
oracle/ag_device.ctl Control File for the Delimited Device File data/device.txt
Populates the AG_DEVICE table
data/* This directory will contain the Delimited Device Files you will process.
bad/* This directory will contain the .bad files that get generated any time a bad record is read during processing
discard/* This directory will contain the .dsc files that get generated any time record is discarded during processing