7 Using the Generated MIP

7.1.1 What's in a MIP?

A MIP contains the code needed to interact with the message queuing system:

• The database link connects the MIP to a procedural gateway that can communicate with a non-Oracle message queuing system. The MIP includes all the code needed to communicate with the gateway.

• A data profile defines the data types for messages exchanged between the Oracle and non-Oracle applications. The Visual Workbench generates data-mapping code according to these definitions, and the MIP calls this code to convert messages.

  See Also:

  "Data Conversion Package" for more information about data profiles and data mapping

• The message queue profile describes message properties and queuing operations. The MIP translates the properties and operations into gateway calls that control the message queuing system when sending or retrieving messages.

The MIP contains four interface procedures that can be called from any Oracle application: QOPEN, ENQUEUE, DEQUEUE, and QCLOSE.

Each data profile attached to the MIP has its own set of ENQUEUE and DEQUEUE procedures in the MIP. The ENQUEUE procedure takes a message on input, formatted according to the PL/SQL type created for the data profile. The DEQUEUE procedure delivers a message as output, formatted according to the PL/SQL type created for the data profile.

7.1.2 MIP Specification Example

This example shows a MIP specification generated for the interface profile named HIRE, a message queue profile named HR that is configured for WebSphere MQ, and a data profile named EMPLOYEE:

/*
 * Copyright (c) 1999. Oracle Corporation. All rights reserved.
 *
 *   Message Interface Package specification HIRE for WebSphere MQ.
 *
 *       Using Database Link:
 *         GTWMQ.WORLD
 *
 *       Using Message Queue Profile 'HR':
 *         Queue              = QUEUE1
 *         Security ID        = 
 *         Enqueue Visibility = On Commit
 *         Dequeue Visibility = On Commit
 *         Dequeue Mode       = Remove
 *         Wait               = No Wait
 *         Correlation        = 
 *         Priority           = Default
 *         Expiration         = Never
 *         Message Type       = 
 *         Response Queue     = QUEUE1
 *         Delivery Mode      = Not Persistent
 *         Acknowledgement    = None
 *
 *       Using Data Profile(s):
 *         EMPLOYEE
 *
 *       MIP procedures:
 *         Procedure QOPEN()   - Establish access to a queue
 *         Procedure QCLOSE()  - Relinquish access to a previously opened queue
 *         Procedure ENQUEUE() - Put a EMPLOYEE message on the queue (basic).
 *         Procedure ENQUEUE() - Put a EMPLOYEE message on the queue (advanced).
 *         Procedure DEQUEUE() - Get a EMPLOYEE message from the queue (basic)
 *         Procedure DEQUEUE() - Get a EMPLOYEE message from the queue (advanced).
 *
 *   Generated 16-MAR-1999 10:51 by pgmadmin.
 */
 
