DEV Community: Pranav Bakare

[Boost]

Pranav Bakare — Sun, 15 Mar 2026 03:06:01 +0000

The Ultimate Terraform Tutorial: From Beginner to Advanced (2025 Guide)

UWABOR KING COLLINS ・ Sep 30 '24

#devops #cloudcomputing #terraform #tutorial

A Simple Photo Album Built with Django | mrcaption4

Pranav Bakare — Mon, 26 Jan 2026 06:39:52 +0000

A Simple Photo Album Built with Django

Photo Album application using Django and Python

Summary -

This project is a Django-based Image Gallery Application designed to showcase real-world media handling and scalable web architecture. It enables users to upload images, add descriptions, and organize content using dynamic category management. The application supports category-based filtering, allowing users to view all images or narrow results efficiently. Each image is rendered on a dedicated detail page for focused viewing. Built using Django’s Model–View–Template (MVT) architecture, the project ensures clean code separation and maintainability. The user interface is developed with Bootstrap 5, delivering a responsive and consistent UI with minimal custom CSS. Django forms handle secure file uploads using multipart/form-data and CSRF protection. Images are managed using Django’s ImageField with Pillow for processing. For production readiness, the project integrates AWS S3 via django-storages and boto3 to provide scalable cloud-based media storage. Proper static and media file configuration supports both development and deployment environments. The system efficiently manages database relationships using foreign keys. Overall, this project demonstrates backend development expertise, cloud integration, and production-ready Django practices.

Project Explaination-

I built a category-based Photo Album application using Django and Python to explore real-world media handling and clean backend architecture. The application allows users to browse photos by category, view them in a gallery layout, and open each image on a dedicated detail page.
The project follows Django’s Model–View–Template (MVT) pattern, ensuring clear separation of concerns and maintainable code. Categories improve navigation, while thumbnail previews keep the interface fast and visually clean.
Each photo opens on its own page, creating a focused viewing experience and laying the foundation for future features like likes, views, or comments. The application is deployed on Render, demonstrating production-level configuration and public hosting.
This project reflects practical Django development—combining structured backend logic, media handling, and real-world deployment into a clean, scalable web application.

🔗 Live Demo: https://django-photoalbum-2025.onrender.com/

10-Line Insightful Summary

This Django Photo Album application demonstrates real-world media handling using a clean and structured backend. Photos are organized into categories for better navigation and filtering. The gallery layout ensures fast and intuitive browsing. Each image opens on a standalone detail page for focused viewing. The project follows Django’s MVT architecture for maintainability. Thumbnail previews enhance performance and UI clarity. Deployment on Render showcases production readiness. The application is simple, scalable, and extendable. It reflects strong Django fundamentals. A solid portfolio project with real-world relevance.

CICD YML file Learnings!

Pranav Bakare — Sun, 28 Sep 2025 05:34:37 +0000

CICD YML file Learnings!

In our project, we automated database deployments by integrating Liquibase with GitLab CI/CD. We structured the repository with environment-specific folders and a master changelog, ensuring version-controlled schema changes. The pipeline runs nightly with three stages — Pre-DB (backups, validations, SQL preview), DB (Liquibase update & tagging), and Post-DB (data fixes, stats gathering, recompiling invalid objects). Database credentials and JDBC drivers are securely managed through CI variables and artifacts. Each changeset includes rollback scripts and is previewed using liquibase updateSQL before execution, reducing production risks. Scheduled pipelines are configured with proper rules, and logs and previews are stored as artifacts for auditing. This approach minimized manual effort, improved reliability, and ensured consistent database deployments across environments.

Gitlab cicd pipeline Build automation

In GitLab, a scheduled pipeline allows automated execution of jobs at specific times using cron expressions. The .gitlab-ci.yml file defines the pipeline’s structure, including stages, jobs, and the scripts to run. Each job can specify rules or only: [schedules] to control when it runs, ensuring scheduled jobs don’t trigger on pushes or merge requests. When a schedule is created in the GitLab UI, it targets a branch and defines the timing and optional variables. At the scheduled time, GitLab triggers a pipeline on the selected branch with CI_PIPELINE_SOURCE="schedule". The runner picks up jobs whose rules match the scheduled context and executes the defined scripts. Job logs and statuses are captured in GitLab for monitoring and debugging. Variables defined in the schedule are passed into the pipeline, allowing dynamic configuration. Using stages ensures jobs execute in the intended order even in complex pipelines. Overall, this integration provides a reliable, automated mechanism for running repetitive tasks without manual intervention. By combining .gitlab-ci.yml rules and GitLab schedules, teams can fully control what runs and when, maintaining flexibility and efficiency in CI/CD workflows.

