| Oracle® Clinical Stable Interface Technical Reference Manual Release 5.0 E36999-01 |
|
|
PDF · Mobi · ePub |
| Oracle® Clinical Stable Interface Technical Reference Manual Release 5.0 E36999-01 |
|
|
PDF · Mobi · ePub |
The term stable interface in this document refers to a subset of the Oracle Clinical database that appears to remain unchanged, or stable, through at least two subsequent releases of Oracle Clinical. This subset consists of a set of synonyms that point to the tables and views. (See "Synonyms".)
If you are building applications that read data from Oracle Clinical, the stable interface provides:
easier access to data held within Oracle Clinical
smooth transition from one version of Oracle Clinical to the next
upgrade timing flexibility
The term stable views in this document refers to the views obtained via the stable interface; it does not refer to the stable views obtained in the Data Extract subsystem of Oracle Clinical.
The stable interface supports read-only access to data. Updates to the Oracle Clinical database should always be performed in the Oracle Clinical client.
Note:
Tables and views not documented as part of the stable interface are not part of it and are therefore subject to change without notice. External use of the tables not included in the stable interface is not supported by Oracle Corporation unless a separate agreement is reached with Oracle Consulting Services.The stable interface allows external reports and applications to run against Oracle Clinical without change when Oracle Clinical is upgraded. By following some general rules for accessing data within Oracle Clinical, it is not necessary to simultaneously upgrade all applications that read from Oracle Clinical. Instead, you may choose when to migrate to the new layout. Each new release of Oracle Clinical includes an upgrade and instructions to upgrade the stable interface.
You do not have to upgrade to the newest version of the stable interface. Each version of the stable interface provides a set of synonyms that point to a table or view. In most cases, the synonym that reflects an earlier release, points to the same underlying table or view as the synonym in the current release. For example, in Oracle Clinical 5.0, the synonyms ACTUAL_EVENTS_V40, ACTUAL_EVENTS_V45, ACTUAL_EVENTS_V46 and ACTUAL_EVENTS_V50 all point to the same table in the database.
You can choose to continue to use the existing stable interface synonyms or upgrade to the new synonyms. We recommend that you use the new synonyms for new interfacing applications because they will be supported through one more release of Oracle Clinical than the older synonyms, but the older synonyms will work as well.
All synonyms correspond exactly between the different versions except for the RDCI_HISTORY Synonym which is explained below.
RDCI_HISTORY_V46 and RDCI_HISTORY_V50 synonyms both refer to the RDCI_HISTORY table. The RDCI_HISTORY_V45 synonym changed in release 4.6.
If you applied patch 4.5.3.11 or a superseding patch before upgrading to 5.0, the RDCI_HISTORY_V45 synonym also refers to the RDCI_HISTORY table as well. If you did not apply these patches, the synonym refers to a view which excludes records where TRANS_TYPE = AUDIT or TRANS_TYPE is like SYS%, RETAIN%, REVERSE%.
While the stable interface hides changes to the database layout or business meaning in the release, Oracle cannot guarantee that exactly the same business information is provided. For example, if the length of a field is increased in a new version of Oracle Clinical, the stable interface truncates the contents of the field and provides existing users of the stable interface with a field of the original length. This truncation means that customers who want to view all the information in a new field must upgrade to the latest version of the stable interface.
The stable interface includes a set of synonyms that point to the underlying Oracle Clinical tables and views. All programs that access Oracle Clinical data should use these synonyms.
Synonym names typically consist of the table name concatenated with a version number. For example, the synonym for the CLINICAL_STUDIES table in Oracle Clinical is CLINICAL_STUDIES_V40 in the stable interface for version 4.0. These synonyms are supported in this release of the stable interface.
Some synonyms follow another naming convention, or have retained the naming convention of the previous release of the stable interface. Refer to Table 1-1 for the exceptions to the standard synonym naming convention.
Table 1-1 Exceptions to standard synonym naming convention
| Table Name | Synonym |
|---|---|
|
CLINICAL_PLANNED_EVENTS |
LIVE_CLINICAL_PLANNED_EVE_V40 |
|
DISCREPANCY_ENTRY_REVIEW_HIST |
DISCREPANCY_ENTRY_REVIEW_V40 |
|
DISCREPANCY_ENTRY_REVIEW_HISTT |
DISCREPANCY_ENTRY_REVIEWT_V40 |
|
PATIENT_POSITIONS |
LIVE_PATIENT_POSITIONS_V40 |
|
PLANNED_STUDY_INTERVALS |
LIVE_PLANNED_STUDY_INTERV_V40 |
|
QUESTION_CATEGORY_RELATIONS |
QUESTION_CATEGORY_RELATIO_V40 |
|
RESPONSES |
RESPONSE_ATTRIBUTES_V40 |
|
RESPONSEST |
RESPONSE_ATTRIBUTEST_V40 |
|
STUDY_SITE_PATIENT_POSITIONS |
STUDY_SITE_PATIENT_POSIT_V40 |
|
STUDY_SITE_PATIENT_POSITIONST |
STUDY_SITE_PATIENT_POSITT_V40 |
|
TREATMENT_PATTERNS |
LIVE_TREATMENT_PATTERNS_V40 |
|
VALIDATION_REPORTED_VALUES |
VALIDATION_REPORTED_VALU_V40 |
|
VALIDATION_REPORTED_VALUEST |
VALIDATION_REPORTED_VALUT_V40 |
Subsequent releases of Oracle Clinical will include new synonyms that follow both naming conventions described above. Version numbers are updated to reflect the version number for the most recent release.
This section describes general rules and techniques for accessing data within Oracle Clinical.
Use the *SELECT syntax to query a table only when you are certain of the sequence of columns within the table. If you do not know the order of the columns, it is recommended that you avoid the *SELECT syntax and instead include a specific column name in the query, for example:
SELECT Region_Code
Note:
The sequence of columns within a table is not guaranteed. As Oracle Clinical is upgraded, the column sequence may change. In addition, in the table and column definitions, some columns are for information only. Although Oracle has defined the expected meaning for these columns, the text the user will add to these columns cannot be guaranteed. For example, a column named Region could be populated with tax code information.A number of objects in Oracle Clinical are associated with two levels: the Global Library level and the Study level. Internally, both levels are held in the same tables. Objects belonging to an individual study include a clinical study ID; objects in the Global Library have a clinical study ID of 0. The objects that follow this convention are:
DCIs
DCMs
Procedures
View definitions
To select objects in the Global Library, select where clinical study ID = 0. To select objects for a specific study, use the appropriate clinical study ID.
Oracle Clinical saves a copy of a self-journaling table each time you modify the table. This generated history enables you to track changes in data over time. RESPONSES, RECEIVED_DCMS, and RECEIVED_DCIS are all self-journaling tables.
This section describes joining tables and updating them, including deleting records from tables. For a list of the journal tables added to this release, see Appendix A, "Journal and History Tables".
When self-journaling tables are joined to each other, they must be individually restricted to the record instance that existed at the time you want to view the data. You do not join on the entry timestamp, even though it is part of the concatenated primary key of the tables.
Example 1-1 Joining Self-Journaling Tables
The following chart illustrates how to join self-journaling tables. Tables A and B are self-journaling tables that have a foreign key relationship from B back up to A. The following state could exist after one year.
| Table A | Table B | |||
|---|---|---|---|---|
|
ID |
Entry Timestamp |
ID |
Entry Timestamp |
Foreign Key |
|
123 |
01-JAN-1998 |
789 |
10-JAN-1998 |
123 |
|
123 |
01-APR-1998 |
789 |
15-MAR-1998 |
123 |
|
123 |
10-JUN-1998 |
789 |
20-SEP-1998 |
123 |
|
123 |
20-DEC-1998 |
|
|
|
All three records in Table B have a foreign key that points to all four records in Table A. The following SELECT statement brackets the date 01-MAR-1998, selecting where:
Timestamps for Table A cover 01-MAR-2009.
Timestamps for Table B cover 01-MAR-2009.
The foreign key links B to A.
SELECT A.*, B.*
FROM A, B WHERE A.ID = B.FOREIGN_KEY
AND A.ENTRY_TIMESTAMP <=
TO_DATE('01-MAR-1998 12:00:00', 'DD-MON-YYYY HH24:MI:SS')
AND A.END_TS >
TO_DATE('01-MAR-1998 12:00:00', 'DD-MON-YYYY HH24:MI:SS') AND
B.ENTRY_TIMESTAMP <=
TO_DATE('01-MAR-1998 12:00:00', 'DD-MON-YYYY HH24:MI:SS')
AND B.END_TS >
TO_DATE('01-MAR-1998 12:00:00', 'DD-MON-YYYY HH24:MI:SS)
This action retrieves one record from each table as it appeared on the requested date.
Oracle Clinical timestamps are precise to the second. A record may be updated more than once during a day. To ensure you select a specific record, choose an explicit time. For example, if you want to view a record from June 20, 1997, decide if you want to view the record at the start, middle, or end of the day. Alternatively, you can select without a time and manage the possibility of multiple results.
Many Oracle Clinical processes use the timestamp of the start of a successfully completed batch validation process as a discrete point of time to view data. These time points are recorded in the table BATCH_DM_RUNS.
To access the current record:
SELECT ... where end timestamp = to_date(3000000,J) and ...
To access data as it existed at a given point in time:
SELECT ... where entry timestamp <= the required date and end_timestamp > required date and ...
For tables containing the clinical data in Oracle Clinical, simple user updates are converted into an update and an insertion. Internally, Oracle Clinical performs the following steps:
Sets an end timestamp on the current record.
Inserts a new version of the record with the changes.
The new record has the same ID as the original record. In addition, it has an entry timestamp equivalent to the end timestamp on the previous record and an end timestamp in the year 3,000,000.
There are two ways to delete records from a self-journaling table.
Soft Delete A record is deleted from a self-journaling table by means of Key Changes or another standard data entry process. The end timestamp of the current record is updated to the current timestamp; no new version of the record is created.
Hard Delete A record is deleted from a self-journaling table by means of the Study Data Deletion utility; the record is deleted from the database with no audit trail.
Most objects within Oracle Clinical are regarded as being owned by a particular database instance. Other instances can have read-only copies, but they will only be as up to date as the last time replication was executed.
If you require the most recent state of a particular type of data, you should access it at the instance that owns it. This rule applies to the following types of data:
Global Library
study designs and definitions at individual locations
Patient Positions and their received data
Sites
Investigators
Only one location in a replication installation owns the Global Library. The Global Library owning location is listed in a reference codelist called OCL_INSTALLATION. For the short value GLIB_LOCATION, the long value (for example, LONDON) is the library owning location.
In the following occurrences, the owning location is held in the OWNING LOCATION column:
Study designs and definitions Ownership is controlled by the owning location of the clinical studies record in the CLINICAL_STUDIES table.
Patient Positions and their received data Ownership is controlled by the owning location on the patient positions record in the PATIENT_POSITIONS table. This means that although a study has only one owning location (for example, LONDON), patient records in the study may have separate owning locations (for example, NEW YORK).
Sites and Investigators Each has a unique owning location.
Note:
The owning location for studies and patients can be changed from within Oracle Clinical up to the point when all clinical data has been collected.To find out where you are within Oracle Clinical, select the short value LOCATION_CODE on the reference codelist called OCL_STATE. The long value indicates where you are.
Many tables have one or more of the following standard attributes.
Every record within Oracle Clinical, except for the self-journaling tables, comes with the following four standard audit attributes:
CREATION_TS: the date the record was created.
CREATED_BY: person who created the record.
MODIFICATION_TS: date the record was last modified
MODIFIED_BY: person who last modified the record.
Many objects in Oracle Clinical include a status. The STATUS_CODE is always P (Provisional), A (Active), or R (Retired). For a given requirement you must choose an appropriate status. If the status is:
P or Provisional, the object may be changed, deleted, or used in a limited context. In addition, provisional objects may be used for test data entry.
A or Active, very limited changes are allowed; this object may not be deleted. The object may be used for Production data entry.
R or Retired, no changes are allowed; this object may not be deleted nor may it be used. Retired objects are retained to support existing data.
Records that have a STATUS_CODE of R also have a code to indicate why the status was set to retired.
The Global Library is partitioned into domains. Object names within domains are unique; across domains they need not be unique. Partitioning allows for objects with the same name to have different definitions. Because, for example, an Adverse Event DCM could have several definitions, it is necessary to specify an Adverse Event DCM as being in a domain called, for example, STANDARD; it is not sufficient to specify the Adverse Event DCM without its domain.
Users may be limited to accessing certain domains, depending upon their privileges.
In tables containing both Global Library and study information, the name of the domain for study information is the same as the study, because names of objects within a study must be unique.
Reference codelists appear to end users as simple lookup tables. However, within Oracle Clinical they are all stored within the following two tables.
REFERENCE_CODELISTS Holds a list of codelists and some of their basic attributes.
REFERENCE_CODELIST_VALUES Holds the actual values for all lists. The value is held in a column called Short Value. The short value is guaranteed to be unique only within the context of a particular codelist.
Most reference codelists are used to validate code values and to supply descriptions of the codes for reports and screens. However, in a few reference codelists the long value has a processing significance.
Some attributes for Questions and Question Groups are repeated at different levels. Oracle Clinical includes business rules that control the field values that may be overwritten as well as the valid override values. For example, the maximum length of a Question could be increased at the study level. The basic principles are that:
The highest order contains the most general values.
Values become more restricted at the study level.
Lower-level values override the values from the level above.
Attributes at the higher order are used as defaults when the object is used at the next level. If no lower-level value exists, the value is derived from the higher-level value.
In the following list, Questions is the highest order, so Question Group Questions default from and are constrained by Questions, while DCM Question Group Questions default from and are constrained by Question Group Questions:
Questions
Question Group Questions
DCM Question Group Questions
In this list, Question Groups is the highest order, so DCM Question Groups default from and are constrained by Question Groups:
Question Groups
DCM Question Groups
|
 Copyright © 2001, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|