CREATE OR REPLACE PACKAGE HIRE AS: 
 
  /*
   *  NAME
   *    Procedure QOPEN() - Establish an access to a queue object
   *  DESCRIPTION
   *    This function takes on input the name of the 
   *    queue to open and an open option parameter. 
   *    The open mode field in the open options structure 
   *    is always a constant, ENQUEUE, when the queue is open
   *    for enqueueing. The open mode field  can take values
   *    REMOVE or BROWSE when the queue is open for dequeueing.
   *  NOTES
   *    If the procedure fails an exception is raised.
   *  RETURNS
   *    queue_handle -  A structure containing the handle to the
   *    queue and the mode in which the queue is open.
   */
 
  PROCEDURE QOPEN(queue_name IN VARCHAR2, 
                 open_options IN PGM_BQM.OPEN_OPTIONS_Typ, 
                 queue_handle OUT PGM_BQM.QUEUE_HANDLE_Typ);
 
  /*
   *  NAME
   *    Procedure QCLOSE() - Relinquish access to a previously open queue
   *  DESCRIPTION
   *    This procedure takes on input a queue handle structure
   *    and relinquishes the access to the queue. After QCLOSE the 
   *    handle becomes invalid and cannot be used for the 
   *    enqueue or dequeue calls any more. 
   *  NOTES
   *    If the procedure fails an exception is raised.
   *  RETURNS
   *    void
   */
 
  PROCEDURE QCLOSE(queue_handle IN OUT PGM_BQM.QUEUE_HANDLE_Typ);
 
  /*
   *  NAME
   *    Procedure ENQUEUE() - Put a EMPLOYEE message on the queue (basic).
   *  DESCRIPTION
   *    This procedure takes on input a payload parameter of PL/SQL 
   *    type EMPLOYEE.EMPLOYEE_Typ, converts it to the 
   *    native format understood by the remote application 
   *    and sends it to the queue defined for this interface.
   *    The procedure uses the options and properties defined by the
   *    message queue profile HR.
   *  NOTES
   *    If the procedure fails an exception is raised.
   *  RETURNS
   *    void
   */
 
  PROCEDURE ENQUEUE(payload IN EMPLOYEE.EMPLOYEE_Typ);
 
  /*
   *  NAME
   *    Procedure ENQUEUE() - Put a EMPLOYEE message on the queue (advanced).
   *  DESCRIPTION
   *    This procedure takes on input a payload parameter of PL/SQL 
   *    type EMPLOYEE.EMPLOYEE_Typ, converts it to the 
   *    native format understood by the remote application 
   *    and sends it to the queue defined for this interface.
   *
   *    Using the input parameters queue_handle, enqueue_options
   *    and message_properties the caller controls how
   *    the enqueue operation should operate.
   *
   *    If the input parameters or their respective fields are null, the procedure
   *    uses the options and properties as defined by the
   *    message queue profile HR.
   *
   *    The message identification as generated by the message queuing
   *    system for the message is returned in the msgid output parameter. This identifier
   *    can be used to identify the message at dequeue time.
   *  NOTES
   *    If the procedure fails an exception is raised.
   *  RETURNS
   *    void
   */
 
  PROCEDURE ENQUEUE(queue_handle IN PGM_BQM.QUEUE_HANDLE_Typ, 
                    enqueue_options IN PGM_BQM.ENQUEUE_OPTIONS_Typ, 
                    message_properties IN PGM_BQM.MESSAGE_PROPERTIES_Typ, 
                    payload IN EMPLOYEE.EMPLOYEE_Typ, 
                    msgid OUT RAW);
 
  /*
   *  NAME
   *    Procedure DEQUEUE() - Get a message EMPLOYEE from the queue (basic).
   *  DESCRIPTION
   *    This procedure reads the next message from the queue 
   *    defined for this interface and converts it to the PL/SQL 
   *    type EMPLOYEE.EMPLOYEE_Typ output parameter named payload.
   *
   *    The procedure uses the options and properties defined by the
   *    message queue profile HR.
   *  NOTES
   *    If the function fails, an exception is raised.
   *  RETURNS
   *    void
   */
 
  PROCEDURE DEQUEUE(payload OUT EMPLOYEE.EMPLOYEE_Typ);
 
  /*
   *  NAME
   *    Procedure DEQUEUE() - Get a message EMPLOYEE from the queue (advanced).
   *  DESCRIPTION
   *    This routine reads the next message from the queue 
   *    defined for this interfaceand converts it to the PL/SQL 
   *    type EMPLOYEE.EMPLOYEE_Typ output parameter named payload.
   *
   *    Using the input parameters queue_handle and dequeue_options
   *    the caller controls how the dequeue operation should operate.
   *
   *    If any of the input parameters or their respective fields
   *    are NULL, the function will use the settings defined by the 
   *    message queue profile HR.
   *
   *    The properties of the dequeued message are returned in the message_properties
   *    output parameter.
   *
   *    The message identification as generated by the message queuing
   *    system for the message is returned in the msgid output parameter.
   *  NOTES
   *    If the function fails, an exception is raised.
   *  RETURNS
   *    void
   */
 
  PROCEDURE DEQUEUE(queue_handle IN PGM_BQM.QUEUE_HANDLE_Typ, 
                    dequeue_options IN PGM_BQM.DEQUEUE_OPTIONS_Typ,
                    message_properties OUT PGM_BQM.MESSAGE_PROPERTIES_Typ,
                    payload OUT EMPLOYEE.EMPLOYEE_Typ,
                    msgid OUT RAW);
 
END HIRE;

7.3.1 QOPEN Syntax

PROCEDURE QOPEN(queue_name IN VARCHAR2,
                open_options IN PGM_BQL.OPEN_OPTIONS_Typ,
                queue_handle OUT PGM_BQL.QUEUE_HANDLE_Typ);

The following table describes the parameters that must be specified:

QOPEN returns the handle to the queue to be opened. If you call QOPEN multiple times in sequence, then a new queue handle is returned each time and the previous handle becomes invalid.

7.4.1 QCLOSE Syntax

PROCEDURE QCLOSE(queue_handle IN OUT queue_handle_typ)

where, queue_handle specifies the handle of the queue being closed

After QCLOSE is called, the queue handle becomes invalid and cannot be used for subsequent ENQUEUE or DEQUEUE procedures.

If QCLOSE fails, then it raises a PL/SQL exception. The application calling the procedure is responsible for handling the exception.

7.5.1 ENQUEUE Naming and Name Resolution

Because the ENQUEUE procedures are part of a MIP, applications must specify this fully qualified name when calling the procedure:

