Post Data to OTM via HTTP Request

If your source system is an Oracle database — such as Oracle E-Business Suite (EBS) — you can post GlogXML data directly to OTM using PL/SQL without requiring a middleware layer. This is done using Oracle’s built-in UTL_HTTP package, which allows a PL/SQL program to make HTTP requests to external URLs.

This approach is useful for simple, direct integrations where installing or configuring a middleware platform is not practical. For more complex scenarios involving multiple source systems or non-XML formats such as EDI or flat files, a dedicated middleware tool such as Oracle SOA/BPEL, webMethods, or MuleSoft is a better fit — see the Inbound Integrations (XML) topic for an overview of all available methods.

Prerequisites:

UTL_HTTP access: The Oracle database user running this code must have the EXECUTE privilege on UTL_HTTP. In Oracle 12c and later, an Oracle Wallet must also be configured if you are connecting to OTM over HTTPS.

OTM WMServlet URL: You need the OTM application server URL. The WMServlet endpoint follows this pattern: http://<OTM-SERVER>:<PORT>/GC3/glog.integration.servlet.WMServlet

OTM credentials: A valid OTM username and password. For production use, create a dedicated integration user with appropriate domain access — do not use DBA.ADMIN.

How it works:

The PL/SQL program builds a GlogXML-formatted XML string, then uses UTL_HTTP to POST it to the OTM WMServlet endpoint. OTM receives the request, processes the XML, and returns a response containing the transmission number. You can then query the Transmission Manager in OTM using that number to verify whether the data was processed successfully.

Complete PL/SQL Example — Load a Location record:

DECLARE

  -- OTM WMServlet endpoint URL
  v_chr_url      VARCHAR2(1000) := 'http://OTM-SERVER:7777/GC3/glog.integration.servlet.WMServlet';

  -- XML payload variable — build this string programmatically from your source data
  v_data_in      VARCHAR2(32767);

  -- UTL_HTTP request and response handles
  v_http_req     UTL_HTTP.req;
  v_http_resp    UTL_HTTP.resp;
  v_chr_resp_val VARCHAR2(3000);

BEGIN

  -- Build the GlogXML transmission payload
  -- GLogXMLElement here is 'Location' — replace with the appropriate OTM element
  -- for the data you want to load (e.g. OrderRelease, Shipment, ServiceProvider, etc.)
  -- Refer to GlogXML.xsd in OTM for the full element structure:
  --   Business Process Automation > Integration > Integration Manager > Retrieve Schemas
  v_data_in :=
    '<Transmission xmlns="http://xmlns.oracle.com/apps/otm/transmission/v6.4">
       <TransmissionHeader>
         <UserName>DBA.ADMIN</UserName>
         <Password>CHANGEME</Password>
         <IsProcessInSequence>Y</IsProcessInSequence>
       </TransmissionHeader>
       <TransmissionBody>
         <GLogXMLElement>
           <Location>
             <TransactionCode>IU</TransactionCode>
             <LocationGid>
               <Gid>
                 <DomainName>YOURDOMAIN</DomainName>
                 <Xid>TEST-LOCATION-001</Xid>
               </Gid>
             </LocationGid>
             <LocationName>Test Location, Houston, TX, USA</LocationName>
             <Address>
               <AddressLines>
                 <SequenceNumber>1</SequenceNumber>
                 <AddressLine>1039 East Plantation Drive</AddressLine>
               </AddressLines>
               <City>CLUTE</City>
               <ProvinceCode>TX</ProvinceCode>
               <PostalCode>77531</PostalCode>
               <CountryCode3Gid>
                 <Gid><Xid>USA</Xid></Gid>
               </CountryCode3Gid>
             </Address>
             <LocationRefnum>
               <LocationRefnumQualifierGid>
                 <Gid><Xid>ORIGIN</Xid></Gid>
               </LocationRefnumQualifierGid>
               <LocationRefnumValue>CUSTOMER</LocationRefnumValue>
             </LocationRefnum>
             <LocationRole>
               <LocationRoleGid>
                 <Gid><Xid>CUSTOMER</Xid></Gid>
               </LocationRoleGid>
             </LocationRole>
           </Location>
         </GLogXMLElement>
       </TransmissionBody>
     </Transmission>';

  -- Configure UTL_HTTP timeouts and exception handling
  UTL_HTTP.set_transfer_timeout(1000);
  UTL_HTTP.set_detailed_excp_support(ENABLE => TRUE);

  -- Open the HTTP POST request to OTM WMServlet
  v_http_req := UTL_HTTP.begin_request(v_chr_url, 'POST');

  -- Set HTTP headers — content-type must be text/xml for OTM to parse the body correctly
  UTL_HTTP.set_header(v_http_req, 'content-type',   'text/xml');
  UTL_HTTP.set_header(v_http_req, 'content-length',  LENGTH(v_data_in));

  -- Write the XML payload to the request body
  UTL_HTTP.write_text(v_http_req, v_data_in);

  -- Read OTM's response — it will contain the Reference Transmission Number
  v_http_resp    := UTL_HTTP.get_response(v_http_req);
  UTL_HTTP.read_text(v_http_resp, v_chr_resp_val, 3000);
  UTL_HTTP.end_response(v_http_resp);

  -- Print the response to verify the transmission number
  DBMS_OUTPUT.put_line('OTM Response: ' || v_chr_resp_val);

END;
/

Key Points in the Code:

v_data_in: This is where you build the XML payload. In a real integration, you would construct this string dynamically from your source tables — for example, querying customer or location data from EBS and mapping it to the OTM XML structure.

content-type: text/xml: This header is required for OTM to correctly parse the XML body. Using text/html can cause the WMServlet to reject or mishandle the payload.

GLogXMLElement: Change this element to match the OTM object you want to load — OrderRelease, Shipment, ServiceProvider, RateOffering, and so on. Refer to GlogXML.xsd for the correct structure of each element.

OTM Response: OTM returns a transmission acknowledgement XML containing a ReferenceTransmissionNo. Use this number to query the Transmission Manager and confirm that the record was processed without errors.

TransactionCode IU: Stands for Insert or Update. OTM inserts the record if the GID does not exist, or updates it if it does. Use D to delete a record.

Verifying the Result in OTM:

After running the PL/SQL block, note the transmission number from the response output. Then navigate to:

Business Process Automation > Integration > Transmission Manager

Query for the transmission number. The status should show as PROCESSED. If there are errors, click the Report button for details.

Note: If you are building multiple integrations from an external system — especially when the source data arrives in non-XML formats such as EDI, fixed-width flat files, or proprietary formats — a middleware platform such as Oracle SOA/BPEL, webMethods, or MuleSoft is the recommended approach. These tools provide transformation, routing, error handling, and monitoring capabilities that are difficult to replicate in custom PL/SQL code.

What’s Next:

Another simple way to load data into OTM without middleware is using CSV files — useful when your source data is in spreadsheet form. The next topic covers how to export a CSV template from OTM, populate it, and upload it back.

Next Topic: CSV Data Uploads

Post Data to OTM via HTTP Request

Questions & Discussion