Skip to main content
U.S. flag

An official website of the United States government

Official websites use .gov
A .gov website belongs to an official government organization in the United States.

Secure .gov websites use HTTPS
A lock ( ) or https:// means you’ve safely connected to the .gov website. Share sensitive information only on official, secure websites.

Unified Medical Language System® (UMLS®)

UMLS Quick Start Guide   |  FAQs   |  Customer Support

Semantic Network Oracle Load Script

This document is a guide to loading the UMLS Semantic Network into an Oracle database by using the scripts and files created by MetamorphoSys. To generate the necessary files and scripts, select the "Semantic Network" checkbox on the "Install UMLS" screen. Also, select the "Oracle" option from the "Choose Database Load Scripts" pick-list on the "Install UMLS" screen.

Recommended Software Version: Oracle 10g Release 2 (Standard Edition, Enterprise Edition), Oracle 11g (Standard Edition, Enterprise Edition)

Creating a Database

To load the Semantic Network into an Oracle database, you will have to either use an existing database or create a new one. The full complexity of creating an Oracle database is outside the scope of this document. However, we have included some discussion regarding this process below and we do have a couple of specific recommendations. In particular, it is recommended that you specify UTF8 as the CHARACTER SET and NATIONAL CHARACTER SET and configure the database to use "character" instead of "byte" semantics by default when interpreting character field lengths. These settings are configured through three Oracle parameters:

  1. NLS_CHARACTER_SET
  2. NLS_NCHAR_CHARACTERSET
  3. NLS_LENGTH_SEMANTICS

For more details on database creation, see the "Creating a Database" section of the Rich Release Format Oracle Load Script documentation.

Configuring and Running the Load Script

Windows

If you are running MetamorphoSys in a Windows environment, in your output directory (after running MetamorphoSys with the "Semantic Network" box checked and the Oracle database selected) you should see a batch file script called "populate_net_oracle_db.bat". This script can be used to load the Semantic Network into an Oracle database. The directory will also contain an SQL script for creating the tables, and an SQL*Loader control file (.ctl) for each of the data files to be loaded.

SQL*Loader is the Oracle tool for loading text files into a database and has a variety of parameters and configuration options (this too is outside the scope of this document). The control files used by the script to load each file into an Oracle database table are designed to be relatively simple and relatively efficient. If you are interested in more information, you are encouraged to look up the SQL*Loader Control File Reference in the Oracle manual.

Edit the script and configure the four parameters at the top of the script. These are:

set ORACLE_HOME=<path to ORACLE_HOME>
set user=<username>
set password=<password>
set tns_name=<db_name>

ORACLE_HOME should be set to the installation directory of your local Oracle client or server. This may be something like "D:\oracle\product\10.2.0\db_1". After configuring these four parameters, this section of your script may look something like the following:

set ORACLE_HOME=D:\oracle\product\10.2.0\db_1
set user=myuser
set password=p4ssw0rd
set tns_name=UMLSDB

You are now ready to run the script. To do so, navigate to the directory containing the Semantic Network files using the Windows explorer and double-click on the "populate_net_oracle_db.bat" file you just finished editing.

Here are some things to keep in mind when using this script:

  1. Your Oracle installation needs to be able to find the database associated with the "tns_name" you specified.  This is typically done through a "tnsnames.ora" file in the network/admin sub-directory of ORACLE_HOME.  If you created a database using the Database Configuration Assistant, this was probably done for you already. If not, you may have to configure it yourself using a tool like Net Manager. To test the connection, you can use "tnsping" in the bin directory of ORACLE_HOME.
  2. The script will automatically create a log file called "oracle_net.log" in the same directory as the script.  If you are planning on running the script more than once, you should consider renaming the log file before your second run, otherwise the contents of the first run will be lost.
  3. While the script is running, you can monitor the process from within the SQL*Plus command shell by using "SQL> select sid, serial#, username, program  from v$session;".
  4. When this load completes, each of the tables will be loaded.
  5. If you are running Oracle 10g and have the recyclebin parameter to ON, drop table statements in the load scripts do not purge the recyclebin area.  If you are using scripts to reload the UMLS, be aware that at the conclusion of the load, it may be desirable to "purge recyclebin" to ensure you are not keeping unwanted data around.

