DDL History in Oracle: A BAU Solution

In this blog post, you’ll learn step-by-step how to set up an Oracle database environment for a demo application showcasing the DDL History feature of BAU.

What is BAU?

Beg APEX Utilities (BAU) is a suite of tools designed to streamline and standardize your Oracle APEX development workflow. The DDL History is just one of the powerful features present in our product!

What is BAU DDL History?

Leveraging a database-level trigger, BAU DDL History automatically keeps track of DDL changes to your database objects. Whenever functions, packages, procedures, triggers, types, or views are modified or dropped, our trigger captures the previous state of the object along with detailed auditing information.

To prevent unwanted changes during development, BAU DDL History trigger also includes an object locking mechanism. BAU Object Lock allows developers to lock specific database objects, ensuring that only selected users can modify them until the lock is released or inactive. This adds an extra layer of safety and coordination for teams working in shared environments.

Now, let's get to work! 🚀

Database setup

Step 1: Connect as SYS on your database

sql /nolog
connect sys/"YourSYSPassword"@your_host/your_service AS SYSDBA

Step 2: Prepare the database

Execute the following commands to ensure a clean environment for the BAU DDL History Demo:

CREATE USER bau IDENTIFIED BY "YourSecurePassword" DEFAULT TABLESPACE SYSAUX QUOTA UNLIMITED ON SYSAUX;
GRANT CONNECT, RESOURCE TO bau;
GRANT SELECT ON dba_users TO bau;
GRANT SELECT ON dba_objects TO bau;

This creates the BAU user and grants the necessary permissions.

Step 3: Create BAU objects

connect bau/"YourSecurePassword"@your_host/your_service

Run the following scripts to create the necessary tables, sequences, and triggers:

CREATE SEQUENCE BAU_DDL_HISTORY_ID_SEQ;

CREATE TABLE BAU_DDL_HISTORY (
    ID                INTEGER         DEFAULT BAU_DDL_HISTORY_ID_SEQ.NEXTVAL PRIMARY KEY,
    EVENT             VARCHAR2(20),
    EVENT_TIMESTAMP   TIMESTAMP(6)    DEFAULT CURRENT_TIMESTAMP,
    EVENT_USER        VARCHAR2(256),
    MACHINE           VARCHAR2(256),
    OSUSER            VARCHAR2(256),
    OBJECT_OWNER      VARCHAR2(256),
    OBJECT_TYPE       VARCHAR2(256),
    OBJECT_NAME       VARCHAR2(256),
    OBJECT_DDL        CLOB
);

CREATE SEQUENCE BAU_OBJECT_LOCK_ID_SEQ;

CREATE TABLE BAU_OBJECT_LOCK (
    ID                INTEGER         DEFAULT BAU_OBJECT_LOCK_ID_SEQ.NEXTVAL PRIMARY KEY,
    ACTIVE            CHAR(1)         DEFAULT 'Y' NOT NULL,
    OBJECT_OWNER      VARCHAR2(256)   NOT NULL,
    OBJECT_TYPE       VARCHAR2(256)   NOT NULL,
    OBJECT_NAME       VARCHAR2(256)   NOT NULL,
    LOCKED_BY         VARCHAR2(256)   NOT NULL,
    ALLOWED_SCHEMAS   VARCHAR2(256),
    CREATED           DATE            NOT NULL,
    CREATEDBY         VARCHAR2(256)   NOT NULL,
    UPDATED           DATE,
    UPDATEDBY         VARCHAR2(256),
    CONSTRAINT BAU_OBJECT_LOCK_ACTIVE_CK CHECK (ACTIVE IN ('Y','N')),
    CONSTRAINT BAU_OBJECT_LOCK_UK UNIQUE (OBJECT_OWNER, OBJECT_TYPE, OBJECT_NAME)
);

CREATE OR REPLACE TRIGGER TRG_BAU_OBJECT_LOCK_BIU
BEFORE INSERT OR UPDATE ON BAU_OBJECT_LOCK
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        IF :NEW.ID IS NULL THEN
            :NEW.ID := BAU_OBJECT_LOCK_ID_SEQ.NEXTVAL;
        END IF;

        :NEW.CREATED := SYSDATE;
        :NEW.CREATEDBY := NVL(V('APP_USER'), USER);
    END IF;

    IF UPDATING THEN
        :NEW.UPDATED := SYSDATE;
        :NEW.UPDATEDBY := NVL(V('APP_USER'), USER);
    END IF;