schema_name.mip_name.ENQUEUE

The following table describes the parameters that need to be specified:

PL/SQL runs the correct ENQUEUE procedure based on the parameters provided. For example, if only the payload output parameter is specified, the basic ENQUEUE procedure is run.

When more than one data profile is added to a MIP and multiple versions of basic and advanced ENQUEUE procedures are generated for the MIP, the type of payload parameter used determines which ENQUEUE procedure in the MIP is selected.

7.5.2 Basic ENQUEUE Procedure

The basic ENQUEUE procedure converts the message content of payload according to the specifications of the data profile, and sends the message to a queue according the specifications of the message queue profile.

If the basic ENQUEUE procedure fails, then it raises a PL/SQL exception. Exceptions can be raised for various reasons, such as an error occurring during conversion or a problem reported by the message queuing system. The application calling the procedure is responsible for handling the exception.

PROCEDURE ENQUEUE(payload IN type_definition)

DECLARE
  message EMPLOYEE.EMPLOYEE_Typ;
BEGIN
  message.EMPLOYEENO := '123456';
  message.FIRSTNAME  := 'SCOTT';
  message.LASTNAME   := 'TIGER';
  message.BIRTHDATE  := '01-01-71';
  message.SALARY     := 50000;
  message.BONUS      := 4000;
  message.COMMISSION := 0;
  HIRE.ENQUEUE(message);
  COMMIT;
EXCEPTION WHEN OTHERS THEN
  ROLLBACK;
  RAISE;
END;

7.5.3 Advanced ENQUEUE Procedure

The advanced ENQUEUE procedure converts the message content of payload according to the specifications of the data profile, and sends the message to a queue.

The queue_name, enqueue_operations and message_properties parameters enable the calling application to control the ENQUEUE operation. If a parameter or its fields are NULL, then the ENQUEUE procedure uses the corresponding setting of the message queue profile associated with the MIP.

The msgid parameter returns the message identifier of the enqueued message. It can be used in the advanced dequeue procedure to select the message to be retrieved.

If the advanced ENQUEUE procedure fails, then it raises a PL/SQL exception. Exceptions can be raised for various reasons, such as an error occurring during message conversion or a problem reported by the message queuing system. Providing an invalid value for one of the input parameters also causes a PL/SQL exception. The application calling the procedure is responsible for handling the exception.

PROCEDURE ENQUEUE(queue_name IN VARCHAR2,
                    enqueue_options IN PGM_BQM.ENQUEUE_OPTIONS_Typ,
                    message_properties IN PGM_BQM.MESSAGE_PROPERTIES_Typ,
                    payload IN type_definition,
                    msgid OUT RAW)

DECLARE
  message      EMPLOYEE.EMPLOYEE_Typ;
  enqueueOpts  PGM_BQM.ENQUEUE_OPTIONS_Typ; 
  msgProps     PGM_BQM.MESSAGE_PROPERTIES_Typ;
  msgid        RAW(24);
  queueHandle  PGM_BQM.QUEUE_HANDLE_Typ;
  queueName    VARCHAR2(48)
  CURSOR C1 IS SELECT EMP_NO EMP_FNAME FROM EMP;
BEGIN
  msgProps.priority := 4;
  msgProps.expiration := PGM_BQM.NEVER;
  openOpts.open_mode := PGM_BQM.ENQUEUE;
  queueName := 'emp';
  HIRE.QOPEN(queueName openOpts queueHandle);
  OPEN C1;
  LOOP
    FETCH C1 into message.employeeno, message.firstname;
    EXIT when C1 %NOTFOUND;
    HIRE.ENQUEUE(queueHandle,
                 queueName,
                 enqueueOpts,
                 msgProps,
                 message,
                 msgid);
  ENDLOOP;
  CLOSE C1;
  HIRE.QCLOSE(queueHandle);
  COMMIT;
EXCEPTION WHEN OTHERS THEN
  HIRE.QCLOSE(queueHandle);
  ROLLBACK;
  RAISE;
END;

7.6.1 DEQUEUE Naming and Name Resolution

Because the DEQUEUE procedures are part of a MIP, applications must specify this fully qualified name when calling the procedure:

schema_name.mip_name.DEQUEUE

The following table describes the parameters that need to be specified:

PL/SQL runs the correct DEQUEUE procedure based on the parameters provided upon invocation. For example, if only a payload output parameter is specified, the basic DEQUEUE procedure is run.

When more than one data profile is added to a MIP and multiple versions of basic and advanced DEQUEUE procedures are generated for the MIP, the type of payload used determines which DEQUEUE procedure in the MIP is selected.

7.6.2 Basic DEQUEUE Procedure