Linux, Macintosh, or Solaris

If you are running MetamorphoSys in a Linux, Macintosh, or Solaris environment, in your output directory (after running MetamorphoSys with the "Semantic Network" box checked and the Oracle database selected) you should see a .sh shell script called "populate_net_oracle_db.sh". This script can be used to load the Semantic Network into an Oracle database. The directory will also contain an SQL script for creating the tables, and an SQL*Loader control file (.ctl) for each of the data files to be loaded.

SQL*Loader is the Oracle tool for loading text files into a database and has a variety of parameters and configuration options (this too is outside the scope of this document). The control files used by the script to load each file into an Oracle database table are designed to be relatively simple and relatively efficient. If you are interested in more information, you are encouraged to look up the SQL*Loader Control File Reference in the Oracle manual.

Edit the script and configure the four parameters at the top of the script. These are:

export ORACLE_HOME=<path to ORACLE_HOME>
user=<username>
password=<password>
tns_name=<tns_name>

ORACLE_HOME should be set to the installation directory of your local Oracle client or server. This may be something like "/u01/app/oracle/product/10.2.0". After configuring these four parameters, this section of your script may look something like the following:

export ORACLE_HOME=/u01/app/oracle/product/10.2.0
user=myuser
password=p4ssw0rd
tns_name=umls

Note: Passwords containing $ or # characters may cause problems as these have special meaning within the shell environment. In this case, you may want to enclose the password in quotes in the script itself (e.g. password="pass#word"). You are now ready to run the script. To do so, navigate to your the directory containing the Semantic Network files and invoke the script from the command line. It may be necessary to change the file permissions to make the script executable before it can be started. For example:

% cd <directory with Semantic Network files>
% chmod 775 populate_net_oracle_db.sh
% populate_net_oracle_db.sh &

Here are some things to keep in mind when using this script:

  1. Your Oracle installation needs to be able to find the database associated with the "tns_name" you specified. This is typically done through a "tnsnames.ora" file in the network/admin sub-directory of ORACLE_HOME.  If you created a database using the Database Configuration Assistant, this was probably done for you already. If not, you may have to configure it yourself using a tool like Net Manager. To test the connection, you can use "tnsping" in the bin directory of ORACLE_HOME.
  2. The script will automatically create a log file called "oracle.log" in the same directory as the script. If you are planning on running the script more than once, you should consider renaming the log file before your second run, otherwise the contents of the first run will be lost.
  3. While the script is running, you can monitor the process from within the SQL*Plus command shell by using "SQL> select sid, serial#, username, program  from v$session;"
  4. When the load completes, each of the tables will be loaded.

Sample Queries

We have provided a number of sample queries to demonstrate how to access certain kinds of data within the Semantic Network.

Semantic Type Queries

Get a semantic type by value.
SELECT * FROM SRDEF
WHERE sty_rl = ? AND rt = 'STY';

Get a semantic type by TUI.
SELECT * FROM SRDEF
WHERE ui = ? AND rt = 'STY';

Get parents of a semantic type by value.
SELECT b.* FROM SRSTR a, SRDEF b
WHERE sty_rl1 = ?
     AND rl = 'isa' AND b.rt = 'STY' AND a.sty_rl2 = b.sty_rl;

Get parents of a semantic type by TUI.
SELECT b.* FROM SRSTR a, SRDEF b, SRDEF c
WHERE c.ui = ?
     AND a.sty_rl1 = c.sty_rl AND rl = 'isa'
     AND b.rt = 'STY' AND a.sty_rl2 = b.sty_rl
     AND c.rt = 'STY';

Get children of a semantic type by value.
SELECT b.* FROM SRSTR a, SRDEF b
WHERE sty_rl2 = ?
     AND rl = 'isa' AND b.rt = 'STY' AND a.sty_rl1 = b.sty_rl;

Get children of a semantic type by TUI.
SELECT b.* FROM SRSTR a, SRDEF b, SRDEF c
WHERE c.ui = ?
     AND a.sty_rl2 = c.sty_rl AND rl = 'isa'
     AND b.rt = 'STY' AND a.sty_rl1 = b.sty_rl
     AND c.rt = 'STY';