END;
/

This sets up the core tables for tracking DDL events and object locking.

Step 5: Create DDL capture functionality

connect sys/"YourSYSPassword"@your_host/your_service AS SYSDBA

Create this PL/SQL function to retrieve DDL from database objects:

CREATE OR REPLACE FUNCTION BAU_GET_DDL (
    P_OWNER         VARCHAR2,
    P_OBJECT_TYPE   VARCHAR2,
    P_OBJECT_NAME   VARCHAR2
) RETURN CLOB IS
    v_source CLOB := '';
BEGIN
    -- If object is a VIEW, retrieve its text from DBA_VIEWS
    IF P_OBJECT_TYPE = 'VIEW' THEN
        BEGIN
            SELECT
                TEXT_VC
            INTO
                v_source
            FROM
                DBA_VIEWS
            WHERE
                VIEW_NAME = P_OBJECT_NAME
                AND OWNER = P_OWNER;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL;
        END;
    ELSE
        -- For other object types, retrieve source code from DBA_SOURCE
        FOR R IN (
            SELECT
                TEXT
            FROM
                DBA_SOURCE
            WHERE
                NAME = P_OBJECT_NAME
                AND OWNER = P_OWNER
                AND TYPE = P_OBJECT_TYPE
            ORDER BY 
                LINE
        ) LOOP
            -- Append each line of the object's source code to the variable
            v_source := v_source || R.TEXT;
        END LOOP;
    END IF;

    -- Return the complete source code
    RETURN v_source;
END;

Finally, create a database-level trigger to log changes on objects:

CREATE OR REPLACE TRIGGER TRG_BAU_DDL_HISTORY
BEFORE CREATE OR ALTER OR DROP ON DATABASE
WHEN (ORA_DICT_OBJ_TYPE IN ('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TRIGGER', 'TYPE', 'TYPE BODY', 'VIEW'))
DECLARE
    v_machine   VARCHAR2(256);
    v_osuser    VARCHAR2(256);
    v_locked_by VARCHAR2(256);
    v_ddl       CLOB;
    v_ddl_body  CLOB;
    v_event     VARCHAR2(256);

    -- Cursor to verify if the object belongs to an Oracle-maintained user.
    CURSOR c_oracle_maintained_user IS
    SELECT 
        USERNAME 
    FROM 
        DBA_USERS
    WHERE 
        USERNAME = ORA_DICT_OBJ_OWNER
        AND (ORACLE_MAINTAINED = 'Y' OR USERNAME LIKE 'ORDS_%');

    -- Cursor to check if the object is currently locked by BAU.
    CURSOR c_bau_object_locked IS
    SELECT 
        LOCKED_BY 
    FROM 
        BAU.BAU_OBJECT_LOCK
    WHERE 
        OBJECT_OWNER = ORA_DICT_OBJ_OWNER
        AND OBJECT_TYPE = REGEXP_SUBSTR(ORA_DICT_OBJ_TYPE, '^\S+')  -- Matches both PACKAGE and PACKAGE BODY
        AND OBJECT_NAME = ORA_DICT_OBJ_NAME
        AND USER NOT IN (SELECT COLUMN_VALUE FROM TABLE(APEX_STRING.SPLIT(ALLOWED_SCHEMAS, ':')))
        AND ACTIVE = 'Y';