The basic DEQUEUE procedure retrieves the message from a queue according to the specifications of the message queue profile, converts the message according to the specifications of the data profile, and returns the result in the payload output parameter.

If the basic DEQUEUE procedure fails, then it raises a PL/SQL exception. Exceptions can be raised for various reasons, such as an error occurring during conversion or a problem reported by the message queuing system. The application calling the procedure is responsible for handling the exception.

PROCEDURE DEQUEUE(payload OUT type_definition)

DECLARE
  firstName    VARCHAR2(30);
  message      EMPLOYEE.EMPLOYEE_Typ;
BEGIN
  HIRE.DEQUEUE(message);
  firstName := message.FIRSTNAME;
  COMMIT;
EXCEPTION WHEN OTHERS THEN
  ROLLBACK;
  RAISE;
END;

7.6.3 Advanced DEQUEUE Procedure

The advanced DEQUEUE procedure retrieves a message from the message queue, converts the payload value from native to PL/SQL format according to the specifications of the data profile, and returns the result in the payload output parameter.

The queue_name and dequeue_options parameters enable the calling application to control the DEQUEUE operation. If a parameter or its fields are NULL, then the DEQUEUE procedure uses the corresponding setting of the message queue profile associated with the MIP.

The message_properties parameter returns the properties of the message as set by the enqueing application or the message queuing system.

The msgid parameter returns the message identifier of the dequeued message.

If the advanced DEQUEUE procedure fails, thenit raises a PL/SQL exception. Exceptions can be raised for various reasons, such as an error occurring during conversion, or a problem reported by the message queuing system, or providing an invalid value for one of the input parameter. The application calling the procedure is responsible for handling the exception.

PROCEDURE DEQUEUE(queue_name IN VARCHAR2, 
                    dequeue_options IN PGM_BQM.DEQUEUE_OPTIONS_Typ,
                    message_properties OUT PGM_BQM.MESSAGE_PROPERTIES_Typ,
                    payload OUT type_definition,
                    msgid OUT RAW)

DECLARE
  message      EMPLOYEE.EMPLOYEE_Typ;
  dequeueOpts  PGM_BQM.DEQUEUE_OPTIONS_Typ; 
  msgProps     PGM_BQM.MESSAGE_PROPERTIES_Typ;
  msgid        RAW(24);
  openOptions  PGM_BQM.OPEN_OPTIONS_Typ;
  queueName    VARCHAR2 (48)
  queueHandle  BINARY_INTEGER
BEGIN
  queueName := 'emp'
  openOpts.open_mode := PGM_BQM.REMOVE;
  HIRE.QOPEN(queueName, openOpts, queueHandle);
  LOOP
    HIRE.DEQUEUE(queueHandle,
                 queueName,
                 dequeueOpts,
                 msgProps,
                 message(count),
                 msgid);
    INSERT into emp (firstname)
    VALUES (message.firstname)
  END LOOP;
EXCEPTION 
  WHEN PGM_BQM.NO_MORE_MESSAGES THEN
    HIRE.QCLOSE(queueHandle);
    COMMIT;
  WHEN OTHERS THEN
    HIRE.QCLOSE(queueHandle);
    ROLLBACK;
    RAISE;
END;

7.7.1 OPEN_OPTIONS_Typ

This type defines the open options by an application when it calls QOPEN. This structure type is used for the open_options parameter in the open call.

TYPE OPEN_OPTIONS_Typ IS RECORD (open_mode binary_integer default null)

7.7.2 ENQUEUE_OPTIONS_Typ

This type defines the ENQUEUE options that can be specified by the calling application. The structure type is used for the enqueue_options parameter of the advanced ENQUEUE procedure. If the fields of this parameter contain values, then they override the settings of the message queue profile.

TYPE ENQUEUE_OPTIONS_Typ IS RECORD (visibility binary_integer
                                    default null)

7.7.3 DEQUEUE_OPTIONS_Typ

This type defines the DEQUEUE options that can be specified by the calling application. The structure type is used for the dequeue_options parameter of the advanced DEQUEUE procedure. If the fields of this parameter contain values, then they override the settings of the message queue profile.

TYPE DEQUEUE_OPTIONS_Typ IS
      RECORD (dequeue_mode binary_integer default null,
              visibility   binary_integer default null,
              wait         binary_integer default null,
              msgid        raw(24) default null,
              correlation  varchar2(128) default null)

7.8.1 Syntax

TYPE MESSAGE_PROPERTIES_Typ is record (
    priority              binary_integer default null,
    expiration            binary_integer default null,
    correlation           varchar2(128) default null,
    message_type          varchar2(256) default null,
    response_queue        varchar2(128) default null,
    delivery_mode         binary_integer default null,
    acknowledgement       binary_integer default null,
    security_id           varchar2(128) default null)

