V1.0
/ec-datahub-svc/rest/v1.0/tenant/{tenantId}/studies/{studyId}/{mode}/sites
Retrieves the study-permissioned Site dataset for a single study and mode.
The dataset exposes audited study-site property history through PROPERTY_NAME and PROPERTY_VALUE, together with the latest available study, organization, address, country, investigator, SDV, supply, audit and reference attributes for the site. Supported audited properties are StudyVersion, DrugDestruction, AddSubjectToSiteToggle, ScreenSubjectsAtSiteToggle, RandomizeSubjectsAtSiteToggle, DispensetoSubjectsAtSiteToggle, SiteScreeningLimit, SiteRandomizationLimit, and SiteStatus.
Supports select, filter, sort, limit, and offset query patterns.
The dataset returns rows only for sites associated to the study and having an assigned Study Version at least once.
Required permission: SiteDatasetPost.
Recommended order columns: The backend query always orders results by DH_TIMESTAMP and VERSION_START in ascending order first. If orderColumns is empty, only these two columns are used. If orderColumns is provided, the specified columns are appended after DH_TIMESTAMP and VERSION_START. For stable, deterministic pagination, use STUDY_ID, SITE_ID, PROPERTY_NAME, and VERSION_START in the orderColumns field of the request payload.
Request
-
mode(required): string
Execution
modefor the study.Allowed values:
test,active,training.Example:
test. -
studyId(required): string(uuid)
Unique study identifier supplied in the
studyIdpath parameter.Use the uppercase hexadecimal UUID value for the study.
Example:
0C7CBA3F70034C47947E2FAB086BFBF5. -
tenantId(required): string(uuid)
Unique tenant identifier supplied in the
tenantIdpath parameter.Use the uppercase hexadecimal UUID value for the tenant.
Example:
EC942244BB30163BE053BEC44C64CF34.
-
limit: integer(int32)
Minimum Value:
0Page size for the result set.
0disables pagination and returns the maximum result set allowed by the dataset endpoint.- Positive values enable pagination.
- Negative values return HTTP 400.
Example:100 -
offset: integer(int32)
Minimum Value:
0Zero-based row offset.
- Counts rows, not pages.
- To fetch the next page, repeat the same request body and set
offset = previous offset + previous count. - When
limit = 0, this value is ignored and the response echoesoffset = 0. - When
limit > 0, an offset beyond the available rows returns HTTP 200 with an empty page. - Negative values return HTTP 400.
Example:0
- application/json
objectSubmit a structured query for a Data Hub dataset.
selectColumnsis required and must contain one or more valid column names exposed by the target dataset version.whereColumnsis optional and provides structured filter predicates. Supported operators are=,!=,<>,>,>=,<,<=,LIKE,NOT LIKE,IN,NOT IN,BETWEEN,NOT BETWEEN,IS, andIS NOT.Default behavior: Data is filtered based on the
STUDY_WIDcolumn.orderColumnsis optional and controls sort order. For stable multi-page retrieval, keep the sameorderColumnsacross page requests.
-
orderColumns: array
orderColumns
Optional sort instructions to apply to the result set.
Duplicate order-by columns are rejected.
-
selectColumns: array
selectColumns
Required non-empty list of dataset column names to return.
Column validation is case-insensitive, and the response echoes canonical uppercase dataset column names in the same order.
-
whereColumns: array
whereColumns
Optional structured filter predicates.
This is the request-body equivalent of a
WHEREclause.All predicate values are supplied as strings and are parsed according to the underlying dataset column type.
arrayOptional sort instructions to apply to the result set.
Duplicate order-by columns are rejected.
-
Array of:
object QueryOrder
Sort instruction specifying a dataset column and optional order direction.
[
{
"columnName":"VERSION_START",
"sortOrder":"ASC"
}
]arrayRequired non-empty list of dataset column names to return.
Column validation is case-insensitive, and the response echoes canonical uppercase dataset column names in the same order.
-
Array of:
string
Required non-empty list of dataset column names to return.
Column validation is case-insensitive, and the response echoes canonical uppercase dataset column names in the same order.
Example:["STUDY_VERSION","STUDY_ID"]
[
"STUDY_VERSION",
"STUDY_ID"
]arrayOptional structured filter predicates.
This is the request-body equivalent of a WHERE clause.
All predicate values are supplied as strings and are parsed according to the underlying dataset column type.
-
Array of:
object QueryPredicate
Structured filter predicate used to constrain dataset results by column, operator, and value
[
{
"columnName":"STUDY_ID",
"operator":"=",
"value":[
"A86F2D0BB610404DB62D37AFA9C20B50"
]
}
]objectSort instruction specifying a dataset column and optional order direction.
-
columnName: string
Required dataset column name to sort by.
Column-name validation is case-insensitive.
Example:VERSION_START -
sortOrder: string
Allowed Values:
[ "ASC", "DESC" ]Optional sort direction.
If omitted, SQL default ascending order is used for that column. Allowed values are ASC and DESC.
Example:ASC
[
{
"columnName":"VERSION_START",
"sortOrder":"ASC"
}
]object-
columnName: string
Required dataset column name to filter on. Column-name validation is case-insensitive.Example:
STUDY_ID -
operator: string
Allowed Values:
[ ">", "<", "=", ">=", "<=", "<>", "!=", "IN", "NOT IN", "BETWEEN", "NOT BETWEEN", "LIKE", "NOT LIKE", "IS", "IS NOT" ]Required comparison operator. Use one value for =, !=, <>, >, >=, <, <="," like, and not like; one or more values for in in; exactly two between between; value equal to null is not.< div>Example:,>= -
value: array
value
Filter values as strings. Value cardinality depends on the operator: one for most operators, one or more for IN/NOT IN, exactly two for BETWEEN/NOT BETWEEN, and exactly one value equal to NULL for IS/IS NOT.
[
{
"columnName":"STUDY_ID",
"operator":"=",
"value":[
"A86F2D0BB610404DB62D37AFA9C20B50"
]
}
]array-
Array of:
string
Filter values as strings. Value cardinality depends on the operator: one for most operators, one or more for IN/NOT IN, exactly two for BETWEEN/NOT BETWEEN, and exactly one value equal to NULL for IS/IS NOT.Example:
["A86F2D0BB610404DB62D37AFA9C20B50"]
[
"A86F2D0BB610404DB62D37AFA9C20B50"
]Response
- application/json
- text/plain
200 Response
Response behavior
- The response is tabular.
columnsdefines the selected output order, and each row indatafollows that same order. - Every value in
datais returned as a JSON string ornull, including logical numbers and date/time values. - No single response column uniquely identifies a history row. Use (
STUDY_ID,SITE_ID,PROPERTY_NAME,VERSION_START) as the stable row-version key. hasMore = "true"means more matching rows exist after the current page.- If filters match no rows, or
offsetis beyond the last row, the API returns HTTP 200 withdata = [],count = 0, andhasMore = "false".
Section index
Study context | Site operations context | Organization context | Primary address and contact context | Investigator context | Audit context | Reference and system identifiers
Study context
Latest study-level identifiers and descriptive protocol attributes applied to every returned audit row.
| Column Name | Data Type | Description |
|---|---|---|
STUDY_TITLE | VARCHAR2(64 CHAR) | The When you include multiple study versions in your dataset, and each has a different title, the
|
STUDY_ID_NAME | VARCHAR2(64 CHAR) | A study ID as specified by the study manager when they created the study, such as a protocol acronym and protocol number. |
STUDY_REFNAME | VARCHAR2(64 CHAR) | Indicates the study's reference name used by the system. This value is composed by STUDY_ID_NAME converted to uppercase with blank spaces removed. Once created, this value never changes, even if STUDY_ID_NAME is changed. |
STUDY_PHASE | VARCHAR2(64 CHAR) | Indicates the study phase. Possible values include I, I/II, II, II/III, III, IV, or Other. |
BLINDING_TYPE | VARCHAR2(64 CHAR) | Indicates the study blinding type. Possible values include OpenLabel, Blinded, OpenLabelBlinded, or Observational. |
STUDY_THERAPEUTIC_AREA | VARCHAR2(64 CHAR) | Indicates the study therapeutic area. Possible values include CardiologyVascularDiseases, DentalOralHealth, Dermatology, Endocrinology, FamilyMedicine, Gastroenterology, GeneticDisease, HealthyVolunteers, Hematology, Hepatology, Immunology, InfectionsInfectiousDisease, Musculoskeletal, Nephrology, Neurology, NutritionWeightLoss, ObstetricsGynecology, Oncology, Ophthalmology, Otolaryngology, PediatricsNeonatology, PlasticSurgery, PharmacologyToxicology, Podiatry, PsychiatryPsychology, PulmonaryRespiratoryDiseases, Rheumatology, Sleep, Trauma, Urology, or Vaccines. |
Parent topic: Section index
Site operations context
Audited study-site property history, current site activity markers, SDV settings, and supply controls associated with the site.
| Column Name | Data Type | Description |
|---|---|---|
SITE_ID_NAME | VARCHAR2(50 CHAR) | Indicates the site ID as entered by a site manager when they created or last modified a site. |
PROPERTY_NAME | VARCHAR2(100 CHAR) | Canonical identifier of the audited study-site property. Possible values are StudyVersion, DrugDestruction, AddSubjectToSiteToggle, ScreenSubjectsAtSiteToggle, RandomizeSubjectsAtSiteToggle, DispensetoSubjectsAtSiteToggle, SiteScreeningLimit, SiteRandomizationLimit, or SiteStatus. |
PROPERTY_VALUE | VARCHAR2(2048 CHAR) | Value captured for the audited property in the current audit row. Possible values include 10.0.0.14, Active, or true.... |
SITE_IS_ACTIVE | CHAR(1) | Current active indicator derived from the latest site status. Y means the latest site status is Active. Possible values include Y or N. |
SITE_ACTIVE_MIN_DATE | TIMESTAMP(6) | Earliest date on which the site became active. |
SITE_ACTIVE_MAX_DATE | TIMESTAMP(6) | Latest date on which the site became active. |
SITE_INACTIVE_MIN_DATE | TIMESTAMP(6) | Earliest date on which the site became inactive. |
SITE_INACTIVE_MAX_DATE | TIMESTAMP(6) | Latest date on which the site became inactive. |
SDV_STRATEGY_NAME | VARCHAR2(255) | Name of the SDV Strategy, as entered by the study manager. |
INIT_SUBJ_COUNT | VARCHAR2(2048) | Number of initial subjects included in the SDV strategy. |
INIT_SDV_TYPE | VARCHAR2(2048) | Type of Source Data Verification: All Questions or Critical Questions. Possible values include All Questions or Critical Questions Only. |
REMAIN_SUBJ_PERC | VARCHAR2(2048) | Percentage of remaining subjects included in the SDV strategy. |
REMAIN_SDV_TYPE | VARCHAR2(2048) | Type of Source Data Verification: All Questions or Critical Questions. Possible values include All Questions or Critical Questions Only. |
SUPPLY_STRATEGY_NAME | VARCHAR2(64 CHAR) | Name of the strategy that was applied to the site. |
RESUPPLY_TYPE | VARCHAR2(64 CHAR) | Indicates the type of resupply strategy. Possible values include MinMax or Predictive. |
Parent topic: Section index
Organization context
Organization profile, lifecycle, and legal attributes associated with the site.
| Column Name | Data Type | Description |
|---|---|---|
ORG_NAME | VARCHAR2(500 CHAR) | Organization name associated with the site profile. |
ORG_ID_NAME | VARCHAR2(50 CHAR) | Identifier name of the organization associated with the site. |
ORG_STATUS | VARCHAR2(32767 CHAR) | Indicates the status of the organization associated with the site. Possible values include New, Active, or Retired. |
ORG_CATEGORY | VARCHAR2(32767 CHAR) | Indicates the type of organization. Possible values include Hospital, Teaching Hospital, Medical Clinic, or Other. |
ORG_TIMEZONE | VARCHAR2(2048 CHAR) | Indicates the time zone the site is currently placed on as specified by a site manager. |
WEBSITE | VARCHAR2(255 CHAR) | Web URL associated with the institution. |
ORG_INT_IDENTIFIER | VARCHAR2(500 CHAR) | Integration ID associated with the institution, when available. |
ORG_TAX_IDENTIFIER | VARCHAR2(50 CHAR) | Tax Identification Number (TIN) associated with the institution. |
VAT_NUMBER | VARCHAR2(50 CHAR) | Value Added Tax (VAT) identification number associated with the institution. |
HAS_MSA | VARCHAR2(5 CHAR) | Indicates whether the institution has a Master Service Agreement with established terms and conditions. Possible values include Y or N. |
MSA_START_DATE | TIMESTAMP(6) | Start date on which the Master Service Agreement becomes effective. |
MSA_END_DATE | TIMESTAMP(6) | End date through which the Master Service Agreement remains effective. |
Parent topic: Section index
Primary address and contact context
Primary address, contact, and location fields associated with the site or organization.
| Column Name | Data Type | Description |
|---|---|---|
IS_MAIN_PRIMARY | CHAR(1) | Indicates whether the address is configured as the site's main primary address. |
ADDRESS_TYPE | CHAR(7) | Indicates the address purpose configured for the site. In Data Hub, this dataset currently exposes only the Primary address. |
ADDRESS_1 | VARCHAR2(150 CHAR) | A site's first address as entered by the site manager when they created or last modified the site. |
ADDRESS_2 | VARCHAR2(150 CHAR) | A site's second address as entered by the site manager when they created or last modified the site. |
CITY | VARCHAR2(100 CHAR) | A site's city as entered by the site manager when they created or last modified the site. |
STATE_PROVINCE | VARCHAR2(100 CHAR) | A site's state, province, or county as entered by the site manager when they created or last modified the site. |
ZIP_POSTAL_CODE | VARCHAR2(20 CHAR) | The Zip Postal Code associated with a site's address. |
COUNTRY | VARCHAR2(4000 CHAR) | Country in the site's primary address. |
SITE_COUNTRY | VARCHAR2(4000 CHAR) | Country associated with the site profile. |
PHONE | VARCHAR2(500 CHAR) | The contact phone number as entered by the site manager when they created or last modified the site. |
ALT_PHONE | VARCHAR2(500 CHAR) | Alternate phone number associated with the contact. |
FAX | VARCHAR2(500 CHAR) | The contact fax number as entered by the site administrator when they created or last modified the site. |
Parent topic: Section index
Investigator context
Principal-investigator identity, credential, licensing, and specialty attributes associated with the site.
| Column Name | Data Type | Description |
|---|---|---|
PREFIX | VARCHAR2(2048 CHAR) | Indicates the principal investigator's prefix. Possible values include 1st Lt, Adm, Atty, Brother, Capt, Chief, Cmdr, Col, Dean, Dr, Elder, Father, Gen, Gov, Hon, Lt Col, Maj, MSgt, Mr, Mrs, Ms, Prince, Prof, Rabbi, Rev, or Sister. |
FIRST_NAME | VARCHAR2(2048 CHAR) | First name of the associated contact. |
MIDDLE_NAME | VARCHAR2(100 CHAR) | Middle name of the associated contact. |
LAST_NAME | VARCHAR2(2048 CHAR) | Last name of the associated contact. |
SUFFIX | VARCHAR2(32767 CHAR) | Indicates the principal investigator's suffix. Possible values include II, III, IV, CPA, DDS, Esq, JD, Jr, LLD, MD, PhD, Ret, RN, Sr, or DO. |
DEGREE | VARCHAR2(200 CHAR) | Degree or credential associated with the investigator. |
INV_STATUS | VARCHAR2(32767 CHAR) | Indicates the investigator status. Possible values include New, Active, Disbarred, Deceased, Retired, Restricted, or Active Not In Use By Study. |
INV_SPECIALTY | VARCHAR2(32767 CHAR) | Indicates the investigator's primary specialty. Possible values include Allergy and immunology, Anesthesiology, Dermatology, Diagnostic radiology, Emergency medicine, Family medicine, Internal medicine, Medical genetics, Neurology, Nuclear medicine, Obstetrics and gynecology, Ophthalmology, Pathology, Pediatrics, Physical medicine and rehabilitation, Preventive medicine, Psychiatry, Radiation oncology, Surgery, or Urology. |
INV_SUB_SPECIALTY | VARCHAR2(500 CHAR) | Indicates the investigator sub-specialty. Valid values depend on INV_SPECIALTY. Possible values include Critical care medicine, Hospice and palliative care, Pain medicine, Pediatric anesthesiology, or Sleep medicine. |
INV_TIMEZONE | VARCHAR2(50 CHAR) | Indicates the investigator time zone. Sample values shown are representative, not exhaustive. Possible values include America/New_York, Europe/Madrid, Asia/Calcutta, or Asia/Kolkata. |
INV_IDENTIFIER | VARCHAR2(50 CHAR) | Associated ID of the contact. This identifier is unique across contacts at your organizations. |
INV_INT_IDENTIFIER | VARCHAR2(500 CHAR) | Integration ID associated with the contact, when available. |
INV_TAX_IDENTIFIER | VARCHAR2(50 CHAR) | Tax Identification Number (TIN) associated with the contact. |
INV_LISC_NUM | VARCHAR2(500 CHAR) | Licensed research number associated with the contact. |
INV_MED_IDENTIFIER | VARCHAR2(500 CHAR) | Medical identifier associated with the contact. |
INV_NPI | VARCHAR2(500 CHAR) | National Provider Identifier (NPI) associated with the contact. This is a unique identification number for covered health care providers. |
INV_DEA_NUM | VARCHAR2(2048 CHAR) | The DEA registration number. |
INV_DEA_EXP | VARCHAR2(2048 CHAR) | Indicates the expiration date of the DEA Registration Number as defined by a site manager. |
INV_EXEMPTION_NUM | VARCHAR2(500 CHAR) | Exemption number associated with the contact. |
Parent topic: Section index
Audit context
Audit-trail metadata describing property-history row versioning, acting users, and reason-for-change details.
| Column Name | Data Type | Description |
|---|---|---|
VERSION_START | TIMESTAMP(6) | Audit trail timestamp when this version of the dataset row became effective. Together with VERSION_END, it defines the validity window for the row version. |
VERSION_END | TIMESTAMP(6) | Indicates the date and time of when data was changed, if the data is not current. |
OPERATION_TYPE | VARCHAR2(16 CHAR) | Audit trail field that indicates how the dataset row version was produced.
|
CURRENT_STUDY_ROLE_NAME | VARCHAR2(100 CHAR) | Current study role name of the user who updated the dataset row. If the user's study role changes later, this field shows the user's current study role. |
USER_NAME | VARCHAR2(255 CHAR) | Audit trail field that represents the user who performed the action. The value for this column may represent a user's actual username or a user's email address, depending on how the user login was defined in Oracle Life Sciences IAMS. |
OBJECT_VERSION_NUMBER | NUMBER | Audit trail field that represents the version number of the data. |
REASON | VARCHAR2(255 CHAR) | Audit trail reason recorded for the dataset row change. Populated when a reason for change is provided for the row update. |
COMMENTS | VARCHAR2(2048 CHAR) | Comment recorded for the dataset row change, when provided. |
IS_CURRENT | CHAR(1 CHAR) | Audit trail flag that indicates whether the dataset row version is the current version (Y) or a historical version (N). |
Parent topic: Section index
Reference and system identifiers
System identifiers, numeric reference keys, and technical linkage fields carried with the dataset row.
| Column Name | Data Type | Description |
|---|---|---|
STUDY_ID | RAW(16 BYTE) | GUID of the study. |
STUDY_WID | NUMBER(10) | A number that represents the unique identifier of the study. |
SITE_ID | RAW(16 BYTE) | GUID of the site. |
SITE_WID | NUMBER(10) | A number that represents the unique identifier of a site. |
USER_ID | RAW(16 BYTE) | GUID of the user. |
USER_WID | NUMBER(10) | Indicates a user's numeric identifier. |
CURRENT_STUDY_ROLE_ID | RAW(16 BYTE) | The ID associated with the study role assigned to the user who updated the given record. If the user study role changes, this field will show the current study role ID of the given user. |
CURRENT_STUDY_ROLE_WID | NUMBER(10) | Numeric identifier of the role of the user who updated the given record. If the user study role changes, this field will show the current study role of the given user. |
DH_TIMESTAMP | TIMESTAMP(6) | A timestamp that indicates when the data became available in the dataset. |
SOFTWARE_VERSION_NUMBER | NUMBER | Software version number stored on the underlying source row that produced the Site dataset record. |
Parent topic: Section index
objectTabular dataset response for a dynamic Data Hub query.
columns defines the selected output order, and each row in data aligns positionally to that list.
-
columns: array
columns
Ordered dataset column names selected by the client.
Each row in
datauses this exact order. -
count: integer
(int32)
Number of rows returned in the current page.
Example:2 -
data: array
data
Row data aligned positionally with
columns.Each cell is serialized as a JSON string or
null, even for logical numbers and timestamps. -
hasMore: string
String pagination flag (
"true"or"false") indicating whether more matching rows exist after the current page.Returned as a string for backward compatibility.
Example:false -
limit: integer
(int32)
Requested page size.
Dataset endpoints commonly use
0to mean unpaginated or all rows.Example:100 -
offset: integer
(int32)
Zero-based row offset applied to the result set.
Dataset endpoints that disable pagination with
limit = 0typically echooffset = 0in the response.Example:0 -
totalResults: integer
(int32)
Total number of matching rows across all pages after filters are applied.
This can be non-zero even when the current page is empty because the requested offset is beyond the last row.
Example:2
arrayOrdered dataset column names selected by the client.
Each row in data uses this exact order.
-
Array of:
string
Ordered dataset column names selected by the client.
Each row in
datauses this exact order.Example:["STUDY_ID","RECORD_ID","STATUS"]
[
"STUDY_ID",
"RECORD_ID",
"STATUS"
]arrayRow data aligned positionally with columns.
Each cell is serialized as a JSON string or null, even for logical numbers and timestamps.
-
Array of:
array items
Row data aligned positionally with
columns.Each cell is serialized as a JSON string or
null, even for logical numbers and timestamps.
[
[
"A86F2D0BB610404DB62D37AFA9C20B50",
"REC001",
"Active"
],
[
"A86F2D0BB610404DB62D37AFA9C20B50",
"REC002",
"Inactive"
]
]arrayRow data aligned positionally with columns.
Each cell is serialized as a JSON string or null, even for logical numbers and timestamps.
-
Array of:
string
Row data aligned positionally with
columns.Each cell is serialized as a JSON string or
null, even for logical numbers and timestamps.Example:[["A86F2D0BB610404DB62D37AFA9C20B50","REC001","Active"],["A86F2D0BB610404DB62D37AFA9C20B50","REC002","Inactive"]]
[
[
"A86F2D0BB610404DB62D37AFA9C20B50",
"REC001",
"Active"
],
[
"A86F2D0BB610404DB62D37AFA9C20B50",
"REC002",
"Inactive"
]
]400 Response
Returned when the request is invalid.
- Causes include invalid
tenantIdorstudyId, negativelimitoroffset, emptyselectColumns, invalid dataset columns, invalidwhereColumnsoperator/value combinations, duplicateorderColumns, invalidsortOrder, and malformed typed filter values. - Error payload format:
{"status":"failed","result":null,"errorData":{"errorCode":"...","errorMessage":"...","details":{...}},"version":1}.
objectStandard Data Hub response envelope.
Successful responses return status as "success" and populate result. Failed responses return status as "failed" and populate errorData.
-
errorData: object
errorData
Machine-readable error payload when
statusis "failed".This field is null when
statusis "success". -
result: object
result
Payload returned by the API when
statusis "success".This field is null when
statusis "failed". -
status: string
Overall request processing status.
Allowed values are "success" and "failed".
Example:success -
version: integer
(int32)
Envelope version number.Example:
1
objectMachine-readable error payload when status is "failed".
This field is null when status is "success".
{
"errorCode":"VALIDATION_ERROR",
"errorMessage":"Invalid column name specified in the select columns: SITE_ID1",
"details":{
"field":"selectColumns"
}
}objectPayload returned by the API when status is "success".
This field is null when status is "failed".
{
"message":"Operation succeeded"
}401 Response
Returned when the request cannot be authenticated.
The response body is empty.
403 Response
Returned when the request is authenticated but the caller is not authorized to access the requested resource.
The response body contains a plain-text authorization message.
Possible response bodies:
Either the resource does not exist, or the user cannot access the resource.The entity is in maintenance mode, or the user cannot access the resource.
string500 Response
Returned when an unexpected server-side error occurs while processing the request.
The response body contains the platform error payload with the error code and description.