ZKTeco Attendance Machine Integration with Oracle Database: A Practical Guide

Many companies in Pakistan and the Gulf use ZKTeco face and fingerprint terminals, but still move attendance by USB export and Excel every month. That process is slow and error-prone, and it makes payroll disputes common. This guide explains how to bring punches from the machines directly into an Oracle database, and how to turn raw punches into reliable attendance for payroll. It is based on integration projects for factories, schools and multi-branch offices.

Looking for a ready solution instead of building it yourself? See our ZKTeco attendance integration service.

1. Understand what the machine gives you

A terminal does not calculate attendance. It stores punch records (also called attendance logs or transactions). Each record typically contains:

• the user ID enrolled on the device (this must match an employee code in your system)
• the date and time of the punch, from the device clock
• the verification method (face, fingerprint, card or password)
• a status or work code on some models (for example check-in or check-out), which employees often press incorrectly
• the device serial number or ID, which tells you which branch or gate it came from

Everything else – shift, late, overtime, absent – is your software’s job.

2. Choose a communication method

Which methods are available depends on the device model and firmware, so check this before promising real-time sync to management.

3. Design the tables

Keep raw punches separate from processed attendance. Never update or delete raw punches – they are your evidence when an employee disputes a salary deduction.

CREATE TABLE att_devices (
  device_id     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  serial_no     VARCHAR2(50) UNIQUE NOT NULL,
  branch_id     NUMBER NOT NULL,
  ip_address    VARCHAR2(45),
  last_sync_at  TIMESTAMP
);

CREATE TABLE att_raw_punches (
  punch_id      NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  device_id     NUMBER NOT NULL REFERENCES att_devices,
  device_user   VARCHAR2(30) NOT NULL,
  punch_time    DATE NOT NULL,
  verify_mode   VARCHAR2(20),
  received_at   TIMESTAMP DEFAULT SYSTIMESTAMP,
  CONSTRAINT att_raw_uk UNIQUE (device_id, device_user, punch_time)
);

The unique constraint is important: devices often resend logs after a reconnect, and the constraint silently stops duplicates (handle DUP_VAL_ON_INDEX in your loader).

4. Map device users to employees

The device only knows its own user ID. Keep a mapping table (or use the employee code as the device user ID everywhere). Decide one rule and enforce it during enrolment – mismatched IDs are the most common cause of “missing attendance”.

5. Turn punches into attendance with PL/SQL

Run a processing job (for example every 15 minutes with DBMS_SCHEDULER, plus a nightly full run) that, for each employee and day:

1. finds the employee’s shift for that date (including night shifts that cross midnight)
2. takes the first punch inside the shift window as time-in and the last as time-out, ignoring repeated punches within a few minutes
3. applies grace time to decide on-time or late, and checks early leave
4. calculates worked hours and overtime according to the employee category
5. marks absent, leave, holiday or weekly off where no valid punches exist

-- simplified: first and last punch per employee per day
SELECT e.emp_id,
       TRUNC(p.punch_time)  AS att_date,
       MIN(p.punch_time)    AS time_in,
       MAX(p.punch_time)    AS time_out
FROM   att_raw_punches p
JOIN   employees e ON e.device_user = p.device_user
WHERE  p.punch_time >= TRUNC(SYSDATE) - 1
GROUP  BY e.emp_id, TRUNC(p.punch_time);

This simple query does not handle night shifts – real projects assign each punch to a shift window first, then group. Store the result in a daily attendance table with a status column that HR can correct, keeping an audit of manual changes.

6. Pitfalls that break payroll

• Device clock drift. Synchronize device time regularly; a few minutes of drift creates false late marks.
• Night shifts. A 10 PM–6 AM shift spans two calendar dates; group by shift, not by date.
• Check-in/check-out keys. Do not rely on employees pressing the right status key; use first-in and last-out logic.
• Offline devices. Monitor last_sync_at and alert IT when a device has not synced for hours.
• Capacity. Each model has limits for faces, fingerprints and log storage; clear old logs on the device only after confirming they are saved.
• Privacy. Biometric templates stay on the device; your database needs punch records, not fingerprint images. Restrict who can edit attendance.

7. Show it in Oracle APEX

With the data in Oracle, an APEX application can provide a live attendance dashboard, late and absent reports, manual correction with approval, and monthly summaries for payroll. See our guide to KPI dashboards in Oracle APEX and the payroll features checklist.

ZKTeco is a trademark of its respective owner. AbairTech Solution is an independent integrator and is not affiliated with ZKTeco.