Summary-

Constructed and refined GitLab CI/CD pipelines (YAML-based) to automate execution of PL/SQL scripts and database changes, eliminating manual interventions.

Understanding GitLab Scheduled Pipelines and YAML Configuration

Pranav Bakare — Sat, 27 Sep 2025 06:54:28 +0000

Understanding GitLab Scheduled Pipelines and YAML Configuration

In GitLab,

A scheduled pipeline allows automated execution of jobs at specific times using cron expressions. The .gitlab-ci.yml file defines the pipeline’s structure, including stages, jobs, and the scripts to run. Each job can specify rules or only: [schedules] to control when it runs, ensuring scheduled jobs don’t trigger on pushes or merge requests. When a schedule is created in the GitLab UI, it targets a branch and defines the timing and optional variables.
At the scheduled time, GitLab triggers a pipeline on the selected branch with CI_PIPELINE_SOURCE="schedule". The runner picks up jobs whose rules match the scheduled context and executes the defined scripts.
Job logs and statuses are captured in GitLab for monitoring and debugging. Variables defined in the schedule are passed into the pipeline, allowing dynamic configuration. Using stages ensures jobs execute in the intended order even in complex pipelines.
Overall, this integration provides a reliable, automated mechanism for running repetitive tasks without manual intervention. By combining .gitlab-ci.yml rules and GitLab schedules, teams can fully control what runs and when, maintaining flexibility and efficiency in CI/CD workflows.

Here’s the basic and minimal .gitlab-ci.yml file structure you need to schedule your job every day at 11:11 AM from GitLab.

This is the simplest version (no workflow, no extra variables):

stages:
  - run

scheduled_job:
  stage: run
  script:
    - echo "Scheduled job running at $(date)"
    - echo "Put your script logic here"
  rules:
    # Run only when the pipeline is triggered by a schedule
    - if: '$CI_PIPELINE_SOURCE == "schedule"'
      when: always
    # Prevent job from running on push/merge
    - when: never

Steps to Schedule in GitLab:

Go to Project → CI/CD → Schedules.
Click New schedule.
Enter:
Interval pattern: 11 11 * * *
Target branch: the branch where the above .gitlab-ci.yml exists (e.g., main)
Timezone: your local timezone
Click Save.

Now this job will run every day at 11:11 AM and execute the commands you placed in script:

Finding All Child Tables Referencing a Parent Table in Oracle

Pranav Bakare — Thu, 25 Sep 2025 11:36:00 +0000

Child Tables Referencing a Parent Table in Oracle

In Oracle databases, understanding foreign key relationships is essential before altering or dropping a parent table. Using the views ALL_CONSTRAINTS and ALL_CONS_COLUMNS, you can identify which child tables reference a specific parent table and the exact columns involved.
A simple SQL query filtering constraint_type = 'R' (foreign keys) provides this mapping clearly. The output shows child tables, child columns, parent tables, and their constraints. This quick analysis helps avoid errors and ensures safe schema changes.

When working with relational databases, it’s often necessary to understand how tables are related through foreign keys. For example, if you want to drop or modify a parent table, you’ll need to know which child tables reference it.
In Oracle, you can discover these relationships using the data dictionary views ALL_CONSTRAINTS and ALL_CONS_COLUMNS

SELECT
    a.table_name      AS child_table,
    a.constraint_name AS child_constraint,
    a.column_name     AS child_column,
    c_pk.table_name   AS parent_table,
    c_pk.constraint_name AS parent_constraint,
    c_pk.column_name  AS parent_column
FROM all_cons_columns a
JOIN all_constraints c 
     ON a.owner = c.owner
    AND a.constraint_name = c.constraint_name
JOIN all_cons_columns c_pk 
     ON c.r_owner = c_pk.owner
    AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'  -- 'R' = Foreign Key
  AND c_pk.table_name = UPPER('MST_IRR');

Test VS CODE Snippet - CodeSnap

