Data Transfer Integration Point

Introduction

The Data Transfer integration point processes a JSON input payload, similar to the Operational Reporting Integration Point, along with two additional attributes to capture the data transfer format and description. This IP logs any access to PHI data.

This IP accepts request to query base views (including PHI data) or reporting views.

Request

Operations

This IP supports the following POST operation:

http://[hostName]:[portNumber]/[api-context-root]/datatransfer
If the request is sent by JET UI, access to base views are restricted.

Payload

The dataTransferFormat supports the following formats.

  • CSV

  • JSON

  • XML (available only for cloud customers)

  • PARQUET (available only for cloud customers)

{ "columns": [ {"expr": "a.col1"},
               {"expr": "b.col2"},
               {"expr": "sum(c.col3)"},
               {"expr": "substr(a.col2,1,1)", "alias": "initial"}
],
  "from": "view a",
  "joins":
    [ {"type": "inner",       "object": "view2 b", "condition": "a.fk1_id=b.id"},
      {"type": "left outer",  "object": "view3 c", "condition": "a.fk2_id=c.id"},
      {"type": "right outer", "object": "view4 d", "condition": "a.fk3_id=d.id"},
      {"type": "full outer",  "object": "view5 e", "condition": "a.fk4_id=e.id"}
    ],
  "where": "a.col2 > 'abc' and datecol > DATE '2024-02-15'",
  "groupBy": "c.col3",
  "orderBy": "a.col1 desc, b.col2 asc",
  "offset": 20,
  "fetchNext": 10,
  "dataTransferFormat": "JSON",
  "description": "Policies in progress for brand ABC"
}

Authorization

A user authorization configuration requires access to this feature. The relevant access restriction is datatransfer IP.

Response

The response contains the HTTP 201 status code with a link to the data transfer in the location header to monitor progress. For example: http://[hostName]:[portNumber]/[api-context-root]/datatransfer/1234

The response returns the HTTP 204 status code:

  • If you do not have access to one or more views that are included in the request.

  • When the query returns no rows.

Response Payload in On-Premises Deployment

{
  "status": "",
  "extraInfo": "",
  "links": [
    {
      "rel": "file",
      "href": "http://[hostName]:[portNumber]/api/datatransfer/1234/file/1"
    },
    {
      "rel": "file",
      "href": "http://[hostName]:[portNumber]/api/datatransfer/1234/file/2"
    }
  ]
}

Response Payload in On-Premises Deployment For JET UI

{
  "status": "",
  "extraInfo": "",
  "links": [
    {
      "rel": "file",
      "href": "http://[hostName]:[portNumber]/api/datatransfer/1234/file"
    }
  ]
}

Response Payload in SaaS Deployment

{
  "status": "",
  "extraInfo": "",
  "links": [
    {
      "rel": "file",
      "href": "https://objectstorage.us-ashburn-1.oraclecloud.com/n/mynamespace/b/my_bucket/o/file1.json?PAR=f12345abcd67890efg123hijklm456no"
    },
    {
      "rel": "file",
      "href": "https://objectstorage.us-ashburn-1.oraclecloud.com/n/mynamespace/b/my_bucket/o/file2.json?PAR=f12345abcd67890efg123hijklm456no"
    }
  ]
}

Response Payload in SaaS Deployment: If the Request Header LINK-TYPE is set to BUCKET_FILE_PATH

{
    "status": "",
    "extraInfo": "",
    "storageDetails": {
        "format": "JSON",
        "files": [
            {
                "path": "POLICIES/export_data_files/2025/2025-02/2025-02-01/124/file1.json"
            },
            {
                "path": "POLICIES/export_data_files/2025/2025-02/2025-02-01/124/file2.json"
            }
        ]
    }
}

Attribute Details

  • status: This field contains any one of the values, that is, In Process, Success, or Failure.

  • extraInfo: Shows error details if the data transfer fails (for example, when connectivity to Object Storage fails). This attribute is present in the response only when the status is Failure.

  • links: One or more links to download the data transfer files. The links appear only when the status is Success.

    • On-Prem deployment: The links contain the URLs to download the files via the data transfer integration point.

    • SaaS deployment: The links contain the PAR (Pre-Authenticated Request) URL to download the files directly from Object Storage The PAR URLs are valid for a limited duration. The PAR URL expires after 30 minutes Attempting to access a PAR URL after the TTL expires results in an error.

    • SaaS deployment for OIG: The exact paths of the files created in Object Storage are provided in the response. Oracle Insurance Gateway directly accesses the Object Storage bucket and retrieves the files.

Response File Format

The data is exported using the DBMS_CLOUD package, so the response file format depends on its implementation. As a result, the response files must be parsed according to the specific format used.

Exporting large CLOB (Character Large Object) columns may not work correctly in SaaS (software-as-a-service) deployments due to a known issue in the DBMS_CLOUD package. See Bug 38183169 - DBMS_CLOUD EXPORT_DATA silently truncates large CLOB values for more details.

JSON

The response is not a valid JSON file. Instead, each row from the table is serialized as an individual JSON object, separated by a newline character.

{"name":"John Williams", "email":"John.Williams@someserver.com", "country":"US"}
{"name":"Jane Doe", "email":"Jane.Doe@someserver.com", "country":"AU"}

XML

The response is not a complete XML file. Each row is returned as a standalone XML fragment, separated by a newline character.

<RECORD><COUNTRY>US</COUNTRY><EMAIL>John.Williams@someserver.com</EMAIL><NAME>John Williams</NAME></RECORD>
<RECORD><COUNTRY>AU</COUNTRY><EMAIL>Jane.Doe@someserver.com</EMAIL><NAME>Jane Doe</NAME></RECORD>

CSV

The CSV response includes a header row. Each line corresponds to a row in the table.

Country,Email,Name
US,John.Williams@someserver.com,John Williams
AU,Jane.Doe@someserver.com,Jane Doe

Error Messages

Table 1. Error Messages
Code Severity Message

OHI-ORV-001

Fatal

No storage destination is configured for the data transfer.

OHI-ORV-002

Fatal

{dataTransferFormat} format is not allowed.

Monitoring the Progress

A monitor link helps to monitor the data transfer process.

Request Message

A GET request on: http://[hostName]:[portNumber]/[api-context-root]/datatransfer/{id} returns the status of the data transfer.

Response Message

The response structure varies based on the deployment type, that is, On-Premises or SaaS.