Skip Headers
Oracle Procedural Gateway® Visual Workbench for WebSphere MQ Installation and User's Guide
10g Release 2 (10.2) for Microsoft Windows (32-Bit)

Part Number B19082-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

7 Using the Generated MIP

This chapter describes the contents of a MIP specification, the MIP procedures, examples of how to use a MIP in an Oracle application, the contents, and use of the data conversion package.

This chapter contains the following sections:

7.1 About the Message Interface Package (MIP)

A MIP is a PL/SQL package that provides a high-level interface for Oracle applications to communicate with non-Oracle message queuing systems. Every time you create or update an interface profile, the Visual Workbench generates a MIP from the interface profile information you supply.


Note:

The MIP described in this chapter is not compatible with the MIP released with the Visual Workbench release 4.0.1.1.1 and earlier. You must modify applications that use the beta version of the MIP.

The Visual Workbench repository is not compatible with the MIP released with the Visual Workbench release 4.0.1.1.1 and earlier. You must re-create interface profiles that were created with the beta release.


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.


Note:

"MIP Procedures" for more information

7.1.1.1 QOPEN and QCLOSE

QOPEN opens a specified queue. You must call QOPEN before calling the advanced ENQUEUE and DEQUEUE procedures.


See Also:

"Type Definitions" for more information

QCLOSE closes an open queue. You must call QCLOSE after processing messages with the advanced ENQUEUE and DEQUEUE procedures.


See Also:

"QCLOSE Procedure" for more information

7.1.1.2 ENQUEUE and DEQUEUE

The ENQUEUE procedure ends a message to the queue specified in the message queue profile.


See Also:

"ENQUEUE Procedure" for more information

The DEQUEUE procedure retrieves a message from the previously opened queue.


See Also:

"DEQUEUE Procedure" for more information

The MIP contains a basic and an advanced version of the ENQUEUE and DEQUEUE procedures for each data profile associated with the MIP:

  • The basic version takes all options and properties from the message queue profile. It requires one argument, a message-content buffer identified as payload, in the syntax described in this chapter.

  • The advanced version has additional parameters that let the calling application control how to send or retrieve a message. The calling application can override the options and properties specified in the message queue profile and can set other options.

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.2 MIP Procedures

The MIP contains QOPEN, QCLOSE, and both a basic and an advanced version of the ENQUEUE and DEQUEUE procedures for each data profile associated with the MIP:


See Also:

"ENQUEUE Procedure" and "DEQUEUE Procedure" for more information

7.3 QOPEN Procedure

The QOPEN procedure establishes access to a queue. It returns a queue handle that is used as an input parameter in the advanced versions of the ENQUEUE and DEQUEUE procedures.

It is not necessary to perform a QOPEN before calling the basic ENQUEUE or DEQUEUE procedures. The basic ENQUEUE and DEQUEUE procedures open the queue before sending and retrieving messages.

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:

Parameter Description
queue_name Name of the queue to be opened. If the value is NULL, then the queue name from the message queue profile is used.
open_options Defines the open options that the calling application can specify when calling QOPEN. See Also: "PGM_BQM Package" for more information. NULL is an invalid parameter.
queue_handle Returns the handle specified by queue_name

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 QCLOSE Procedure

QCLOSE relinquishes access to a queue object. You must call QCLOSE after all messages have been processed using the advanced ENQUEUE and DEQUEUE procedures.

It is not necessary to perform a QCLOSE after processing messages using the basic ENQUEUE and DEQUEUE procedures. The basic version ENQUEUE and DEQUEUE close the queue after sending and retrieving messages.

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 ENQUEUE Procedure

The MIP ENQUEUE procedure:

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:

Parameter Description
schema_name Name of the Oracle user that compiled the MIP
mip_name Name you specified when you created the MIP

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.

7.5.2.1 Syntax

PROCEDURE ENQUEUE(payload IN type_definition)

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

Parameter Description
payload Must be specified according to type_definition. NULL is an invalid value.
type_definition See Also: "Type Definitions" and "Data Conversion Package".

7.5.2.2 Example

In this example, message contains an employee record defined by a data profile named EMPLOYEE. It is populated with data, then passed to the basic ENQUEUE procedure of a MIP named HIRE.

When it is called, the MIP converts the employee record according to the rules of the data profile and delivers the employee record to the specified queue using the ENQUEUE options and message properties of the message queue profile.

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.


See Also:

"PGM_BQM Package" for more information

7.5.3.1 Syntax

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)

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

Parameter Description
queue_name Name of the queue to send the message.
enqueue_options Defines the ENQUEUE options that can be specified by the calling application.

See Also: "ENQUEUE_OPTIONS_Typ".

message_properties Defines the message options for a message. See Also: "MESSAGE_PROPERTIES_Typ".
payload Must be specified according to the type_definition. NULL is an invalid value.
type_definition See Also: "Type Definitions" and "Data Conversion Package".
msgid Identifier generated by the message queuing system for the message.