Pranav Bakare — Sat, 20 Sep 2025 06:47:05 +0000

Automating Partition Cleanup in Oracle with PL/SQL and DBMS Scheduler

Pranav Bakare — Sat, 20 Sep 2025 06:39:57 +0000

Automating Partition Cleanup in Oracle with PL/SQL and DBMS Scheduler

The procedure begins by reading from the configuration table CMN_DROP_PARTITION, which defines which partitioned tables need cleanup, how many partitions should be retained, and which partitions must be excluded.
For each active table entry, it retrieves the table owner, table name, and retention count. Next, it queries the Oracle data dictionary views (dba_tab_partitions) to fetch all available partitions for that table in ascending order by position. Once the partitions are listed, it checks the configured exclusion partition so that it is never dropped, even if it falls outside the retention period.
It then calculates how many partitions must be kept, ensuring the most recent ones (based on partition position) remain untouched. The procedure identifies the older partitions that are beyond the retention limit and marks them as candidates for dropping. Using dynamic SQL, it generates and executes ALTER TABLE DROP PARTITION statements for each of these outdated partitions.
Error handling is usually included to skip over issues like locked partitions or dependencies. Finally, this process ensures partition cleanup is automated, data-driven, and consistent, improving database performance and storage efficiency without manual intervention.

The solution automates partition maintenance in Oracle by combining a PL/SQL procedure, a configuration table, and a scheduled job. The procedure CMN_DROP_PARTITIONS_PR dynamically identifies and drops partitions older than the retention rules defined in CMN_DROP_PARTITION.
The configuration table controls how many partitions to keep, which partitions to exclude, and which tables are active for cleanup, making the logic fully data-driven. A DBMS Scheduler job then executes the procedure every Monday at 3 AM, ensuring cleanup happens consistently without manual effort.
Together, these components deliver a reliable, automated, and flexible way to keep partitioned tables lean and manageable.

Managing partitioned tables efficiently is crucial in large-scale databases, especially when dealing with high-volume transactional or logging data.
If older partitions are not purged regularly, the database grows uncontrollably, impacting performance, storage, and maintenance overhead.
To address this challenge, we implemented an automated partition cleanup mechanism in Oracle. The solution involves three components working together:
(1) a PL/SQL procedure that drops old partitions beyond a configurable retention period,
(2) a configuration table that defines retention rules and exceptions per table, and
(3) a DBMS Scheduler job that triggers the cleanup procedure at regular intervals without manual intervention. This approach ensures that database housekeeping is automated, safe, and consistent, reducing operational effort while keeping only the required data available.

1. The Cleanup Procedure —

At the core of this solution is the PL/SQL procedure CMN_DROP_PARTITIONS_PR.
It scans all partitioned tables configured for cleanup, checks how many partitions should be retained, and automatically issues ALTER TABLE DROP PARTITION commands for older partitions.
Any errors are logged but don’t interrupt the overall execution, ensuring that one bad partition doesn’t stop the cleanup process.

CREATE OR REPLACE PROCEDURE CMN_OWNR.CMN_DROP_PARTITIONS_PR IS
    v_Dropstr_Sql varchar2(4000);
BEGIN
    FOR i IN (
        SELECT * FROM (
            SELECT u.table_name,
                   partition_name,
                   partition_position,
                   RANK() OVER(PARTITION BY u.table_name ORDER BY u.partition_position DESC) rnk,
                   cdp.no_of_part_to_retn retn_prt_days
            FROM user_tab_partitions u, cmn_drop_partition cdp
            WHERE cdp.table_name = u.table_name
              AND cdp.exclude_partition != u.partition_name
              AND cdp.active = 'Y')
        WHERE rnk > retn_prt_days
        ORDER BY rnk
    ) LOOP
        BEGIN
            v_Dropstr_Sql := 'ALTER TABLE ' || i.table_name || ' DROP PARTITION ' || i.partition_name || ' UPDATE INDEXES';
            EXECUTE IMMEDIATE v_Dropstr_Sql;
            v_Dropstr_Sql := '';
        EXCEPTION WHEN OTHERS THEN
            ngcs_util.db_err_log_pr('CMN','CMN_DROP_PARTITIONS_PR',i.table_name||':'||i.partition_name,NULL,SQLERRM,NULL,NULL);
        END;
    END LOOP;