The following table describes the parameters that need to be specified:

7.9.1 Exceptions

Applications are responsible for handling these PL/SQL exceptions:

• When an application assigns an invalid value to a type definition field, the MIP procedures raise this PL/SQL exception:

  INVALID_BGM_VALUE EXCEPTION
  PRAGMA EXCEPTION_INIT(INVALID_BGM_VALUE, -20002)
  
  

• When an application tries to dequeue a message when there are no more messages on the queue, the MIP procedures raise this exception:

  NO_MORE_MESSAGES_EXCEPTION;
  PRAGMA EXCEPTION_INIT(NO_MORE_MESSAGES, -20003);
  

  See Also:

  "Using the MIP for WebSphere MQ" for more information

7.9.2 Naming

You must prefix the type definitions, constants and exceptions with PGM_BQM. No schema name need be provided, because the package has a public synonym. Example:

DECLARE
  message      EMPLOYEE.EMPLOYEE_Typ;
  dequeueOpts  PGM_BQM.DEQUEUE_OPTIONS_Typ; 
  msgProps     PGM_BQM.MESSAGE_PROPERTIES_Typ;
  msgid        RAW(24);
  firstName    VARCHAR2(30);
BEGIN
  dequeueOpts.DEQUEUE_MODE := PGM_BQM.BROWSE;
  HIRE.DEQUEUE('QUEUE2', dequeueOpts, msgProps, message, msgid);
  firstName := message.FIRSTNAME;  
  COMMIT;
EXCEPTION WHEN OTHERS THEN
  ROLLBACK;
  RAISE;
END;

7.10.1 PGM_SUP Package

To access a WebSphere MQ system, you must install the PGM_SUP package before deploying the MIP. PGM_SUP contains definitions that are required for using the Oracle Procedural Gateway for WebSphere MQ.

When the Visual Workbench generates a MIP, it uses the definitions of the PGM_SUP package in the package body of the MIP. Oracle applications using the MIP procedures do not access these definitions directly, but need only be granted access to them.

7.10.2 Exceptions

When applications assign a value that is supported by the PGM_BQM package but not supported by WebSphere MQ, the MIP procedures raise this exception for WebSphere MQ:

INVALID_MQSERIES_VALUE EXCEPTION                
PRAGMA EXCEPTION_INIT(INVALID_MQSERIES_VALUE, -20001)

Applications are responsible for handling these exceptions.

7.12.1 PIC X

Visual Workbench converts the COBOL X data type to a PL/SQL CHAR data type of the same character length. NLS character-set conversion is also performed.

COBOL lacks a data type specifically designated for variable-length data. Such data is represented in COBOL as a subgroup containing a PIC 9 length field followed by a PIC X character field.

For example:

10 NAME.
   15 LENGTH PIC S9(4).
   15 LETTERS PIC X(30).

It cannot be guaranteed that all instances of an S9(4) field followed by an X field are always variable-length data. Instead of converting the COBOL group NAME to a PL/SQL VARCHAR in the example, the Visual Workbench constructs a nested PL/SQL record:

TYPE NAME_typ is RECORD {
        LENGTH NUMBER(4,0),
        LETTERS CHAR(30) 
);
 
TYPE … is RECORD (
         ...
         NAME NAME_type,
         ...
);

It is the client application's responsibility to extract NAME.LENGTH characters from NAME.LETTERS and assign the result to a PL/SQL VARCHAR data type, if VARCHAR is desired. This requires specific knowledge of the remote host data.

Character-set conversion is performed for single-byte encoding:

• For remote host character data, select a character set from the character set drop-down list in the COBOL Data Definition dialog box for all single-byte character fields in the data profile.

  See Also:

  "Creating a Data Profile" for more information

• For local Oracle character data, set the language character set on the Oracle server for all character fields in the data profile.

7.12.2 PIC G

Visual Workbench converts the COBOL G data type to a PL/SQL VARCHAR2 data type of the same length, allowing two bytes for every character position.

The alphanumeric and double-byte character set editing field positions are listed in the following table:

The presence of the editing symbols means that the remote host field contains the data content and length indicated. The editing positions are included in the length of the data field, but all field positions are converted as a single string and no special scanning or translation is done for edited byte positions.

Edited positions in data retrieved from the remote host are converted along with the entire field and passed to the client application in the corresponding PL/SQL output variable defined as a VARCHAR2 data type. For example:

• PIC XXXBBXX: Alphanumeric field 7 bytes long and is converted in a single conversion call. No testing or translation is done on the contents of the byte positions indicated by B. Although VS COBOL II language rules indicate that these positions contain "blank" in the character set specified for the remote host, the data that is present is the user's responsibility.

