Oracle DMW public views are listed in the API guide which you can find online at Oracle homepage here: Public Views (oracle.com)
The public views are useful for listing study meta data including data models, tables, columns, transformations, validation etc.
If your company has created study templates in Oracle DMW is it essential that you can check and verify using a database query for any changes between versions. If your company is maintaining study templates in a Meta Data Repository (MDR) or MS Excel, you will need a solution to verify that the MDR or Excel contains current and updated information.
In DMW you can blind data in your study using different levels of blinding types (table, column, row, cell), and you can specify how the blinded data should be masked, e.g. using hardcoded ‘999999’ for numeric values, or ‘XXXXXX’ for character values. But more advanced blinding can also be accomplished, e.g. blinding of data using random date within FPFV and LPLV, random values from a codelist applied for field validation, e.g. simple Yes/No, or AE relation to trial product: LIKELY, UNLIKEY, POSSIBLE, PROBABLE.
You can use the Oracle database package DBMS_RANDOM and its function to generate a random value, e.g. random integer from 0..6, and then use the DECODE to set a value depending on the random value generated.
Example: Random value generated from codelist for Action taken to Trial Product in SDTM-AE (Adverse Events):
Random value
Codelist Value assignment
0
DRUG INTERUPTED
1
DRUG WITHDRAWN
2
DOSE REDUCED
3
DOSE INCREASED
4
DOSE NOT CHANGED
5
UNKNOWN
6
N/A
Random codelist value assignment for use with Bliding and masking criteria in Oracle DMW.
Note: For random date you will need to write a function to lookup and return a valid date from within FPFV to LPLV.
If you want to know more or need help to implement more advanced blinding solutions in Oracle DMW, please contact us.
In order to read study data in DMW from visualisation tools like QlikSense or Oracle Analytics Cloud (OAC) you must first initialize read on Business Area (BA) in LSH database before you can issue a SELECT statement to query data in the business area. The problem is, that QlikSense and OAC only allows you to issue a SELECT statement, but not a procedure call, which is required to initialize read on BA via API procedure call in LSH database.
The problem can be resolved by use of a trigger solution, that automatically initialize read on selected BA’s after logon and connection to LSH database is established.
Please contact us if you would like to know more about this solution.
If your company is moving from Oracle Clinical (OC)/TMS to DMW/TMS we have a solution in place for bringing over the TMS Learnings (VTAs) that your medical coders have created over the past many years.
The number of manually created TMS codings is typically in the houndreds of thousands for large pharmas coducting many large scale trials.
Most companies want to bring over the TMS Knowledge database when migrating to a new platform, e.g. DMW/TMS, where TMS is new fresh install with new dictionaries and no previous coding knowledge (VTAs).
Note: TMS Learnings (VTAs) can be created in TMS target system prior to verbatim arriving from DMW.
TMS VTA copy solution can be used for:
Migration of TMS knowledge database from OC/TMS -> DMW/TMS
Migration of TMS knowledge database from one environment to another, e.g. Prod to Train for training of medical coders.
Please Contact us if Your company is interested in hearing more about our solutions for bringing over TMS learnings from one TMS source instance to another TMS target instance, e.g. if your company is moving from OC/TMS to DWM/TMS, or if you want to copy the TMS knowledge database from one TMS instance to another, e.g. copy from TMS production to TMS training environment.
OC: Oracle Clinical | DMW: Data Management Workbench | TMS: Thesaurus Management System | VTA: Verbatim Term Assignment (TMS learning created during manual coding (classification) of verbatim term to a dictionary term).
Oracle Database 18c is the first version of the product to follow a yearly release pattern. From here onwards the Oracle Database will be released every year along with quarterly updates.
Oracle Database 18c is the next iteration of Oracle Database 12c Release 2.
Oracle AutonomousDatabase Cloud offers total automation based on machine learning and eliminates human labor, human error, and manual tuning.
Oracle’s 18c main aim is “self-driving” database. New features which prefer No human labor means half cost, no human error means 100x reliable. Here NoHuman Labor represents automation in Installation, Patching, Upgrades, and Tuning of Oracle Database which uses the Robot Technique with the help of MachineLearning.
Oracle Database technology automates management to deliver unprecedented availability, performance, and security—at a significantly lower cost.
Today (Monday Sept. 4th, 2017) Oracle released the latest version of Oracle Clinical 5.2, Oracle Clinical Remote Data Capture 5.2 and Thesaurus Management System 5.2.1.
OC/RDC 5.2
TMS 5.2.1 (TMS 5.2 with patch for supporting WHODrug B3)
OC 5.2 and TMS 5.2 is primarily a technology stack upgrade from version 5.1 including some RDC bug-fixes, and hence no new functionality as such. However, TMS 5.2 comes with a patch (5.2.1) to support the new WHODrug B3 format, which was released on March 1st, 2017, and will replace the WHODrug B2 Enhanced Format. The dictionary vendor UMC will support WHODrug B2 until end of 2018.
SQL for listing Scheduled BVS jobs in Oracle Clinical.
BVS = Batch Validation Session.
select
batch_job_id,
module_name,
m.description,
user_name,
study,
execution_status,
to_char(submission_ts,'DAY') submission_day,
to_char(submission_ts,'YYYY-MM-DD HH24:MI:SS') submission_ts,
to_char(entered_ts,'YYYY-MM-DD HH24:MI:SS') entered_ts,
to_char(started_ts,'YYYY-MM-DD HH24:MI:SS') started_cet,
to_char(completion_ts,'YYYY-MM-DD HH24:MI:SS') completed_cet,
round((completion_ts-started_ts)*24,2) dura_hours,
round((completion_ts-started_ts)*1440,0) dura_min,
round((completion_ts-started_ts)*86400,0) dura_sec,
output_file_name,
log_file_name,
failure_text,
bj.schedule_server,
bj.schedule_string
from batch_jobs bj, modules m
where bj.module_name = m.name
and module_name = 'RXCBVBVS'
and bj.execution_status = 'SCHEDULED'
order by 8 desc;
Also, see table BATCH_DM_RUNS for info on BVS runs including success flag, modified patient count and data currency flag (CURRENT =Y for production data).
select * from batch_dm_runs
order by creation_ts desc;
How to list logon/logoff sessions in the Oracle (Clinical) database including failed logins?
Example for Oracle 11g database (OC 4.6.x)
Logon to SQL*Plus or TOAD as DBA user in the Oracle database with access to DBA system views and tables.
SQL query DBA Audit Session
/* Logon/Logoffs using referencing lookup function for returning error message for return (error) code */
select
username,
terminal,
action_name,
to_char(timestamp,'YYYYMMDD HH24:MI:SS') timestamp,
to_char(logoff_time,'YYYYMMDD HH24:MI:SS') logoff_time,
returncode,
get_ora_error_message(-returncode) error_msg
from dba_audit_session
where 1=1 -- timestamp > sysdate-7
AND username like '%' -- = 'OPS$<init>' -- OC users are prefixed with OPS$
and returncode <> 0 -- <> 0 to list failed logins
--and logoff_time is not null
order by 5 desc;
Function used to return error message from error code (return code)
create or replace function get_ora_error_message(p_ora_number in number) return varchar2 is
v_msg varchar2(255);
begin
v_msg := sqlerrm(p_ora_number);
return v_msg;
end;
/
Return codes and messages (commonly seen)
ORA-01017: invalid username/password; logon denied
ORA-01045: user lacks CREATE SESSION privilege; logon denied
ORA-03136: inbound connection timed out
ORA-28000: the account is locked
ORA-28001: the password has expired
ORA-28003: password verification for the specified password failed
ORA-28007: the password cannot be reused
ORA-28043: invalid bind credentials for DB-OID connection