BEGIN
    -- Skip logging for Oracle-maintained objects.
    OPEN c_oracle_maintained_user;
    FETCH c_oracle_maintained_user INTO v_locked_by;
    IF c_oracle_maintained_user%FOUND THEN
        CLOSE c_oracle_maintained_user;
        RETURN;
    END IF;
    CLOSE c_oracle_maintained_user;

    -- Prevent DDL operation if the object is locked by BAU.
    OPEN c_bau_object_locked;
    FETCH c_bau_object_locked INTO v_locked_by;
    IF c_bau_object_locked%FOUND THEN
        CLOSE c_bau_object_locked;
        RAISE_APPLICATION_ERROR(-20001, 'BAU - Object Locked by: ' || v_locked_by);
    END IF;
    CLOSE c_bau_object_locked;

    -- Retrieve current session information.
    SELECT 
        MACHINE, OSUSER
    INTO 
        v_machine, v_osuser
    FROM 
        SYS.V_$SESSION
    WHERE 
        SID = SYS_CONTEXT('USERENV', 'SID');

    -- Clean up any inactive locks upon object drop.
    IF ORA_SYSEVENT = 'DROP' THEN
        DELETE FROM 
            BAU.BAU_OBJECT_LOCK
        WHERE 
            OBJECT_OWNER = ORA_DICT_OBJ_OWNER 
            AND OBJECT_TYPE = REGEXP_SUBSTR(ORA_DICT_OBJ_TYPE, '^\S+')
            AND OBJECT_NAME = ORA_DICT_OBJ_NAME
            AND ACTIVE = 'N';
    END IF;

    -- Fetch current DDL definition of the object, if it already exists.
    v_ddl := BAU_GET_DDL (
        P_OWNER       => ORA_DICT_OBJ_OWNER,
        P_OBJECT_TYPE => ORA_DICT_OBJ_TYPE,
        P_OBJECT_NAME => ORA_DICT_OBJ_NAME
    );

    -- Define CREATE OR REPLACE custom event.
    IF ORA_SYSEVENT = 'CREATE' AND v_ddl IS NOT NULL THEN 
        v_event := 'CREATE OR REPLACE';
    ELSE 
        v_event := ORA_SYSEVENT;
    END IF;

    -- If DROP of a PACKAGE or TYPE, also fetch the BODY.
    IF ORA_SYSEVENT = 'DROP' AND ORA_DICT_OBJ_TYPE IN ('PACKAGE', 'TYPE') THEN
        v_ddl_body := BAU_GET_DDL (
            P_OWNER       => ORA_DICT_OBJ_OWNER,
            P_OBJECT_TYPE => ORA_DICT_OBJ_TYPE || ' BODY',
            P_OBJECT_NAME => ORA_DICT_OBJ_NAME
        );

        IF v_ddl_body IS NOT NULL THEN
            v_ddl := v_ddl || CHR(13) || v_ddl_body;
        END IF;
    END IF;

    -- Insert DDL event into BAU history log, distinguishing CREATE vs CREATE OR REPLACE.
    INSERT INTO BAU.BAU_DDL_HISTORY (
        EVENT,
        EVENT_TIMESTAMP,
        EVENT_USER,
        MACHINE,
        OSUSER,
        OBJECT_OWNER,
        OBJECT_TYPE,
        OBJECT_NAME,
        OBJECT_DDL
    ) VALUES (
        v_event,
        SYSTIMESTAMP,
        USER,
        v_machine,
        v_osuser,
        ORA_DICT_OBJ_OWNER,
        ORA_DICT_OBJ_TYPE,
        ORA_DICT_OBJ_NAME,
        v_ddl
    );
END;

Oracle APEX setup

Step 1: Create a workspace for the BAU schema

First we will need to sign in into your APEX instance internal workspace:

On the internal homescreen, click on the top right Create Workspace green button:

Set Re-use existing schema as Yes and choose BAU as the Schema Name:

Set up the workspace admin, choose a strong password! 🔑

On the internal homescreen, select the Create User option on the right tab Workspace Tasks:

Set up the workspace developer user, choose a strong password! 🔑

Click on the top right Create User green button:

Sign out of the internal workspace:

Sign in into our new BAU workspace, with the developer user:

Step 2: Import and use BAU DDL History Demo application

After signing in on our new workspace, we need to import the BAU DDL History Demo. Under App Builder on the top bar, select the Import option:

Select or drag-and-drop the BAU DDL History Demo file on the file dropzone:

Install Application!

Run Application!

Sign in into BAU DDL History Demo with the BAU workspace developer credentials:

Important

Contact us!

Explore all the advantages BAU (Beg APEX Utilities) brings to your Oracle APEX development.
To learn more about BAU, schedule a demo, or discuss how our solutions can meet your company’s unique needs, visit our website at www.begcloud.com or reach out directly at <atendimento@begcloud.com>.