ngcs_util.db_err_log_pr('CMN','CMN_DROP_PARTITIONS_PR','Drop Partition',NULL,'CMN PARTITIONS DROP JOB GOT COMPLETED SUCCESSFULLY AT:'||systimestamp,NULL,NULL);
EXCEPTION WHEN OTHERS THEN
    ngcs_util.db_err_log_pr('CMN','CMN_DROP_PARTITIONS_PR','Main EXCP',NULL,SQLERRM,NULL,NULL);
END CMN_DROP_PARTITIONS_PR;
/

2. Configuration Table —

The cleanup rules are defined in the CMN_DROP_PARTITION table. For each table, we can specify how many partitions to retain, which partitions to exclude from deletion, and whether the rule is active.
This makes the procedure flexible and data-driven.

CREATE TABLE CMN_DROP_PARTITION (
  table_name         VARCHAR2(100),
  exclude_partition  VARCHAR2(100),
  table_owner        VARCHAR2(15),
  no_of_part_to_retn NUMBER(5),
  active             VARCHAR2(1),
  created_date       TIMESTAMP(6),
  created_by         VARCHAR2(30),
  modified_date      TIMESTAMP(6),
  modified_by        VARCHAR2(30)
);


-- Sample entries
INSERT INTO CMN_DROP_PARTITION (table_name, exclude_partition, 
active, table_owner, no_of_part_to_retn, created_date, created_by)
VALUES('CMN_UPLD_DATA', 'P_PREDEC24','Y', 'CMN_OWNR',31, SYSDATE, 'ACCELYA-IT');
INSERT INTO CMN_DROP_PARTITION (table_name, exclude_partition, 
active, table_owner, no_of_part_to_retn, created_date, created_by)
VALUES('CMN_LOG_REQ_RESP', 'P_PRE24','Y', 'CMN_OWNR',7, SYSDATE, 'ACCELYA-IT');
COMMIT;

In this example:

For CMN_UPLD_DATA, the last 31 partitions are retained, but partition P_PREDEC24 is excluded from deletion.
For CMN_LOG_REQ_RESP, the last 7 partitions are retained, excluding P_PRE24.

3. Scheduling with DBMS Scheduler —

Finally, the procedure is scheduled using Oracle’s DBMS Scheduler.
We created a job that runs the cleanup process every Monday at 3 AM, ensuring weekly housekeeping without manual effort.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'CMN_DROP_PARTITION_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'cmn_drop_partitions_pr;',
    start_date      => systimestamp,
    repeat_interval => 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=03; BYMINUTE=00; BYSECOND=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'JOB To drop older partition from CMN tables');
END;
/

This job guarantees the cleanup procedure runs at a fixed schedule, keeping the database lean and partitions under control.

Summary —

With this implementation, we achieved a robust and automated partition cleanup strategy. The procedure ensures that only the latest required partitions are kept, the configuration table provides flexibility and control without code changes, and the DBMS Scheduler job automates execution at off-peak hours. Together, these three components create a reliable maintenance framework that improves performance, reduces storage costs, and minimizes manual intervention in partition management.

Partitioning

Pranav Bakare — Fri, 19 Sep 2025 16:33:39 +0000

Pranav Bakare

Sep 18 '25

Automating Old Partition Cleanup in Oracle with PL/SQL

#automation #database #sql

Comments

3 min read

Automating Old Partition Cleanup in Oracle with PL/SQL

Pranav Bakare — Thu, 18 Sep 2025 12:03:30 +0000

Automating Old Partition Cleanup in Oracle with PL/SQL

This PL/SQL block is designed to automate partition maintenance for all tables owned by the schema CMN_OWNR. It loops through each table partition (skipping ones with INIT in their name), extracts the partition boundary date from the high_value column, and checks if it is on or before 1st June 2025.
If the condition is met, it tries to drop the partition along with updating indexes; however, if the partition cannot be dropped due to being the last one (ORA-14758), it instead truncates the partition to remove data while keeping structure intact.
This ensures that only old partitions are purged while preserving the latest required ones. In short, it’s a safe cleanup mechanism for managing historical data in partitioned tables.

DECLARE
  v_date DATE;
  v_sql  VARCHAR2(4000);
