Loading Oracle Cloud Time and Labor Time Entries via REST API using PL/SQL (UTL_HTTP)
Oracle Cloud HCM Time and Labor integrations are commonly built using Python, OCI Integration, or other middleware tools. However, there are situations where a lightweight, database-driven approach is useful, especially for rapid prototyping, controlled batch processing, or small-volume integrations.
In this example, the REST API is invoked directly from a local Oracle Express Edition database running on a personal system. The solution uses a custom staging table, PL/SQL procedures, UTL_HTTP, and an Oracle wallet for secure HTTPS communication. The example focuses on loading time entries through the REST API flow and not a separate time event integration model.
Important Note: This REST API supports creation of complete time entries that include both start time and stop time (or measured duration). It leverages the same configuration used for standard time entry in Oracle Time and Labor and does not require any additional setup.
However, if the business requirement involves capturing partial time events (such as only start time or only stop time), a different REST endpoint (/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/) must be used. This approach requires additional configurations, including event definitions and device-related attributes.
This approach is particularly useful for
- One time – time entries load for payroll parallel run testing, time parallel run testing
- Rapid prototyping
- Small-scale integrations
- Controlled batch processing from staging tables
- Enter the time entries in custom application/UI and then load into OTL with more controlled way.
Solution Overview
The design follows a simple but effective pattern:
- Insert time entry records into a custom staging table.
- Read unprocessed rows from the staging table.
- Construct the REST payload in PL/SQL.
- Call Oracle Cloud HCM REST API using
UTL_HTTP. - Capture response details and update the processing status.
Step 1: Create the staging table
The staging table stores inbound time entry data and maintains processing metadata such as status, request identifiers, API response, and error message.
CREATE TABLE xx_time_entry_stg (
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
reporter_id VARCHAR2(100) NOT NULL,
start_time VARCHAR2(50) NOT NULL,
stop_time VARCHAR2(50) NOT NULL,
payroll_time_type VARCHAR2(200) NOT NULL,
comment_txt VARCHAR2(1000),
process_status VARCHAR2(30) DEFAULT 'NEW',
request_id NUMBER,
event_id NUMBER,
response_clob CLOB,
error_message VARCHAR2(4000),
created_on DATE DEFAULT SYSDATE,
updated_on DATE
);This structure provides a strong foundation for traceability and troubleshooting:
- process_status tracks whether a row is NEW, PROCESSING, SUCCESS, or ERROR.
- response_clob stores the full REST API response for audit and debugging.
- error_message stores a summarized failure reason.
- request_id and event_id help connect the database record to the HCM transaction.
Step 2: Insert sample time entry data
A sample record can be inserted into the staging table before processing.In this example we are using 4 days’ time entries for 1 employee.
INSERT INTO xx_time_entry_stg (
reporter_id,
start_time,
stop_time,
payroll_time_type,
comment_txt
) VALUES (
'1129',
'2026-03-16T08:00:00.000-04:00',
'2026-03-16T17:00:00.000-04:00',
'XX Regular Hours US',
'Loaded from custom table-1'
);INSERT INTO xx_time_entry_stg (
reporter_id,
start_time,
stop_time,
payroll_time_type,
comment_txt
) VALUES (
'1129',
'2026-03-17T08:00:00.000-04:00',
'2026-03-17T17:00:00.000-04:00',
'XX Regular Hours US',
'Loaded from custom table-1'
);
INSERT INTO xx_time_entry_stg (
reporter_id,
start_time,
stop_time,
payroll_time_type,
comment_txt
) VALUES (
'1129',
'2026-03-18T08:00:00.000-04:00',
'2026-03-18T17:00:00.000-04:00',
'XX Regular Hours US',
'Loaded from custom table-1'
);
INSERT INTO xx_time_entry_stg (
reporter_id,
start_time,
stop_time,
payroll_time_type,
comment_txt
) VALUES (
'1129',
'2026-03-19T08:00:00.000-04:00',
'2026-03-19T17:00:00.000-04:00',
'XX Regular Hours US',
'Loaded from custom table-1'
);
COMMIT;The timestamp values are provided in ISO 8601 format with timezone offset, which is important for Oracle Cloud HCM REST requests.
Step 3: Build the PL/SQL procedure that loads one time entry
The main procedure reads a staging row, constructs the JSON payload, invokes the REST API, reads the response, and updates the row status.
CREATE OR REPLACE PROCEDURE xx_load_one_time_entry (
p_id IN NUMBER,
p_host IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_wallet_path IN VARCHAR2,
p_wallet_password IN VARCHAR2
) AS
l_url VARCHAR2(2000);
l_req UTL_HTTP.req;
l_resp UTL_HTTP.resp;
l_buffer VARCHAR2(32767);
l_response_clob CLOB;
l_payload CLOB;
l_reporter_id xx_time_entry_stg.reporter_id%TYPE;
l_start_time xx_time_entry_stg.start_time%TYPE;
l_stop_time xx_time_entry_stg.stop_time%TYPE;
l_payroll_type xx_time_entry_stg.payroll_time_type%TYPE;
l_comment_txt xx_time_entry_stg.comment_txt%TYPE;
l_request_id NUMBER;
l_event_id NUMBER;
l_event_status VARCHAR2(100);
l_status_code PLS_INTEGER;
l_reason_phrase VARCHAR2(256);
l_error_message VARCHAR2(4000);
BEGIN
SELECT reporter_id,
start_time,
stop_time,
payroll_time_type,
NVL(comment_txt, 'Loaded from custom table')
INTO l_reporter_id,
l_start_time,
l_stop_time,
l_payroll_type,
l_comment_txt
FROM xx_time_entry_stg
WHERE id = p_id
FOR UPDATE;
UPDATE xx_time_entry_stg
SET process_status = 'PROCESSING',
updated_on = SYSDATE,
error_message = NULL
WHERE id = p_id;
l_url := 'https://' || p_host || '/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests';
l_payload :=
'{'
|| '\"processInline\":\"Y\",'
|| '\"processMode\":\"TIME_SAVE\",'
|| '\"timeRecordEvent\":['
|| '{'
|| '\"startTime\":\"' || REPLACE(REPLACE(l_start_time, '\', '\\'), '\"', '\\\"') || '\",'
|| '\"stopTime\":\"' || REPLACE(REPLACE(l_stop_time, '\', '\\'), '\"', '\\\"') || '\",'
|| '\"reporterIdType\":\"PERSON\",'
|| '\"reporterId\":\"' || REPLACE(REPLACE(l_reporter_id, '\', '\\'), '\"', '\\\"') || '\",'
|| '\"comment\":\"' || REPLACE(REPLACE(l_comment_txt, '\', '\\'), '\"', '\\\"') || '\",'
|| '\"operationType\":\"ADD\",'
|| '\"timeRecordEventAttribute\":['
|| '{'
|| '\"attributeName\":\"PayrollTimeType\",'
|| '\"attributeValue\":\"' || REPLACE(REPLACE(l_payroll_type, '\', '\\'), '\"', '\\\"') || '\"'
|| '}'
|| ']'
|| '}'
|| ']'
|| '}';
UTL_HTTP.set_detailed_excp_support(TRUE);
UTL_HTTP.set_response_error_check(FALSE);
UTL_HTTP.set_wallet(p_wallet_path, p_wallet_password);
l_req := UTL_HTTP.begin_request(l_url, 'POST', 'HTTP/1.1');
UTL_HTTP.set_authentication(l_req, p_username, p_password, 'Basic', FALSE);
UTL_HTTP.set_header(l_req, 'Content-Type', 'application/json');
UTL_HTTP.set_header(l_req, 'Accept', 'application/json');
UTL_HTTP.set_header(l_req, 'Content-Length', TO_CHAR(DBMS_LOB.getlength(l_payload)));
UTL_HTTP.write_text(l_req, l_payload);
l_resp := UTL_HTTP.get_response(l_req);
l_status_code := l_resp.status_code;
l_reason_phrase := l_resp.reason_phrase;
DBMS_LOB.createtemporary(l_response_clob, TRUE);
BEGIN
LOOP
UTL_HTTP.read_text(l_resp, l_buffer, 32767);
DBMS_LOB.writeappend(l_response_clob, LENGTH(l_buffer), l_buffer);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
NULL;
END;
UTL_HTTP.end_response(l_resp);
BEGIN
SELECT JSON_VALUE(l_response_clob, '$.timeRecordEventRequestId' RETURNING NUMBER),
JSON_VALUE(l_response_clob, '$.timeRecordEvent[0].timeRecordEventId' RETURNING NUMBER),
JSON_VALUE(l_response_clob, '$.timeRecordEvent[0].eventStatus' RETURNING VARCHAR2(100))
INTO l_request_id, l_event_id, l_event_status
FROM dual;
EXCEPTION
WHEN OTHERS THEN
l_request_id := NULL;
l_event_id := NULL;
l_event_status := NULL;
END;
IF l_status_code BETWEEN 200 AND 299
AND l_event_status = 'COMPLETE'
THEN
UPDATE xx_time_entry_stg
SET process_status = 'SUCCESS',
request_id = l_request_id,
event_id = l_event_id,
response_clob = l_response_clob,
updated_on = SYSDATE,
error_message = NULL
WHERE id = p_id;
ELSE
l_error_message :=
SUBSTR(
'HTTP ' || l_status_code || ' ' || l_reason_phrase || ' | ' ||
DBMS_LOB.SUBSTR(l_response_clob, 3000, 1),
1,
4000
);
UPDATE xx_time_entry_stg
SET process_status = 'ERROR',
request_id = l_request_id,
event_id = l_event_id,
response_clob = l_response_clob,
updated_on = SYSDATE,
error_message = l_error_message
WHERE id = p_id;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
BEGIN
UTL_HTTP.end_response(l_resp);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
l_error_message :=
SUBSTR(
SQLERRM || ' | ' || UTL_HTTP.get_detailed_sqlerrm,
1,
4000
);
UPDATE xx_time_entry_stg
SET process_status = 'ERROR',
updated_on = SYSDATE,
error_message = l_error_message
WHERE id = p_id;
COMMIT;
RAISE;
END;
/How the procedure works
- It selects a specific staging row by ID and locks it using
FOR UPDATE. - It updates the row to PROCESSING before the API call starts.
- It constructs the JSON payload dynamically in PL/SQL.
- It uses
UTL_HTTPto send the HTTPS POST request. - It reads the REST response into a CLOB.
- It parses the response using
JSON_VALUE. - It marks the row as SUCCESS only when the HTTP status is successful and the returned event status is COMPLETE.
- Otherwise, it stores the failure details and marks the record as ERROR.
REST payload design
The payload uses processInline = "Y" and processMode = "TIME_SAVE". This is important because the integration is being used to save a time entry through the REST API processing flow. You can change processMode = "TIME_SUBMIT"or "TIME_ENTER" depending on your requirement. The payload also passes:
- startTime - start time of the time entry
- stopTime - stop time of the time entry
- reporterIdType - PERSON
- reporterId - Person Number
- comment - any custom optional comment
- operationType - ADD
- PayrollTimeType - Time type defined in the layout component set(element name not the display name)
This makes the example practical for a common Time and Labor requirement where the integration needs to create a time entry with a payroll time type.
Important implementation details
1. Wallet configuration for HTTPS
The call uses UTL_HTTP.set_wallet so the local Oracle database can trust the SSL certificate presented by Oracle Cloud. Without a properly configured wallet, HTTPS requests typically fail.
2. Content-Length header
The code explicitly sets the Content-Length header using DBMS_LOB.getlength(l_payload). This is a very important detail because missing this header can result in HTTP 411 Length Required errors.
3. JSON escaping
The code carefully escapes backslashes and double quotes when building the payload. This is important because comments and other text values can break the JSON structure if they are not escaped correctly.
4. Detailed exception support
UTL_HTTP.set_detailed_excp_support(TRUE) makes troubleshooting easier by exposing more detailed network and HTTP diagnostics.
5. Response parsing
The API response is stored in a CLOB and parsed for:
timeRecordEventRequestIdtimeRecordEventIdeventStatus
This gives the solution both technical traceability and business-level visibility into the outcome.
Step 4: Batch procedure for multiple entries
To process all new staging rows, a batch procedure loops through records with status NEW and calls the single-row loader for each entry.
CREATE OR REPLACE PROCEDURE xx_load_time_entries_batch (
p_host IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_wallet_path IN VARCHAR2,
p_wallet_password IN VARCHAR2
) AS
BEGIN
FOR r IN (
SELECT id
FROM xx_time_entry_stg
WHERE process_status = 'NEW'
ORDER BY id
) LOOP
BEGIN
xx_load_one_time_entry(
p_id => r.id,
p_host => p_host,
p_username => p_username,
p_password => p_password,
p_wallet_path => p_wallet_path,
p_wallet_password => p_wallet_password
);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/This design keeps the processing model simple. Each row is handled independently, which makes error isolation easier. One failed row does not stop the remaining rows from being attempted.
Step 5: Execute the batch load
The batch procedure can be executed with the Oracle Cloud host, credentials, and wallet details.
BEGIN
xx_load_time_entries_batch(
p_host => 'your-test-instance.fa.us6.oraclecloud.com',
p_username => 'your_username',
p_password => 'your_password',
p_wallet_path => 'file:c:\wallet',
p_wallet_password => 'your_wallet_password'
);
END;
/After execution, the staging table can be queried to confirm whether each record completed successfully or failed with an error.
SELECT *
FROM xx_time_entry_stg
ORDER BY id;Testing the solution
A good test cycle for this approach is:
- Record
inserted in staging table
- Run the
batch procedure
- Status
transitions:
- NEW
→ PROCESSING → SUCCESS / ERROR
- Response
captured in response_clob
- Review
the failed records if any
- Validate
time entries from the front-end(You don’t have to run any job/any scheduled
process to see the entries in the front-end)
This gives a clean and repeatable way to validate the integration end to end.
Common issues and troubleshooting
| Issue | Possible Cause | Typical Fix |
|---|---|---|
| ORA-29273 | HTTP request failed | Check wallet setup, ACLs, hostname, SSL trust, and network connectivity |
| ORA-28759 | Failure to open wallet file | Verify wallet path, database service account permissions, and wallet contents |
| HTTP 411 Length Required | Missing Content-Length header | Explicitly set Content-Length before writing the payload |
| Invalid JSON or API rejection | Malformed payload or unescaped text | Escape values correctly and validate the payload structure |
Strengths of this pattern
- Simple and easy to understand
- Fully database-driven
- Good audit trail through staging and stored responses
- Useful for low-volume or controlled integrations
- No separate middleware is required for the basic flow
Practical limitations
- It is not the best pattern for very high transaction volumes.
- Basic authentication may not be the preferred production-grade option in all environments.
- Sequential row-by-row processing can become a bottleneck at scale.
- Credential handling must be hardened before production use.
Recommendations for production hardening
- Move credentials out of anonymous execution blocks and store them securely.
- Add a retry strategy for transient failures.
- Introduce better logging for request payloads, response codes, and execution timestamps.
- Add validation logic before calling the REST API.
- Consider parallel processing for larger data volumes.
- Review whether OAuth-based authentication is required in the target environment.
Key takeaway
This example shows how Oracle Database can act as a lightweight integration layer for Oracle Cloud HCM Time and Labor. By combining a staging table, PL/SQL, UTL_HTTP, and wallet-based HTTPS configuration, it is possible to load time entries directly from a local Oracle XE environment while still preserving auditability and control.
For teams that want a compact and database-centric integration option, this is a practical pattern worth considering.
Disclaimer
This example is intended for demonstration and learning purposes. Additional controls, security hardening, validation, and operational considerations may be required before adopting the pattern in a production environment.
.png)
