1. Creating a Study
  2. Defining Data Collection Objects
  3. Validation and Derivation Procedures
  4. How Procedures Work Internally

How Procedures Work Internally

When you invoke generation, Oracle Clinical takes the Procedure definitions you create and generates PL/SQL code that retrieves patient records (Question responses and related data) for evaluation or computation one at a time. To create complex Procedures it is important to understand:

  • Which records are retrieved, and the order of retrieval (see Structure of Procedure PL/SQL Programs)

  • How to modify the way records are retrieved to best suit your needs and to enhance performance (see Special Processing)

  • Given a certain order of record retrieval, how to best save any data that may be needed later in the Procedure—for example, to compare values across visits (see User Variables, Aggregate Functions, Aggregate Lag Functions, and Lag Checks.

  • Do not use special characters in your code, including messages. Special characters include: ! & ( ) * + , - . / : < = > [ ] ^ |. For further information see the Oracle Database PL/SQL Language Reference 11g Release 19c. Or, for the latest information, you can generate a list of all keywords and reserved words with the V$RESERVED_WORDS view, described in the Oracle Database Reference 11g Release 19c; see User Documentation in Preface.

For more information, see:

Parent topic: Validation and Derivation Procedures

Cursors

A cursor is a PL/SQL data structure that retrieves one record at a time, as specified in a SELECT statement. The system places each column value retrieved by the cursor into a variable. When you generate a Procedure, the system creates two kinds of cursors:

Parent topic: How Procedures Work Internally

Patient Information Cursors

The Patient Information Cursor becomes the outermost loop in the Procedure. The system generates a standard Select statement for the Patient Information Cursor so that it always fetches values for the same columns. The cursor retrieves patient records sequentially, putting the information into standard variables such as Clinical Study ID and Patient Position ID. See Standard Variables for a complete list.

Parent topic: Cursors

Procedure Question Group Cursors

Procedure Question Group (Procedure QG) Cursors are nested inside the Patient Cursor. The system creates one Procedure QG Cursor for each Procedure Question Group, and generates a Select statement for it based on your Procedure Question Group definition (see Defining Procedure Question Groups). The Procedure QG Cursors retrieve key values, Question responses, and response-related information (entry timestamp, response ID, and exception value text).

The system puts the values of the key fields and response-related information into Question Group variables (see Question Group Variables) and puts Question response values into Question variables (see Question Variables).

Parent topic: Cursors

Structure of Procedure PL/SQL Programs

For each patient, a Procedure fetches records for each nested cursor, running each expression for every set of records.

Each Procedure includes the following logical parts:

  • Declaration of variables and cursors. The system converts your Procedure definitions to PL/SQL code to declare each of the following:

    • Variables. All the variables—user, Question, and system variables—that you have included in the Procedure. See Procedure Variables.

    • Patient Cursor. The Patient Cursor is not based on your definitions, but is standard in every Procedure. See Patient Information Cursors.

    • Procedure Question Group Cursors. Each alias (Procedure Question Group) defined in the Procedure is declared as a cursor, with aggregate Question Groups (if any) first, then the primary reference Question Group, and then all other Question Groups in the order they are displayed in the Question Groups window. See Procedure Question Group Cursors.

  • Patient Cursor. The patient cursor is the outermost loop in the Procedure. It fetches information from the Patient Positions table about the next patient to be processed, and populates standard system variables with the information.

  • First...n Procedure QG Cursors. The system creates a cursor for each Question Group, and orders them as follows:

    1. Aggregate Question Groups (if any)

    2. The primary reference Question Group

    3. All other Question Groups, in the order they are displayed in the Question Groups window

    The first Procedure QG Cursor is the loop immediately inside the patient loop, and each successive cursor forms the next inner loop.

    For each Question you specify in the Procedure Question Group, each Procedure QG Cursor fetches response values collected for the current patient at all CPEs (unless otherwise specified in the Question Group Event Range or correlation by event) in the order you specify in the Sort Order fields for the Question Group (see Sort Order).

    The cursor also fetches values for any other variables you specify in the Procedure (see Procedure Question Group Cursors).

  • Detail. The system picks up your definitions from the Detail and Detail Variable windows to determine whether to run the expression on the current record.

  • Expression. The system runs each expression, referencing values for Questions and other variables as specified in the expression.

    For Derivation Procedures, the system populates derived Question with the derived value (see About Derivation Procedures). For Derivation and Validation Procedures, the system reports any discrepancies found, and associates each discrepancy with the variables and comments specified in the Procedure.

  • The system closes each cursor in turn and starts processing the next patient.

For more information, see:

Parent topic: How Procedures Work Internally

Example: Procedure with More Than One Procedure Question Group

The structure can be represented as follows:

/* cursor for getting VITALS Production */
cursor V_CUR(
ORDER BY RDCM.VISIT_NUMBER ASC,RDCM.SUBEVENT_NUMBER ASC, RES.REPEAT_SN ASC;
V V_CUR%rowtype;
V$BEGIN_SEQNUM  number := -99999;
V$END_SEQNUM  number := 99999;
/* cursor for getting AE Production */
cursor A_CUR(
...
ORDER BY RDCM.VISIT_NUMBER ASC,RDCM.SUBEVENT_NUMBER ASC, RES.REPEAT_SN ASC;
A A_CUR%rowtype;
A$BEGIN_SEQNUM  number := -99999;
A$END_SEQNUM  number := 99999;
   /****** Main Begin ******/
loop <<fetch_next_patient>>
         /****** Post Patient ******/
 loop << fetch_V_cur>>
            /****** Post QG V ******/
 loop << fetch_A_cur>>
               /****** Post QG A ******/
 <<detail_1>>
 <<detail_2>>
  /****** Post Details ******/
          end loop; /* A cursor loop */
        end loop; /* V cursor loop */ 
 end loop; /* patients cursor loop */
   /****** Main End ******/
 /* Populate discrepancy check table */
  /*  Populate response check table with derived values*/
end;

Parent topic: Structure of Procedure PL/SQL Programs

Example: Procedure Containing One Procedure Question Group

A Procedure fetches information as follows when it includes only one Procedure Question Group, and therefore only one Procedure QG Cursor. In this example, the Procedure Question Group is called Physical Exam, and two visits have occurred so far. The sort order is set to the default: by Event and Subevent in ascending order (see Sort Order).

Retrieve Patient 1 information

Retrieve responses and key values for Visit 1 Physical Exam for Patient 1

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Physical Exam for Patient 1

For each detail, execute expression(s)

Retrieve Patient 2 information

Retrieve responses and key values for Visit 1 Physical Exam for Patient 2

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Physical Exam for Patient 2

For each detail, execute expression(s)

Parent topic: Structure of Procedure PL/SQL Programs

Example: Procedure with Multiple DCMs

In this example, the Procedure retrieves information from two DCM Question Groups, Physical Exam and Lab Tests, that are collected at the same visit. The sort order is set to the default: by Event and Subevent in ascending order (see Sort Order). Each Question Group has its own Procedure QG Cursor:

Retrieve Patient 1 information

Retrieve responses and key values for Visit 1 Physical Exam for Patient 1

Retrieve responses and key values for Visit 1 Lab Tests for Patient 1

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 1

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Physical Exam for Patient 1

Retrieve responses and key values for Visit 1 Lab Tests for Patient 1

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 1

For each detail, execute expression(s)

Retrieve Patient 2 information

Retrieve responses and key values for Visit 1 Physical Exam for Patient 2

Retrieve responses and key values for Visit 1 Lab Tests for Patient 2

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 2

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Physical Exam for Patient 2

Retrieve responses and key values for Visit 1 Lab Tests for Patient 2

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 2

For each detail, execute expression(s)

Parent topic: Structure of Procedure PL/SQL Programs

Sort Order

In the Question Groups window you can specify the order in which you want the system to retrieve records within the Procedure Question Group Cursor for each Question Group. Order matters primarily when you use lag variables or perform a similar function yourself by using user variables to hold information from record instance to instance. You may want to compare each visit with the previous visit by Visit and Subevent Order or by DCM Date and Time (which, particularly for subevents, could differ from the Subevent Order). There are two fields for this purpose: Event and Extension.

In the Event field, you can choose to order by either:

  • Visit Number and Subevent Number

  • DCM Date and Time

The default behavior is: Visit Number ascending order, Subevent Number ascending order.

Within the context you choose in the Event field, in the Extension field you can choose to order by either:

  • Repeat Sequence Number (SN) alone (applicable only to repeating Question Groups; the setting has no effect if repeats are not collected)

  • Qualifying Value followed by Repeat Sequence Number (applicable only to DCMs with qualifying values; not available in pre-V3.1-style Procedures)

The default value is Response Repeat Sequence Number ascending.

In both fields, you can choose any combination of ascending or descending order.

Parent topic: Structure of Procedure PL/SQL Programs

Sample Procedure Code, with Custom Code Locations

Following is more detailed sample Procedure code. The places where you can insert custom code are highlighted. See Custom Code for further information. 

PROCEDURE main /*-------------------------------------------------------------*/
(i_clinical_study_id         IN patient_positions.clinical_study_id%TYPE,
 i_clinical_study_version_id IN patient_positions.clinical_study_version_id%TYPE,
 i_data_modified_flag        IN patient_positions.data_modified_flag%TYPE,
 ...
 i_current_location          IN patient_positions.owning_location%TYPE,
 i_timer                     IN BOOLEAN := FALSE ) IS
 v_duplicate_discrepancy     BOOLEAN;

/* << Oracle Clinical puts DECLARATION custom code here >> */ 



BEGIN
 IF i_timer THEN timer.capture; END IF;

BEGIN
  ...
 std.v_code_location := 'Main Begin';

/* << Oracle Clinical puts MAIN-BEGIN custom code here >> */

 OPEN std.patients_cur; 
 LOOP <<fetch_next_patient>>
   FETCH std.patients_cur INTO std.patients_rec; 
   std.v_code_location := 'Post Patient';
   EXIT WHEN std.patients_cur%NOTFOUND;

/* <<Oracle Clinical puts POST-PATIENT custom code here >> */ 

  IF std.v_debug = 'Y' 
      THEN DBMS_OUTPUT.PUT_LINE (' ');
           DBMS_OUTPUT.PUT_LINE ('--> Patient: '||std.patients_rec.patient);
   END IF;
   BEGIN
     OPEN b_cur(std.patients_rec.patient_position_id,b$begin_seqnum,b$end_  seqnum);
     std.v_code_location := 'Fetching B data';
     FETCH b_cur INTO b;
     IF b_cur%FOUND 
         THEN  b$has_data := 'Y';
          ELSE IF b$has_data = 'Y'  
                         THEN EXIT;
                           ELSE b := null_b_record; 
                     END IF;
     END IF;
     std.v_code_location := 'Post QG B';

/* << Oracle Clinical puts POST-QG-B custom code here >> */

				...  std.v_code_location := 'Pre Details';

/* << Oracle Clinical puts PRE-DETAILS custom code here >> */

            <<detail_1>>
            std.v_code_location := 'First prototype detail - 1 = 1 test';
            IF (1 = 1)
					...
            END IF;
            GOTO detail_2; /* NextDetail=detail_2 */
            <<detail_2>>
            std.v_code_location := 'Second prototype detail - 2 = 2 test';
            IF (2 = 2)
					...
            END IF;
            GOTO detail_3; /* NextDetail=detail_3 */
            <<detail_3>>
            std.v_code_location := 'Third prototype detail - 3 = 3 test';
            IF (NOT(c.diasbp IS NULL OR c.sysbp IS NULL OR c$sysbp$l1 IS NULL))
               AND (3 = 3)
               ...
            END IF;
            GOTO no_more_details; /* NextDetail=no_more_details, could omit */
         <<no_more_details>> 
         NULL;
				  ...
  				  std.v_code_location := 'Post Details';

/* << Oracle Clinical puts POST-DETAILS custom code here >> */ 

  ...
     END LOOP; /* b_cur */
 END;
 END LOOP; /* Patients cursor */
 CLOSE std.patients_cur;
 IF i_timer THEN timer.show_elapsed; END IF;

std.v_code_location := 'Main End';

/* << Oracle Clinical puts MAIN-END custom code here >> */ 

EXCEPTION WHEN OTHERS THEN exception_handling(SQLERRM);

END main;

Parent topic: Structure of Procedure PL/SQL Programs

Viewing Generated PL/SQL Code

After you have created the necessary elements of a Procedure, from the Procedures w Edit window (which is visible only if you have the privileges necessary to edit the code) you can generate the code and view the result (both from the Special menu).

Note that V3.1-style Procedures are implemented using a static package, and the Patient Cursor declaration is contained in the package spec, so that you do not see it in the generated code. You do see the Question Group Cursor declarations, which are specific to the Procedure.

In addition, the generated code includes a test and production version of each cursor. The test version has a T appended to its name (A_CURT instead of A_CUR for Alias A) and is not shown in the examples in this documentation.

Parent topic: How Procedures Work Internally