7.5.3.2 Example

In this example, the queue to which messages are to be sent is opened with QOPEN. The openOpts.open_mode parameter is set to ENQUEUE, to open the queue for input. This parameter must be specified. A NULL value is invalid. A handle to the queue is returned and is used as an input parameter in each ENQUEUE call.

In this example, the message contains an employee record defined by a data profile named EMPLOYEE. It is populated with data, and then passed to the advanced ENQUEUE procedure of a MIP named HIRE. The priority and expiration of the message are set by the calling application and passed in using the values specified by enqueueOpts and msgProps.

After the messages are enqueued, the QCLOSE procedure is called.

When called, the MIP converts the employee record according to the specifications of the data profile and sends the employee record to the specified queue using the passed-in queue name, priority, and expiration, taking the other options and properties from the message queue profile.

The message property priority (msgProps.priority) directs the ENQUEUE procedure to give the message a priority of 4 when enqueuing the message, instead of using the value set by the message queue profile. The expiration property (msgProps.expiration) specifies that the message is to remain on the queue unless it is dequeued again.

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 DEQUEUE Procedure

The DEQUEUE procedure:

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:

Parameter Description
schema_name Name of the Oracle user that compiled the MIP.
mip_name Name you specified when you created the MIP.

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.

7.6.2.1 Syntax

PROCEDURE DEQUEUE(payload OUT type_definition)

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

Parameter Description
payload Returns the message data, formatted according to the type_definition. NULL is an invalid value.
type_definition See Also: "Type Definitions" and "Data Conversion Package".

7.6.2.2 Example

In this example, message contains an employee record defined by a data profile named EMPLOYEE. It is passed as an output argument to the basic DEQUEUE procedure of a MIP named HIRE.

When it is called, the MIP retrieves a message from the specified queue using the DEQUEUE options of the message queue profile. After the message is retrieved, it is converted from native to PL/SQL format according to the specifications of the data profile and returned in the output parameter message. In this example, the application uses the dequeued message to obtain the employee's first name.

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.


See Also:

"PGM_BQM Package"

7.6.3.1 Syntax

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)

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

Parameter Description
queue_name Name of the queue from which to retrieve the message
dequeue_options Defines the DEQUEUE options that can be specified by the calling application.

See Also: "DEQUEUE_OPTIONS_Typ".

message_properties Returns the message options for a message. See Also: "MESSAGE_PROPERTIES_Typ"
payload Returns the message data, formatted according to type_definition.
type_definition See Also: "Data Conversion Package" for information.
msgid Identifier generated by the message queuing system for the message.

7.6.3.2 Example

In this example, the queue from which messages are to be retrieved is opened using QOPEN. In the example, openOpts.open_mode is set to REMOVE, so that each time an ENQUEUE procedure is called, messages are removed permanently from the queue. You can set openOpts.open_mode to BROWSE to enable reading a message from the queue and not removing it. A handle to the queue is returned and is used as an input parameter in each DEQUEUE call.

In this example, the message contains an employee record defined by a data profile named EMPLOYEE. It is passed as an output argument to the advanced DEQUEUE procedure of a MIP named HIRE. The queue name and the DEQUEUE mode are explicitly specified by the calling application and passed in using the queue and dequeueOpts input parameters. In this example, dequeueOpts directs the DEQUEUE procedure to get the next message from the queue but not remove it from the queue.

The DEQUEUE procedure is called in a loop to retrieve a message from the specified queue using the passed-in queue name and DEQUEUE mode, taking the other options and properties from the message queue profile. When the exception PGM_BQM.NO_MORE_MESSAGE is raised, QCLOSE is called and the transaction is committed.

The message is converted from native to PL/SQL format according to the specifications of the data profile and returned in the output parameter message. In this example, the application uses the dequeued message to obtain the employee's first name.

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 Type Definitions

This section describes the data structures used in the MIP procedures and the supporting packages that define these structures.

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.

7.7.1.1 Syntax

TYPE OPEN_OPTIONS_Typ IS RECORD (open_mode binary_integer default null)

The open_mode parameter specifies in which mode the queue is open:

  • ENQUEUE puts messages into the queue (can be used in combination with REMOVE and BROWSE).

  • REMOVE reads and removes the message from the queue (can be used in combination with ENQUEUE).

  • BROWSE reads a message from the queue but does not remove it (can be used in combination with ENQUEUE).

  • NULL is an invalid value.

You must set the open_mode when you call the advanced ENQUEUE and DEQUEUE procedures. The advanced DEQUEUE procedure does not use the value specified in the Dequeue Mode field in the Visual Workbench. You must specify REMOVE or BROWSE dequeue mode in the advanced DEQUEUE procedure.

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.

