Data Archiving with PHP and Oracle: Creating Archive Tables, Triggers, and PHP Scripts

In large applications, archiving data in Oracle databases is essential for performance; moving older rows to an archive table reduces query response time. This guide shows how to use PHP together with Oracle to set up data archiving.

1. Preparing for Data Archiving

1.1 Create Archive Table

Use the CREATE TABLE statement in Oracle to define a table for archived records.

CREATE TABLE archive_data (
    id NUMBER,
    name VARCHAR2(50),
    created_date DATE
);

1.2 Create Archive Trigger

A trigger monitors changes on the main table and inserts deleted or updated rows into the archive table.

CREATE OR REPLACE TRIGGER archive_trigger
AFTER DELETE OR UPDATE OF created_date ON main_table
FOR EACH ROW
BEGIN
    INSERT INTO archive_data (id, name, created_date)
    VALUES (:old.id, :old.name, :old.created_date);
END;

2. Using PHP for Archiving

2.1 Connect to Oracle Database

Establish a connection with oci_connect and handle connection errors.

<?php
$conn = oci_connect('username', 'password', 'database');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
?>

2.2 Execute Archiving Operation

Run a DELETE statement to move records older than one year to the archive table.

<?php
$sql = 'DELETE FROM main_table WHERE created_date < SYSDATE - 365';
$stmt = oci_parse($conn, $sql);
if (!$stmt) {
    $e = oci_error($conn);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$result = oci_execute($stmt);
if (!$result) {
    $e = oci_error($stmt);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
oci_free_statement($stmt);
?>

2.3 Query Archived Data

Retrieve and display rows from the archive table.

<?php
$sql = 'SELECT * FROM archive_data';
$stmt = oci_parse($conn, $sql);
if (!$stmt) {
    $e = oci_error($conn);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$result = oci_execute($stmt);
if (!$result) {
    $e = oci_error($stmt);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
while ($row = oci_fetch_array($stmt, OCI_ASSOC)) {
    echo $row['ID'] . ", " . $row['NAME'] . ", " . $row['CREATED_DATE'] . "<br>";
}
oci_free_statement($stmt);
?>

Conclusion

By combining PHP with Oracle, developers can easily implement data archiving to boost database performance and reduce query latency, tailoring archiving strategies to specific business needs such as timestamps or data importance.