Get siblings of a semantic type by value.
SELECT c.* FROM SRSTR a, SRSTR b, SRDEF c
WHERE a.sty_rl1 = ?
     AND a.rl = 'isa' AND b.rl = 'isa'
     AND a.sty_rl2 = b.sty_rl2 AND a.sty_rl1 != b.sty_rl1
     AND c.rt = 'STY' AND c.sty_rl = b.sty_rl1;

Get siblings of a semantic type by TUI.
SELECT c.* FROM SRSTR a, SRSTR b, SRDEF c, SRDEF d
WHERE d.ui = ?
     AND a.rl = 'isa' AND b.rl = 'isa'
     AND a.sty_rl2 = b.sty_rl2 AND a.sty_rl1 != b.sty_rl1
     AND c.rt = 'STY' AND b.sty_rl1 = c.sty_rl
     AND d.rt = 'STY' AND a.sty_rl1 = d.sty_rl;

Get ancestors of a semantic type by value.
SELECT b.* FROM SRSTRE2 a, SRDEF b
WHERE sty1 = ?
     AND rl = 'isa' AND b.rt = 'STY' AND a.sty2 = b.sty_rl
ORDER BY stn_rtn;

Get ancestors of a semantic type by TUI.
SELECT b.* FROM SRSTRE1 a, SRDEF b
WHERE ui1 = ?
     AND b.rt = 'STY' AND a.ui3 = b.ui
     AND a.ui2 IN (SELECT ui FROM srdef WHERE rt= 'RL' and sty_rl = 'isa')
ORDER BY stn_rtn;

Get descendants of a semantic type by value.
SELECT b.* FROM SRSTRE2 a, SRDEF b
WHERE sty2 = ?
     AND rl = 'isa' AND b.rt = 'STY' AND a.sty1 = b.sty_rl
ORDER BY stn_rtn;

Get descendants of a semantic type by TUI.
SELECT b.* FROM SRSTRE1 a, SRDEF b
WHERE ui3 = ?
     AND b.rt = 'STY' AND a.ui1 = b.ui
     AND a.ui2 IN (SELECT ui FROM srdef WHERE rt= 'RL' and sty_rl = 'isa')
ORDER BY stn_rtn;

Get defined relationships for a semantic type by value.
SELECT a.rl, b.ui rl_ui, sty_rl2 sty, c.ui sty_ui FROM SRSTR a, SRDEF b, SRDEF c
WHERE sty_rl1 = ?
     AND ls = 'D' AND a.rl = b.sty_rl AND b.rt = 'RL'
     AND a.sty_rl2 = c.sty_rl AND c.rt = 'STY';

Get defined relationships for a semantic type by TUI.
SELECT a.rl, b.ui rl_ui, sty_rl2 sty, c.ui sty_ui FROM SRSTR a, SRDEF b, SRDEF c, SRDEF d
WHERE d.ui = ?
     AND ls = 'D' AND a.rl = b.sty_rl AND b.rt = 'RL'
     AND a.sty_rl2 = c.sty_rl AND c.rt = 'STY'
     AND a.sty_rl1 = d.sty_rl AND d.rt = 'STY';

Get all relationships for a semantic type by value (including inherited relationships).
SELECT a.rl, b.ui rl_ui, sty2 sty, c.ui sty_ui FROM SRSTRE2 a, SRDEF b, SRDEF c
WHERE sty1 = ?
     AND a.rl = b.sty_rl AND b.rt = 'RL'
     AND a.sty2 = c.sty_rl AND c.rt = 'STY';

Get all relationships for a semantic type by TUI (including inherited relationships).
SELECT a.rl, b.ui rl_ui, sty2 sty, c.ui sty_ui FROM SRSTRE2 a, SRDEF b, SRDEF c, SRDEF d
WHERE d.ui = ?
     AND a.rl = b.sty_rl AND b.rt = 'RL'
     AND a.sty2 = c.sty_rl AND c.rt = 'STY'
     AND a.sty1 = d.sty_rl AND c.rt = 'STY';

Get all relationships from one semantic type to another by value.
SELECT a.rl, b.ui FROM SRSTRE2 a, SRDEF b
WHERE sty1 = ? AND sty2 = ?
     AND a.rl = b.sty_rl AND b.rt = 'RL';