BEGIN
  FOR i IN (SELECT dp.table_name,
                   dp.partition_name,
                   dp.high_value,
                   dp.partition_position
              FROM dba_tab_partitions dp
             WHERE dp.table_owner = 'CMN_OWNR'
               and dp.partition_name not like '%INIT%'
            --AND table_name = 'CMN_LOG'
             ORDER BY dp.table_name, partition_position) LOOP
    v_date := TO_DATE(substr(i.high_value, 12, 10), 'RRRR-MM-DD');
    IF v_date <= to_date('01-JUN-2025', 'dd-mon-yyyy') THEN
      v_sql := 'ALTER TABLE CMN_OWNR.' || i.table_name ||
               ' DROP PARTITION ' || i.partition_name || ' UPDATE INDEXES';
      BEGIN
        EXECUTE IMMEDIATE v_sql;
      EXCEPTION
        WHEN OTHERS THEN
          IF SQLCODE = -14758 THEN
            v_sql := 'ALTER TABLE CMN_OWNR.' || i.table_name ||
                     ' TRUNCATE PARTITION ' || i.partition_name ||
                     ' UPDATE INDEXES';
            EXECUTE IMMEDIATE v_sql;
          ELSE
            dbms_output.put_line(SQLERRM);
            dbms_output.put_line(v_sql);
          END IF;
      END;
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;

🔎 Step-by-step explanation

1. Loop through all partitions

FOR i IN (
   SELECT dp.table_name,
          dp.partition_name,
          dp.high_value,
          dp.partition_position
   FROM dba_tab_partitions dp
   WHERE dp.table_owner = 'CMN_OWNR'
     AND dp.partition_name NOT LIKE '%INIT%'
   ORDER BY dp.table_name, partition_position
) LOOP

Finds all partitions of all tables owned by CMN_OWNR.
Skips partitions whose name contains INIT.
Loops through each partition in order.

2. Extract partition boundary date

v_date := TO_DATE(SUBSTR(i.high_value, 12, 10), 'RRRR-MM-DD');

dba_tab_partitions.high_value is a text expression like:
TO_DATE(' 2025-06-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', ...)
SUBSTR(...,12,10) picks 2025-06-01.
TO_DATE(...,'RRRR-MM-DD') converts that to an Oracle DATE → 01-JUN-2025.

3. Check if partition is old

IF v_date <= TO_DATE('01-JUN-2025','DD-MON-YYYY') THEN

If the partition’s upper boundary date is before or equal to 01-JUN-2025, it’s considered old → purge it.

4. Try to drop the partition

v_sql := 'ALTER TABLE CMN_OWNR.' || i.table_name ||
         ' DROP PARTITION ' || i.partition_name || 
' UPDATE INDEXES';
EXECUTE IMMEDIATE v_sql;

Builds dynamic SQL to drop the old partition.
UPDATE INDEXES ensures local/global indexes remain usable.

5. Handle errors

EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -14758 THEN
      v_sql := 'ALTER TABLE CMN_OWNR.' || i.table_name ||
               ' TRUNCATE PARTITION ' || i.partition_name ||
               ' UPDATE INDEXES';
      EXECUTE IMMEDIATE v_sql;
    ELSE
      dbms_output.put_line(SQLERRM);
      dbms_output.put_line(v_sql);
    END IF;

If DROP fails with ORA-14758 (cannot drop the last partition in a range), it falls back to:
ALTER TABLE ... TRUNCATE PARTITION ...
→ keeps the partition structure but deletes its data.
Any other error is logged with dbms_output.

6. Global error handling

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;

If the whole block fails for some reason, the error is printed.

Summary —

This PL/SQL script automatically manages old partitions in Oracle tables owned by CMN_OWNR. It checks each partition’s HIGH_VALUE date and, if it is on or before 01-JUN-2025, it attempts to drop the partition. If dropping is not allowed (SQL error -14758), it instead truncates the partition while updating indexes. This helps keep partitioned tables lean by cleaning up historical data without manual intervention.

Audit Trigger for Tracking User Updates and Deletions in table

Pranav Bakare — Thu, 18 Sep 2025 11:57:51 +0000

Audit Trigger for Tracking User Updates and Deletions in table

This trigger audits every update or delete on the GEN_MST_USR_LST table.
It records the old data into an audit table with action type (U or D) and logs transaction details for traceability.

Summary of working of Trigger