• PIC GGBGGG: Double-byte character set field 12 bytes long and is converted in a single conversion call. No testing or translation is done on the contents of the byte positions indicated by B. Although VS COBOL II language rules indicate that these positions contain "blank" in the character set specified for the remote host, the data that is present is the user's responsibility

7.12.3 PIC 9

The Visual Workbench converts the COBOL 9 data type to a PL/SQL NUMBER data type of the same precision and scale. NLS character-set translation is also performed on signs, currency symbols, and spaces. These data types are supported:

• COMPUTATIONAL (binary)

• COMPUTATIONAL-3 (packed decimal)

• COMPUTATIONAL-4 (binary)

• DISPLAY (zoned decimal)

  For DISPLAY data types, these sign specifications are supported:

  • SEPARATE[CHARACTER]

  • LEADING

  • TRAILING

COMPUTATIONAL-1 and COMPUTATIONAL-2 (floating point) data types are not supported.

7.12.4 FILLER

The Visual Workbench recognizes COBOL FILLER fields by the spelling of the element name FILLER. The Visual Workbench does not generate any data conversion for such elements, but does require that their space be properly allocated to preserve offsets within the records exchanged with the remote host translation.

If a RENAMES or REDEFINES definition covers a FILLER element, then the Visual Workbench generates data conversion statements for the same area when it is referenced as a component of the RENAMES or REDEFINES variable. Such data conversion reflects only the format of the RENAMES or REDEFINES definition and not the bounds of the FILLER definition.

7.13.1 JUSTIFIED and JUSTIFIED RIGHT

JUSTIFIED and JUSTIFIED RIGHT cause remote host transaction data to be converted as a PL/SQL CHAR data type according to character data type for both IN and OUT parameters:

• Input data passed from the application is stripped of its right-most blanks and are left-padded as required, and are sent to the remote host

• Output data is aligned as it is received from the remote host and left- padded with blanks as required, and are sent to the application.

7.13.2 JUSTIFIED LEFT

JUSTIFIED LEFT causes warnings in the Visual Workbench when the data conversion package is being generated. No alignment is performed. The remote host transaction data is converted as a PL/SQL CHAR data type according to character data type for both IN and OUT parameters.

7.13.3 OCCURS n TIMES

OCCURS n TIMES causes conversion of n instances of a set of PL/SQL variables to or from a repeating group area within the remote host record. The size of the area equals the group length multiplied by n repetitions. The data conversion packages generated by the Visual Workbench use PL/SQL RECORDs of TABLEs to implement an array-like subscript on fields in a repeating group. Because PL/SQL supports a single dimension TABLE, the Visual Workbench supports only a single level of an OCCURS group. It does not support nested OCCURS groups. Conversion and formatting are dictated by the COBOL data type of each subfield defined in the repeating group.

7.13.4 OCCURS m TO n TIMES DEPENDING ON field-2

This causes conversion of at least m and not more than n instances of a set of PL/SQL variables to or from a repeating group area within the remote host record. The size of the area equals the group length multiplied by the repetition count contained in the named field. The data conversion packages generated by the Visual Workbench use PL/SQL RECORDs of TABLEs to implement an array-like subscript on fields in a repeating group. Because PL/SQL supports a single dimension TABLE, the Visual Workbench supports only a single level of an OCCURS group. It does not support nested OCCURS groups. Conversion and formatting are dictated by the COBOL data type of each sub-field defined in the repeating group. The data conversion packages generated by the Visual Workbench use a FOR … LOOP algorithm with a range of 1 to the specified upper limit of TIMES.

7.13.5 RENAMES item-2 THRU item-3

A single PL/SQL variable declaration corresponds to a RENAMES definition. If all the subfields covered by a RENAMES definition are PIC X, the PL/SQL variable is data type VARCHAR2. Otherwise, any non-PIC X subfield causes the PL/SQL variable data type to be RAW. Lengths of renamed fields do not contribute to the overall parameter data length because the original fields dictate the lengths.

7.13.6 RENAMES item-2 WHEN item-3=value

WHEN item-3 = value is an Oracle extension to the COBOL copybook data definition as stored in the Visual Workbench repository. This extension exists only in the Visual Workbench context and is not valid IBM VS COBOL II syntax.

The gateway administrator or application developer can use this extension to specify the criteria by which to apply the redefinition. For example, a record-type field is often present in a record, and different record formats apply depending on which record type is being processed. The specification of which type values apply to which redefinition is typically contained in the application programming logic, not in the data definition. The WHEN criterion is included in data definitions to specify which conversion to perform on redefined formats in the data conversion package.

