Pathway Tools Installation Guide -- Oracle 10

Pathway Tools server:

Download a configuration of Pathway Tools bundled with the minimal Oracle 10 client.
Choose one of two ways of configuring Oracle access:
- If you are familiar with Oracle configuration, want to manually configure TNS, and don't mind the limitation that database name, username, and password all be "biocyc", then, in your ~/.login or ~/.cshrc file, set the TNS_ADMIN environment variable to a path containing a properly configured tnsnames.ora file for the biocyc database. Be sure the HOST in your tnsnames.ora file is an IP address, not a domain name; otherwise, Pathway Tools may intermittently crash.
- If you need to customize the Oracle connection parameters, then ensure the TNS_ADMIN environment variable is not set when you or other users run Pathway Tools.
  After completing the Pathway Tools installation, a file containing initialization parameters will have been written, called ptools-local/ptools-init.dat . In this file's RDBMS section, 5 parameters need to be set to the correct values, such that database access will work. Hypothetical example settings might look like this:
```
RDBMS-Server-Hostname 123.45.67.89  ### Provide an IP address here, not a domain name.
RDBMS-Server-Port 1521
RDBMS-Database-Name paleo
RDBMS-Username paleocurator
RDBMS-Password ********
```
Note that, for efficiency, data retrieved from Oracle is cached in a subdirectory of the user's home directory:
- Unix: ~/.ocelot-dcache
- Windows: C:\Documents and Settings\WINDOWS-USERNAME\ocelot-dcache
If for any reason the cache ever becomes corrupted, it is safe to shut down Pathway Tools and delete the cache. Then, a new cache will be created next time you run Pathway Tools.
If you downloaded a configuration of Pathway Tools that excludes the Oracle client libraries and you already had Oracle client libraries installed, you may be able to use them instead: Make sure their path is listed in the value of the LD_LIBRARY_PATH environment variable on Unix or the PATH environment variable on Windows.

Oracle Server:

This work needs to be performed by someone with dba-level access, preferably a database administrator:

Install Oracle 10. Pathway Tools might work with Oracle 9, but we have not tested it.
After Oracle is installed, we recommend it have access to at least 550 MB of free disk space.
Create a database. By default, Pathway Tools assumes the database is named biocyc and is under user account biocyc having password biocyc, but you can override the defaults if you wish. See the Pathway Tools section above for details on configuring Pathway Tools to access a non-default database, username, and password.
grant select on sys.v_$session to biocyc or whatever username you chose
In the biocyc database (or whatever you decided to call your database), run the schema-creation script that is in your Pathway Tools installation directory: aic-export/oracle/pgdb-schema.sql

Important Note

The oracle schema for Pathway Tools 12.0 has changed from earlier versions of Pathway Tools. Two tables and two stored procedures have been added. If you are upgrading, please execute the following commands while connected to your Biocyc database.

REM Tables specific to Pathway Tools
create table OVERVIEW_GRAPH (
                KBID            NUMBER(5) primary key,
                LAST_UPDATED    NUMBER,
                USERID          VARCHAR2(40),
                GRAPH           CLOB)
/
create table GENEL_SEQUENCES (
                KBID            number(5),
                GEN_EL          VARCHAR2(80),
                DEFLINE         VARCHAR2(2040),
                SEQUENCE        CLOB, 
                LAST_UPDATED    NUMBER,
                USERID          VARCHAR2(40),
                constraint      unq_gen_el unique (kbid, gen_el))
/
REM Stored procedures specific to Pathway Tools.

CREATE OR REPLACE PROCEDURE store_overview_graph  (
                P_KBID in number,
                P_LAST_UPDATED in number,
                P_USERID in VARCHAR2,
                P_GRAPH in VARCHAR2,
                P_FLAG in number
                )
                IS
        g_clob CLOB;
        BEGIN
                if P_FLAG=0 then
                   insert into OVERVIEW_GRAPH
                   values (P_KBID, P_LAST_UPDATED, P_USERID, empty_clob()) returning GRAPH into g_clob;
                   dbms_lob.write(g_clob, length(P_GRAPH), 1, P_GRAPH);
                else
                   select GRAPH into g_clob from OVERVIEW_GRAPH where KBID=P_KBID for update;
                   dbms_lob.writeappend(g_clob, length(P_GRAPH), P_GRAPH);
                end if;
        END store_overview_graph;
.
/

CREATE OR REPLACE PROCEDURE store_genel_sequences(
                P_KBID in number,
                P_GEN_EL in VARCHAR2,
                P_DEFLINE in varchar2,
                P_LAST_UPDATED in number,
                P_USERID in VARCHAR2,
                P_SEQUENCE in VARCHAR2,
                P_FLAG in number
                ) 
                IS
        g_clob CLOB;
        BEGIN
                if P_FLAG=0 then
                   insert into GENEL_SEQUENCES
                   values (P_KBID, P_GEN_EL, P_DEFLINE, empty_clob(), P_LAST_UPDATED, P_USERID) returning SEQUENCE into g_clob;
                   dbms_lob.write(g_clob, length(P_SEQUENCE), 1, P_SEQUENCE);
                else
                   select SEQUENCE into g_clob from GENEL_SEQUENCES where KBID=P_KBID and GEN_EL=P_GEN_EL for update;
                   dbms_lob.writeappend(g_clob, length(P_SEQUENCE), P_SEQUENCE);
                end if;
        END store_genel_sequences;
.
/
REM End of Pathway Tools Schema Additions