The trigger AUD_ULT_AR_UD is designed to maintain an audit trail whenever a row in the GEN_MST_USR_LST table is updated or deleted. It fires after each row change and excludes actions performed by the system user PRG_USER.
When triggered, it first calls the gen_pkg.get_user_context procedure to fetch the current session's user, role, city, and transaction details. If no transaction detail ID (v_dtl_id) is available, it generates one using a sequence (dtl_seq.NEXTVAL) and inserts a record into the GEN_DB_TRANSACTION_LOG table, capturing information about the user, role, host machine, and module.
For a delete operation, the trigger inserts the entire old row into GEN_MST_USR_LST_AUD, marking the action as 'D' (delete) along with the new transaction detail ID.
For an update operation, it similarly inserts the old row values into the audit table but marks the action as 'U' (update). This way, the trigger ensures that all previous states of the data are preserved for auditing, enabling traceability of both user activity and data modifications in the system.
Audit Trigger for Tracking User Updates and Deletions in GEN_MST_USR_LST

Implementation of Trigger

create or replace TRIGGER AUD_ULT_AR_UD
AFTER DELETE OR UPDATE ON  GEN_MST_USR_LST
FOR EACH ROW

DECLARE
 v_user_id            gen_db_transaction_log.username%TYPE DEFAULT 'TC01052';
 v_trns_id            gen_db_transaction_log.transaction_id%TYPE;
 v_city               gen_db_transaction_log.city%TYPE;
 v_role               gen_db_transaction_log.user_role%TYPE;
 v_transaction_name   gen_db_transaction_log.transaction_id%TYPE;
 v_dtl_id             gen_db_transaction_log.dtl_id%TYPE;
 v_sessionid          gen_db_transaction_log.transaction_id%TYPE;