7.7.2.1 Syntax

TYPE ENQUEUE_OPTIONS_Typ IS RECORD (visibility binary_integer
                                    default null)

where visibility specifies the transaction behavior of the ENQUEUE requests. Choose one of the these modes:

  • ON_COMMIT

    Specifies that the ENQUEUE is part of the current transaction. The operation is completed when the transaction is committed. ON_COMMIT is the default.

  • IMMEDIATE

    Specifies that the ENQUEUE is not part of the current transaction. The operation constitutes a transaction of its own.

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.

7.7.3.1 Syntax

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)

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

Parameter Description
dequeue_mode Specifies how messages are read from the queue:
  • REMOVE reads the message and removes it from the queue. REMOVE is the default.

  • BROWSE reads a message from the queue but does not remove it.

visibility Specifies the transaction behavior of the DEQUEUE request:
  • ON_COMMIT specifies that the ENQUEUE is part of the current transaction. The visibility parameter is ignored when DEQUEUE_MODE is set to BROWSE. The operation is completed when the transaction commits. ON_COMMIT is the default.

  • IMMEDIATE specifies that the DEQUEUE is not part of the transaction. The operation constitutes a transaction of its own.

wait Specifies the wait time if no message is available on the queue:
  • FOREVER specifies an unlimited wait time. This is the default.

  • NO_WAIT specifies that there is no wait time.

  • nn specifies the time to wait in seconds.

msgid Specifies the message identifier of the message to be dequeued.
correlation Specifies the correlation identifier of the message to be dequeued.

7.8 MESSAGE_PROPERTIES_Typ

This type defines or obtains the message options for a message by the calling application. The structure type is used for the message_properties input parameter of the advanced ENQUEUE procedure and is an output parameter for the DEQUEUE procedure.

If the fields of this parameter contain values on input for the advanced ENQUEUE procedure, then they override the settings of the message queue profile. On output for the advanced DEQUEUE procedure, the fields reflect the message properties given to the message either by the enqueuing application or the message queuing system.

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:

Parameter Description
priority Specifies the message priority:
  • DEFAULT_PRIORITY specifies the priority as the default priority of the message queuing system.

  • nn specifies a numeric priority range from 0 up. The smaller the number, the higher the priority.

expiration Specifies when the message expires, determining in seconds how long the message is available for dequeuing:
  • NEVER specifies that the message does not expire and is available on the queue for an unlimited time.

  • nn specifies the number of seconds the message remains available on the queue. Depending on how the queues and event handling of the message queuing system are configured, the message queuing system might place expired messages on dedicated event queues.

correlation Specifies an application-supplied identification for the message.
message_type Specifies an application-supplied, free-format description of the message. The description is forwarded by the message queuing system as is from the enqueuing application to the dequeuing application. Applications are responsible for the usage of the specified response queue.
response_queue Name of the response queue for the message. The response queue name is forwarded by the message queuing system as is from the enqueuing application to the dequeuing application. Applications are responsible for the usage of the specified response queue. A response queue is required if acknowledgment is specified as either POSITIVE or NEGATIVE.
delivery_mode Specifies whether enqueued messages survives a system failure:
  • PERSISTENT specifies that messages survive a system failure.

  • NOT_PERSISTENT specifies that messages do not survive a system failure.

acknowledgment Specifies whether the enqueing application receives an acknowledgment upon receipt of the message by the dequeuing application:
  • NONE specifies that no acknowledgment message is provided.

  • POSITIVE specifies that an acknowledgment message is provided both when a message is retrieved and when it is not retrieved.

  • NEGATIVE specifies that an acknowledgment message is provided only if the message is not retrieved.

The acknowledgment message is delivered to the queue specified by response_queue.

security_id Security identity associated with the application enqueuing the message. The property is forwarded by the message queuing system, as is, from the enqueuing application to the dequeuing application. Applications are responsible for the usage of the specified response queue.

7.9 PGM_BQM Package

The type definitions and constants described in "Type Definitions" are defined in the PGM_BQM package. This package must be installed on the production Oracle server before you deploy MIPs.


See Also:

"Preparing the MIP for Production" for more information

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 Using the MIP for WebSphere MQ

This section describes how to use the MIP for WebSphere MQ.

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.


See Also:

"Preparing the MIP for Production" for more information

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.


See Also:

"MIP Restrictions" for more information

7.10.2.1 MIP Restrictions

The following table lists the restrictions that apply when using the MIP procedures or the associated type definitions for WebSphere MQ:

Parameter Restriction
correlation Its value is limited to a length of 24 characters.
message_type Its value is limited to a length of 8 characters.
msgid Its value is limited to a length of 24 bytes.
priority Its value range is between 0 and 9.
queue_name Its value is limited to a length of 48 characters.
response_queue Its value is limited to a length of 48 characters.
security_id Its value is limited to a length of 12 characters.