The Visual Workbench generates PL/SQL nested record declarations which correspond in name and data type to the subordinate elements covered by the REDEFINED definition.

LEVEL 01 REDEFINE is ignored, permitting remote host copybooks to include definitions which REDEFINE other transaction working storage buffers without having to define such buffers in the data conversion package or alter the copybook used as input for the definition.

7.13.7 SYNCHRONIZED and SYNCHRONIZED RIGHT

SYNCHRONIZED and SYNCHRONIZED RIGHT cause the numeric field to be aligned on boundaries as dictated by the remote host environment, compiler language, and data type.

Numeric conversion is performed on the aligned data fields according to numeric data type for IN and OUT parameters.

7.13.8 SYNCHRONIZED LEFT

SYNCHRONIZED LEFT causes warnings to be displayed on the Visual Workbench during the generation of data conversion package, and no realignment is performed.

Numeric conversion is performed on the aligned data fields according to numeric data type for IN and OUT parameters.

7.14.1 Delimiters

Special COBOL characters in record, group, and element names are translated when data profiles are created using the Visual Workbench:

• Hyphen (-) is translated to underscore (_)

• Period (.) is deleted

7.14.2 Qualified Compound Names

PL/SQL variable names are fully qualified and composed from:

• PL/SQL record name as the leftmost qualifier corresponding to level 01 or 77 COBOL record name

• PL/SQL nested record names corresponding to COBOL group names

• PL/SQL nested fields corresponding to COBOL elements of data type

• CHAR or NUMBER corresponding to nonrepeating COBOL elements

• TABLE corresponding to COBOL elements which fall within an OCCURS or OCCURS DEPENDING ON group (COBOL repeating fields correspond to PL/SQL nested RECORDs of TABLEs)

When referencing PL/SQL variables from calling applications, the data conversion package name (the data profile name created in the Visual Workbench) must be prefixed as the leftmost qualifier. These examples show the fully qualified reference to the PL/SQL variable:

BIRTHDAY is: EMPLOYEE.EMPLOYEE_Typ.BIRTHDAY
BONUS is: EMPLOYEE.EMPLOYEE_Typ.BONUS

7.14.3 Truncated and Non-Unique Names

The Visual Workbench truncates field names and corresponding PL/SQL variable names when the name exceeds:

• 26 bytes for fields within an aggregate record or group. This is because each field or PL/SQL variable name must have the suffix _Typ for group names and _Tbl for element names with a repeating group.

• 30 bytes because of a PL/SQL limitation for any name

The rightmost four characters are truncated, imposing the restriction that names be unique to 26 characters.

7.14.4 Duplicate Names

COBOL allows repetitive definition of the same group or element names within a record, and the context of the higher-level groups uniquely qualifies names. However, because data conversion packages generated by the Visual Workbench declare PL/SQL record variables that reference nested PL/SQL records for subordinate groups and fields, such nested record types can have duplicate names.

Given this COBOL definition, ZIP is uniquely qualified in COBOL, but the corresponding PL/SQL declaration would have a duplicate nested record type for ZIP. (The PL/SQL declaration is shown in "Generated PL/SQL").

01  EMPREC.
      05 HIREDATE          PIC X(8).
      05 BIRTHDATE         PIC X(8).
      05 SKILL             PIC X(12)OCCURS 4.
      05 EMPNO             PIC 9(4).
      05 EMPNAME.
         10 FIRST-NAME     PIC X(10).
         10 LAST-NAME      PIC X(15).
      05 HOME-ADDRESS.
         10 STREET         PIC X(20).
         10 CITY           PIC X(15).
         10 STATE          PIC XX.
         10 ZIP.
            15 FIRST-FIVE  PIC X(5).
            15 LAST-FOUR   PIC X(4).
      05 DEPT              PIC X(45).
      05 OFFICE-ADDRESS.
         10 STREET         PIC X(20).
         10 CITY           PIC X(15).
         10 STATE          PIC XX.
         10 ZIP.
            15 FIRST-FIVE  PIC X(5).
            15 LAST-FOUR   PIC X(4).
      05 JOBTITLE          PIC X(20).

Generated PL/SQL

The Visual Workbench avoids declaring duplicate nested record types, and generates this PL/SQL based on the COBOL definition in "COBOL definition":

SKILL_Key BINARY_INTEGER;
 
TYPE SKILL_Tbl is TABLE of CHAR(12)
   INDEX by BINARY_INTEGER;
 
TYPE EMPNAME_Typ is RECORD ( 
   FIRST_NAME       CHAR(10),
   LAST_NAME        CHAR(15));
 
TYPE ZIP_Typ is RECORD (
   FIRST_FIVE       CHAR(5),
   LAST_FOUR        CHAR(4));
 