Get all relationships from one semantic type to another by TUI.
SELECT a.rl, b.ui FROM SRSTRE2 a, SRDEF b, SRDEF c, SRDEF d
WHERE c.ui = ? AND d.ui = ?
     AND a.rl = b.sty_rl AND b.rt = 'RL'
     AND a.sty1 = c.sty_rl AND c.rt = 'STY'
     AND a.sty2 = d.sty_rl AND d.rt = 'STY';

Semantic Network Relation Label Queries

NOTE: Semantic Network relation labels only have "isa" relationships to other labels.

Get a Semantic Network relation label by value.
SELECT * FROM SRDEF
WHERE sty_rl = ? AND rt = 'RL';

Get a Semantic Network relation label by TUI.
SELECT * FROM SRDEF
WHERE ui = ? AND rt = 'RL';

Get parents of a Semantic Network relation label by value.
SELECT b.* FROM SRSTR a, SRDEF b
WHERE sty_rl1 = ?
     AND rl = 'isa' AND b.rt = 'RL' AND a.sty_rl2 = b.sty_rl;

Get parents of a Semantic Network relation label by TUI.
SELECT b.* FROM SRSTR a, SRDEF b, SRDEF c
WHERE c.ui = ?
     AND a.sty_rl1 = c.sty_rl AND rl = 'isa'
     AND b.rt = 'RL' AND a.sty_rl2 = b.sty_rl
     AND c.rt = 'RL';

Get children of a Semantic Network relation label by value.
SELECT b.* FROM SRSTR a, SRDEF b
WHERE sty_rl2 = ?
     AND rl = 'isa' AND b.rt = 'RL' AND a.sty_rl1 = b.sty_rl;

Get children of a Semantic Network relation label by TUI.
SELECT b.* FROM SRSTR a, SRDEF b, SRDEF c
WHERE c.ui = ?
     AND a.sty_rl2 = c.sty_rl AND rl = 'isa'
     AND b.rt = 'RL' AND a.sty_rl1 = b.sty_rl
     AND c.rt = 'RL';

Get siblings of a Semantic Network relation label by value.
SELECT c.* FROM SRSTR a, SRSTR b, SRDEF c
WHERE a.sty_rl1 = ?
     AND a.rl = 'isa' AND b.rl = 'isa'
     AND a.sty_rl2 = b.sty_rl2 AND a.sty_rl1 != b.sty_rl1
     AND c.rt = 'RL' AND c.sty_rl = b.sty_rl1;

Get siblings of a Semantic Network relation label by TUI.
SELECT c.* FROM SRSTR a, SRSTR b, SRDEF c, SRDEF d
WHERE d.ui = ?
     AND a.rl = 'isa' AND b.rl = 'isa'
     AND a.sty_rl2 = b.sty_rl2 AND a.sty_rl1 != b.sty_rl1
     AND c.rt = 'RL' AND b.sty_rl1 = c.sty_rl
     AND d.rt = 'RL' AND a.sty_rl1 = d.sty_rl;

Get ancestors of a Semantic Network relation label by value.
SELECT b.* FROM SRSTRE2 a, SRDEF b
WHERE sty1 = ?
     AND rl = 'isa' AND b.rt = 'RL' AND a.sty2 = b.sty_rl
ORDER BY stn_rtn;

Get ancestors of a Semantic Network relation label by TUI.
SELECT b.* FROM SRSTRE1 a, SRDEF b
WHERE ui1 = ?
     AND b.rt = 'RL' AND a.ui3 = b.ui
     AND a.ui2 IN (SELECT ui FROM srdef WHERE rt= 'RL' and sty_rl = 'isa')
ORDER BY stn_rtn;

Get descendants of a Semantic Network relation label by value.
SELECT b.* FROM SRSTRE2 a, SRDEF b
WHERE sty2 = ?
     AND rl = 'isa' AND b.rt = 'RL' AND a.sty1 = b.sty_rl
ORDER BY stn_rtn;

Get descendants of a Semantic Network relation label by TUI.
SELECT b.* FROM SRSTRE1 a, SRDEF b
WHERE ui3 = ?
     AND b.rt = 'RL' AND a.ui1 = b.ui
     AND a.ui2 IN (SELECT ui FROM srdef WHERE rt= 'RL' and sty_rl = 'isa')
ORDER BY stn_rtn;

Last Reviewed: July 29, 2016