BEGIN
IF USER <> 'PRG_USER' THEN
  gen_pkg.get_user_context ( v_user_id, v_role, v_city, v_transaction_name, v_trns_id, v_dtl_id, v_sessionid );

   IF v_dtl_id IS NULL THEN
      SELECT dtl_seq.NEXTVAL into v_dtl_id FROM DUAL;
      INSERT INTO gen_db_transaction_log ( dtl_id, username, user_role, apps_server, transaction_id, business_tans_id, created_date, created_by)
      VALUES ( v_dtl_id, USER, SYS_CONTEXT('USERENV', 'OS_USER'), SUBSTR(SYS_CONTEXT('USERENV', 'HOST'), 1, 50), SYS_CONTEXT('USERENV', 'MODULE'), 555, SYSDATE, SYS_CONTEXT('USERENV', 'OS_USER'));
   END IF;
   IF DELETING THEN
      INSERT INTO GEN_MST_USR_LST_AUD
      (ULT_ID, OWNR_CARR_CODE, USR_NAME, FIRST_NAME, LAST_NAME, USR_TYP, USR_DSGN, SALUTATION, DOB, MAX_SESNS, CRNT_SESNS, LANGUAGE, APLN_ADMIN_IND, SYS_ADMIN_IND, LAST_LOGIN_DATT, LAST_LOGOUT_DATT, GRACE_LOGINS_RMNG, ONLINE_IND, USR_PWD, USR_PWD_CODE, USR_PWD_CODE_VAL, REG_MODE, ACNT_ENABLED, PWD_EXPRY_DT, PWD_EXPRY_ALRT_DT, TRACE_ENABLED, STAFF_ID, DFLT_APLN_CLASSIFIER, MGR_ID, ADRS_ID, VALID_UNTIL, INACTIVE, DTL_ID, CREATED_DATE, CREATED_BY, MODIFIED_DATE, MODIFIED_BY, DESKTOP_ALOWD_IND, USR_ROLE, LOCKED_DATE, INVLD_ATMPTS, OTP, OTP_VALID_UNTIL, USR_ACT_KEY, USR_ACT_KEY_VLDTY_DAT, EMAIL_VERIFIED, USR_BCRPT_PWD, IDP_NUMBER, ACTION, NEW_DTL_ID )
      VALUES
      (:OLD.ULT_ID, :OLD.OWNR_CARR_CODE, :OLD.USR_NAME, :OLD.FIRST_NAME, :OLD.LAST_NAME, :OLD.USR_TYP, :OLD.USR_DSGN, :OLD.SALUTATION, :OLD.DOB, :OLD.MAX_SESNS, :OLD.CRNT_SESNS, :OLD.LANGUAGE, :OLD.APLN_ADMIN_IND, :OLD.SYS_ADMIN_IND, :OLD.LAST_LOGIN_DATT, :OLD.LAST_LOGOUT_DATT, :OLD.GRACE_LOGINS_RMNG, :OLD.ONLINE_IND, :OLD.USR_PWD, :OLD.USR_PWD_CODE, :OLD.USR_PWD_CODE_VAL, :OLD.REG_MODE, :OLD.ACNT_ENABLED, :OLD.PWD_EXPRY_DT, :OLD.PWD_EXPRY_ALRT_DT, :OLD.TRACE_ENABLED, :OLD.STAFF_ID, :OLD.DFLT_APLN_CLASSIFIER, :OLD.MGR_ID, :OLD.ADRS_ID, :OLD.VALID_UNTIL, :OLD.INACTIVE, :OLD.DTL_ID, :OLD.CREATED_DATE, :OLD.CREATED_BY, SYSDATE, v_user_id, :OLD.DESKTOP_ALOWD_IND, :OLD.USR_ROLE, :OLD.LOCKED_DATE, :OLD.INVLD_ATMPTS, :OLD.OTP, :OLD.OTP_VALID_UNTIL, :OLD.USR_ACT_KEY, :OLD.USR_ACT_KEY_VLDTY_DAT, :OLD.EMAIL_VERIFIED, :OLD.USR_BCRPT_PWD, :OLD.IDP_NUMBER, 'D', v_dtl_id
      );
   END IF;

   IF UPDATING THEN
      INSERT INTO GEN_MST_USR_LST_AUD
      (ULT_ID, OWNR_CARR_CODE, USR_NAME, FIRST_NAME, LAST_NAME, USR_TYP, USR_DSGN, SALUTATION, DOB, MAX_SESNS, CRNT_SESNS, LANGUAGE, APLN_ADMIN_IND, SYS_ADMIN_IND, LAST_LOGIN_DATT, LAST_LOGOUT_DATT, GRACE_LOGINS_RMNG, ONLINE_IND, USR_PWD, USR_PWD_CODE, USR_PWD_CODE_VAL, REG_MODE, ACNT_ENABLED, PWD_EXPRY_DT, PWD_EXPRY_ALRT_DT, TRACE_ENABLED, STAFF_ID, DFLT_APLN_CLASSIFIER, MGR_ID, ADRS_ID, VALID_UNTIL, INACTIVE, DTL_ID, CREATED_DATE, CREATED_BY, MODIFIED_DATE, MODIFIED_BY, DESKTOP_ALOWD_IND, USR_ROLE, LOCKED_DATE, INVLD_ATMPTS, OTP, OTP_VALID_UNTIL,  USR_ACT_KEY, USR_ACT_KEY_VLDTY_DAT,EMAIL_VERIFIED, USR_BCRPT_PWD, IDP_NUMBER, ACTION, NEW_DTL_ID )
      VALUES
      (:OLD.ULT_ID, :OLD.OWNR_CARR_CODE, :OLD.USR_NAME, :OLD.FIRST_NAME, :OLD.LAST_NAME, :OLD.USR_TYP, :OLD.USR_DSGN, :OLD.SALUTATION, :OLD.DOB, :OLD.MAX_SESNS, :OLD.CRNT_SESNS, :OLD.LANGUAGE, :OLD.APLN_ADMIN_IND, :OLD.SYS_ADMIN_IND, :OLD.LAST_LOGIN_DATT, :OLD.LAST_LOGOUT_DATT, :OLD.GRACE_LOGINS_RMNG, :OLD.ONLINE_IND, :OLD.USR_PWD, :OLD.USR_PWD_CODE, :OLD.USR_PWD_CODE_VAL, :OLD.REG_MODE, :OLD.ACNT_ENABLED, :OLD.PWD_EXPRY_DT, :OLD.PWD_EXPRY_ALRT_DT, :OLD.TRACE_ENABLED, :OLD.STAFF_ID, :OLD.DFLT_APLN_CLASSIFIER, :OLD.MGR_ID, :OLD.ADRS_ID, :OLD.VALID_UNTIL, :OLD.INACTIVE, :OLD.DTL_ID, :OLD.CREATED_DATE, :OLD.CREATED_BY, :OLD.MODIFIED_DATE, :OLD.MODIFIED_BY, :OLD.DESKTOP_ALOWD_IND, :OLD.USR_ROLE, :OLD.LOCKED_DATE, :OLD.INVLD_ATMPTS, :OLD.OTP, :OLD.OTP_VALID_UNTIL,  :OLD.USR_ACT_KEY, :OLD.USR_ACT_KEY_VLDTY_DAT,:OLD.EMAIL_VERIFIED, :OLD.USR_BCRPT_PWD, :OLD.IDP_NUMBER, 'U', v_dtl_id
      );
   END IF;