TYPE HOME_ADDRESS_Typ is RECORD (
   STREET                CHAR(20),
   CITY                  CHAR(15),
   STATE                 CHAR(2),
   ZIP                   ZIP_Typ);
 
TYPE OFFICE_ADDRESS_Typ is RECORD (
   STREET                CHAR(20),
   CITY                  CHAR(15),
   STATE                 CHAR(2),
   ZIP                   ZIP_Typ);
 
TYPE EMPREC_Typ is RECORD (
   HIREDATE             CHAR(8),
   BIRTHDATE            CHAR(8),
   SKILL                SKILL_Tbl,
   EMPNO                NUMBER(4,0),
   EMPNAME              EMPNAME_Typ,
   HOME_ADDRESS         HOME_ADDRESS_Typ,
   DEPT                 CHAR(45),
   OFFICE_ADDRESS       OFFICE_ADDRESS_Typ,
   JOBTITLE             CHAR(20));

However, multiple nested groups might have the same name but have different sub-fields, as in the COBOL definition in Example 7-2.

01  EMPREC.
      05 HIREDATE           PIC X(8).
      05 BIRTHDATE          PIC X(8).
      05 SKILL              PIC X(12) OCCURS 4.
      05 EMPNO              PIC 9(4).
      05 EMPNAME.
         10 FIRST-NAME      PIC X(10).
         10 LAST-NAME       PIC X(15).
      05 HOME-ADDRESS.
         10 STREET          PIC X(20).
         10 CITY            PIC X(15).
         10 STATE           PIC XX.
         10 ZIP.
            15 LEFTMOST-FIVE   PIC X(5).
            15 RIGHTMOST-FOUR  PIC X(4).
       05 DEPT              PIC X(45).
       05 OFFICE-ADDRESS.
          10 STREET         PIC X(20).
          10 CITY           PIC X(15).
          10 STATE          PIC XX.
          10 ZIP.
             15 FIRST-FIVE  PIC X(5).
             15 LAST-FOUR   PIC X(4).
       05 JOBTITLE          PIC X(20).

In a COBOL definition where multiple nested groups have the same name but different subfields, the Visual Workbench alters the name of the PL/SQL nested record type for each declaration in which the subfields differ in name, data type, or options, as shown in Example 7-3.

SKILL_Key BINARY_INTEGER;
 
TYPE SKILL_Tbl is TABLE of CHAR(12)
   INDEX by BINARY_INTEGER;
 
TYPE EMPNAME_Typ is RECORD (
   FIRST_NAME       CHAR(10),
   LAST_NAME        CHAR(15));
 
TYPE ZIP_Typ is RECORD (
   LEFTMOST_FIVE    CHAR(5),
   RIGHTMOST_FOUR   CHAR(4));
 
TYPE HOME_ADDRESS_Typ is RECORD (
   STREET           CHAR(20),
   CITY             CHAR(15),
   STATE            CHAR(2),
   ZIP              ZIP_Typ);
 
TYPE ZIP_Typ02 is RECORD (
   FIRST_FIVE       CHAR(5),
   LAST_FOUR        CHAR(4));
 
TYPE OFFICE_ADDRESS_Typ is RECORD (
   STREET            CHAR(20),
   CITY              CHAR(15),
   STATE             CHAR(2),
   ZIP               ZIP_Typ02);
 
TYPE EMPREC_Typ is RECORD (
   HIREDATE          CHAR(8),
   BIRTHDATE         CHAR(8),
   SKILL             SKILL_Tbl,
   EMPNO             NUMBER(4,0),
   EMPNAME           EMPNAME_Typ,
   HOME_ADDRESS      HOME_ADDRESS_Typ,
   DEPT              CHAR(45),
   OFFICE_ADDRESS    OFFICE_ADDRESS_Typ,
   JOBTITLE          CHAR(20));

In Example 7-3, note that the 02 appended to the second declaration (ZIP_Typ02) and its reference in OFFICE_ADDRESS, and the fully qualified reference to the PL/SQL variable that corresponds to the following:

• HOME_ADDRESS.ZIP is:

  packagename.EMPREC_Typ.HOME_ADDRESS.ZIP.LEFTMOST_FOUR
  packagename.EMPREC_Typ.HOME_ADDRESS.ZIP.RIGHTMOST_FIVE
  
  

• OFFICE_ADDRESS.ZIP is:

  packagename.EMPREC_Typ.OFFICE_ADDRESS.ZIP.FIRST_FIVE
  packagename.EMPREC_Typ.OFFICE_ADDRESS.ZIP.LAST_FOUR
  
  

The nested record type name ZIP_Typ02 is not used in the reference but is implicit within PL/SQL's association of the nested records.