Skip Headers
Oracle® Clinical Interfacing from Oracle Clinical
Release 4.6

Part Number A83793-06
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

1 Customer-Defined Identifier Handling

The OCL_CLIENT_PACK database package contains procedures that let you define object-naming validation for your Oracle Clinical installation. If you are the Oracle Clinical site administrator, you generally will write the function body to create site-specific validation and derivation code before you run the package. You can implement local naming conventions for:


The OCL_CLIENT_PACK package consists of two files in the RXC_INSTALL directory, ocl_client_ps.sql and ocl_client_pb.sql. Refer to these files while reading this chapter. The files serve the following purposes:

Use SQL*Plus from the RXC database account to execute ocl_client_ps.sql:

This file is a skeleton for creating custom validation and derivation because each function provides a stub; as delivered all validations succeeds and no derivation occurs. Each function includes detailed header comments that describe its purpose, parameters, and usage.

The functions in the OCL_CLIENT_PACK package are described in the following sections. The functions to customize for validation and derivation are ValidateName, ValidateDocument, and DeriveDocumentNumber. The functions to customize for page tracking are DefaultBookPageNumber, TrimPageNumber, ChangePageStatus, and AddPhysPageNumber. In each case, the function duplicates a function that already exists in the Oracle Clinical code. If you write new code for the function, your code supersedes the existing application code; otherwise, it simply uses the default system code. The SQL files ocl_client_ps.sql and ocl_client_pb.sql in the RXC_INSTALL are well commented, use them for additional information and to implement your custom functions.


This function is called by the validation triggers on the naming of each of the Oracle Clinical objects. These objects include discrete value groups, questions, question groups, DCMs, DCIs, Procedures, Where clauses, and queries. As input, the function takes a code for the type of object and the name being validated. If the validation succeeds, the function returns NULL and the user can continue.

If the validation fails, the function returns a varchar2 character string, that contains an error message. The error message is displayed in the status line of the form. This function is called by the following forms that validate the name of the object being created. You can add your own validation to enforce your company naming standards as an example:

  • Maintain DVG

  • Maintain Questions

  • Maintain Question Groups

  • Maintain DCMs

  • Maintain DCIs

  • Maintain Procedures (both Derivation and Validation)

  • Maintain Copy Groups

  • Maintain Where Clauses

  • Maintain Labs

  • Mass Changes


This function is called by Validation Procedures on the document number in the CRF Log-In form and the Batch Data Load program. The input to the function is the document number being validated. If the validation succeeds, the function returns NULL; otherwise, the function returns the error message the user defined in the package.


With this function you can derive CRF document numbers from the key fields entered in the Log-In form and the Batch Data Load program. If you modify this function, you must review the argument list, reapply the modifications, then reinstall the package. Inputs are:

  • Key fields from the CRF and their ID numbers: Study, Clinical_Study_ID, Patient, Patient_Position_ID, Investigator, Investigator_ID, Site, Site_ID, DCI, DCI_ID, Event, Clin_Plan_Eve_ID, and Subevent

  • Indicator of test or production mode date: RXC_ENV_TYPE

The function returns NULL if the derivation is not possible; otherwise it returns the derived document number. Document numbers generated by this procedure are not validated against the ValidateDocument function; they are assumed to be valid.


This function assigns the correct page number to the first page of a DCI in the DCI book. The function returns zero if the operation is successful, or a non-zero error message number. The message number (message_topic_id) is used by the Maintain DCI Books to display the appropriate error message from the message_topics table. It uses the TrimPageNumber function and has these input parameters:

  • first page number of the previous DCI in the DCI book

  • numbering scheme of the previous DCI

  • number of expected pages

The function's one output parameter corresponds to the default page number that represents the first page number of a DCI in the DCI book.


This function trims the page number off its suffix according to the numbering scheme. It is called each time Oracle Clinical needs to add one unit to a page number and each time Oracle Clinical needs to assign a default page number.

There are two input parameters:

  • page number to be trimmed

  • numbering scheme according to which the page number needs to be trimmed

There are two output parameters:

  • the result of the trimming operations

  • Y when the other output parameter contains a numeric value; N otherwise

Examples of parameter values:

input 1 input2 output 1 output 2


This function sets the client-specific status of a new page, depending on the current page status, the RDCI status, and the blank flag, according to user-defined rules. It returns NULL, if successful; otherwise, it returns an error message.


This function adds a number to a page number, according to the numbering scheme— when DCIs are added to a DCI book, and also when pages assigned at data entry correspond to one of the following unplanned events: the DCI is not in the DCI book; the DCI is present in the DCI book but not for the specific visit; or the subevent number is zero.

There are three input parameters:

  • page number to which the number must be added

  • number to be added to the page number

  • numbering scheme

There is one output parameter: the page number after the operation.

input 1 input2 input 3 output
2.1 3 NUMERIC 2.4