END IF;
END;
/

Here's a concise 8-line point-wise explanation:

Trigger fires AFTER DELETE or UPDATE on GEN_MST_USR_LST for each row.
Ignores actions by the system user PRG_USER.
Calls gen_pkg.get_user_context to fetch user, role, city, and transaction details.
If no transaction detail ID exists, generates one using dtl_seq.NEXTVAL and logs it in GEN_DB_TRANSACTION_LOG.
On DELETE, inserts the old row into GEN_MST_USR_LST_AUD with action 'D'.
On UPDATE, inserts the old row into GEN_MST_USR_LST_AUD with action 'U'.
Captures all column values along with timestamps and user info for auditing.
Ensures a complete history of user master changes for traceability and accountability.

mrcaption | PLSQL dev

Pranav Bakare — Sun, 14 Sep 2025 13:30:42 +0000

Software Engineer – Oracle PL/SQL Developer
Accelya Airline Platform | Dec 2024 – Present

Project Context & Business Impact: Pioneered Accelya’s Airline-First Software Platform, trusted by 200+ global carriers—including Emirates, United Airlines, Swiss International Air Lines, Finnair, Virgin Atlantic, SAS, Flydubai, and Aeroméxico—to streamline end-to-end cargo operations and drive revenue growth through modular systems like SkyChain and Offer & Order.

Agile Delivery & Change Management: Handled 25+ Change Requests (CRs) and defect fixes within Agile sprints, coordinating across teams to ensure on-time delivery and zero post-deployment failures across TF, UAT, TRG, and PROD environments.

Security & Compliance (SSO/MFA): Engineered database-level Single Sign-On (SSO) and Multi-Factor Authentication (MFA) using schema objects, triggers, and PL/SQL modules; implemented fine-grained access control (FGAC) with VPD/DBMS_RLS to enforce dynamic row-level security and regulatory compliance.

PL/SQL Development & Modularization: Revamped 40+ parametrized, autonomous, and layered PL/SQL Packages, Procedures, and Functions with overloading, encapsulation, and PRAGMAs, delivering reusable, maintainable, and high-performance database solutions. Implemented Dynamic SQL using EXECUTE IMMEDIATE with bind variables to enable runtime handling of unknown tables and operations.

Performance Optimization & SQL Tuning: Spearheaded query and PL/SQL optimization using ASH, AWR, ADDM, and performance views (V$SESSION, V$SQL, V$LOCK), achieving 30–40% faster execution and resolving session/contention issues for high-volume workloads. Minimized SQL-PL/SQL context switching using BULK COLLECT, FORALL, and Global Temporary Tables (GTTs) for efficient batch processing.

ETL, Reporting & Data Handling: Accelerated data retrieval and reporting with Materialized Views (MViews) and Pipelined Table Functions; designed robust ETL pipelines using SQL*Loader, External Tables, and UTL_FILE for fast bulk loads, flat file processing, and error-free exports.

Automation & Job Scheduling: Orchestrated enterprise-level workflows using DBMS_SCHEDULER and Cron jobs, automating 90% of recurring tasks to reduce manual intervention and ensure consistent execution of critical system processes.

Audit & Trigger Management: Developed Audit, Security, Compound, and Instead-of triggers using PRAGMA AUTONOMOUS_TRANSACTION to ensure tamper-proof DML logging, enforce access control, and resolve mutating table issues, safeguarding transactional integrity.

High-impact PL/SQL resume

Pranav Bakare — Sun, 14 Sep 2025 12:03:03 +0000

Pranav Bakare

Sep 14 '25

Resume Crafting Guide - PLSQL dev

#oracle #data #database #datascience

Comments

4 min read