The MQSeries profile complies with these restrictions because the Visual Workbench verifies all input when entering the message queue profile properties. You can, however, override the profile using the advanced ENQUEUE and DEQUEUE procedures, possibly causing the MIP to raise an exception at runtime.


See Also:

"OPEN_OPTIONS_Typ" for more information about overrides

7.11 Data Conversion Package

The data conversion package generated by the Visual Workbench provides PL/SQL mapping for non-Oracle data types. It includes one or more user-defined types in PL/SQL that are mapped to the non-Oracle data type, and functions to convert between them.

Data conversion packages have two parts, a specification and a body. The specification declares the types, functions, and other information available for use by developers, and the body provides the implementation for the specification.

You use the data conversion package and so does the generated MIP:

Read the generated package specification to learn about the PL/SQL types that were created. In most cases, you need not look at the package body.

01  EMPLOYEE.
    10 EMPLOYEENOPIC X(6).
    10 FIRSTNAME            PIC X(12).
    10 LASTNAME             PIC X(12).
    10 BIRTHDATE            PIC X(10).
    10 SALARY               PIC S9999999V99 USAGE COMP-3.
    10 BONUS                PIC S9999999V99 USAGE COMP-3.
    10 COMMISSION           PIC S9999999V99 USAGE COMP-3.

"Creating a Data Profile" describes how to use the Visual Workbench to create a data profile. Use the Visual Workbench Wizard to add them to an interface profile.

The corresponding generated data conversion package EMPLOYEE for COBOL data profile EMPLOYEE contains the PL/SQL type used to map to the COBOL data type, and two functions to convert between the PL/SQL mapping data type and the COBOL data type. This is the package specification for the package EMPLOYEE:

*  Copyright (c) 1999 Oracle Corporation.  All rights reserved.
 *                                                                
 *  PL/SQL and Cobol Data Mapping Package Body EMPLOYEE.
 *                                                              
 *      PG DD  release 4.0.1.0.0                              
 *      PGMAU  release 8.0.4.1.0
 *                                                         
 *  Generated 11-MAR-99 at 15:06:33 by PGMADMIN
 */
 
CREATE or REPLACE PACKAGE EMPLOYEE IS
 
   /*---------------------------------------------------------------*/
   /* EMPLOYEE                public definitions          */
   /*---------------------------------------------------------------*/
 
   TYPE MQGET_BUFFER IS TABLE OF RAW(32767) INDEX BY BINARY_INTEGER;
   TYPE MQPUT_BUFFER IS TABLE OF RAW(32767) NOT NULL INDEX BY BINARY_INTEGER;
 
   TYPE EMPLOYEE_Typ is RECORD (
       EMPLOYEENO            CHAR(6),
       FIRSTNAME             CHAR(12),
       LASTNAME              CHAR(12),
       BIRTHDATE             CHAR(10),
       SALARY                NUMBER(9,2),
       BONUS                 NUMBER(9,2),
       COMMISSION            NUMBER(9,2));
 
 
   FUNCTION raw2EMPLOYEE(message IN MQGET_BUFFER,
                        EMPLOYEE OUT EMPLOYEE_Typ,
                        mipdiag IN CHAR) RETURN INTEGER;
 
   FUNCTION EMPLOYEE2raw(EMPLOYEE IN EMPLOYEE_Typ,
                        message OUT MQPUT_BUFFER,
                        mipdiag IN CHAR) RETURN INTEGER;
 
END EMPLOYEE;

The data conversion functions require these PL/SQL packages:

These PL/SQL packages must be installed on the production Oracle server when data conversion packages are used in run-time environments.

You can view the data conversion package by using the Code Viewer. Select the Cobol Mapping entry from the list and click View Code. The Code Viewer appears.


Note:

You can view the body of the data conversion package. Do not modify the package body or specification, as it might cause the calling Oracle application to malfunction or lead to unrecoverable errors.

7.12 COBOL Data Type Conversion

The Visual Workbench supports IBM VS COBOL II, specified as IBMVSCOBOLII when defining data profiles.

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:

Symbol Data Content
B Blank (one byte single-byte character set; two bytes double-byte character set, depending on USAGE)
0 Zero (one byte single-byte character set)
/
Slash (one byte single-byte character set)

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 COBOL Format Conversion

This section describes COBOL format conversion.

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.


See Also:

"COBOL Data Type Conversion" for more information

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.


Note:

"COBOL Data Type Conversion" for more information

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.


See Also:

"COBOL Data Type Conversion" for more information

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.


See Also:

"COBOL Data Type Conversion" for more information

7.14 PL/SQL Naming Algorithm

This section describes the PL/SQL naming algorithm.

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").

Example 7-1 COBOL definition

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.

Example 7-2 COBOL definition

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.

Example 7-3 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 (
   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.