DEV Community: Pranav Bakare

[Boost]

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

UWABOR KING COLLINS

Sep 30 '24

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

#devops #cloudcomputing #terraform #tutorial

Comments

20 min read

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');

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.

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

Resume Crafting Guide - PLSQL dev

Pranav Bakare — Sun, 14 Sep 2025 11:59:27 +0000

Resume Crafting Guide

High-Impact Resume Framework for PL/SQL Professionals -

Begin with project context and business impact, then highlight Agile delivery, Change Requests, and defect fixes. Emphasize key achievements in security (SSO, MFA, FGAC) and core PL/SQL development with modular, high-performance solutions. Showcase performance tuning, batch processing, ETL, reporting, and automation using Oracle tools and DBMS_SCHEDULER/Cron. Conclude with deployment across environments, ensuring security, compliance, and maintainability for a robust, scalable platform.

1. Project Context / High-Level Impact

Pioneered Accelya's Airline-First Software Platform, trusted by 200+ global carriers - including Emirates (EK), United Airlines (UA), Swiss International Air Lines (LX), Finnair (AY), Virgin Atlantic (VS), Scandinavian Airlines (SAS), Flydubai (FZ), and Aeroméxico (AM) - to streamline end-to-end cargo operations and drive revenue growth through modular systems like SkyChain and Offer & Order.

2. Agile & Change Management / Key Delivery Achievements

Drove defect remediation and product enhancements by actively engaging in sprint planning and cross-team coordination, successfully handling 25+ Change Requests (CRs) and defect fixes to ensure on-time delivery within Agile SDLC frameworks.
Worked across structured environments - TF (MFA, SSO), TF2 (defects & change requests), UAT, TRG, and PROD - to segregate tasks, validate changes, and ensure smooth, risk-free deployments, contributing to stable, high-quality code delivery and zero post-deployment failures for over a year.
Rationale: This emphasizes your ability to deliver reliably in Agile environments and handle complex change requests, while integrating with structured deployment processes.

3. Security & Authentication (SSO/MFA)

Engineered database-level integration for Single Sign-On (SSO) and Multi-Factor Authentication (MFA) using schema objects, event-driven triggers, and PL/SQL modules, enabling secure session orchestration, token management, and seamless enterprise-grade authentication across applications.
Conceived FGAC (fine-grained access control) with Oracle VPD and DBMS_RLS, enforcing dynamic row-level security based on user roles and session context, ensuring fortified data protection and compliance.
Propelled Audit trigger development using PRAGMA AUTONOMOUS_TRANSACTION to ensure tamper-proof DML logging and full traceability, safeguarding critical transactional data.
Rationale: Frontloads the most visible achievement in terms of enterprise security and compliance.

4. PL/SQL Development & Modularization

Revamped and mobilized 40+ parametrized, autonomous, and layered PL/SQL Packages, Procedures, and Functions with overloading and encapsulation, delivering modular, reusable, and maintainable database solutions.
Formulated the orchestration of Oracle PL/SQL PRAGMAs (Autonomous Transaction, Exception Init, Serially Reusable, Inline), ensuring real-time logging with zero rollback impact, faster debugging through custom error codes, reduced PGA memory consumption, and 5–10% faster execution of compute-heavy modules.
Devised Dynamic SQL using EXECUTE IMMEDIATE with bind variables in PL/SQL to create reusable, secure, and scalable procedures, enabling runtime handling of tables and operations unknown at compile time.

5. Performance Optimization & SQL Tuning

Spearheaded PL/SQL performance optimization initiatives by leveraging Oracle Performance Reports - ASH for real-time session monitoring, AWR for historical workload analysis, and ADDM for actionable recommendations - achieving 30–40% faster query execution and significantly improving system efficiency.
Capitalized on Oracle Performance Views (V$SESSION, V$SQL, V$LOCK) to analyze and optimize PL/SQL code, tune SQL queries, and resolve session/contention issues, reducing execution time and enhancing application throughput.
Fortified process integrity and end-to-end observability for high-volume batch jobs processing millions of transactions by leveraging BULK COLLECT, FORALL, and Global Temporary Tables (GTTs), ensuring efficient session-level processing.

6. Reporting, Data Handling & ETL

Accelerated data retrieval and reporting by implementing Materialized Views (MViews) to cache precomputed results and Pipelined Table Functions for efficient sequential processing, ensuring reliable and scalable reporting workflows.
Forged robust ETL data import/export pipelines leveraging SQL*Loader (direct-path) for fast bulk data loads, External Tables for direct querying of flat files, and UTL_FILE for reliable file handling and error logging - automating exports and improving data accuracy.

7. Job Scheduling & Automation

Curated seamless workflows by designing and orchestrating enterprise-level job scheduling with DBMS_SCHEDULER and Cron jobs, automating 90% of recurring system tasks and reducing manual intervention significantly.

High-Impact Resume Framework for PL/SQL Professionals -
When crafting a high-impact PL/SQL resume section, start with the project context and business impact to immediately set the scale and significance of your work. Next, highlight Agile delivery and change management, including handling of Change Requests (CRs) and defect fixes, to showcase your reliability in team-based environments. Follow this with key achievements in security and authentication, such as SSO and MFA implementations, along with fine-grained access control, emphasizing enterprise-level compliance and data protection. Then, detail core PL/SQL development work, including modularization, Packages, Procedures, Functions, PRAGMAs, and Dynamic SQL, to show technical depth and maintainability. After that, focus on performance optimization and SQL tuning, leveraging tools like ASH, AWR, ADDM, and performance views, demonstrating measurable improvements. Include batch processing and high-volume transaction handling using BULK COLLECT, FORALL, and GTTs for efficient data workflows. Next, cover reporting, ETL, and data handling, such as Materialized Views, Pipelined Table Functions, SQL*Loader, External Tables, and UTL_FILE. Highlight job scheduling and automation with DBMS_SCHEDULER and Cron jobs, showing operational efficiency. Ensure that deployment and environment management are mentioned to indicate risk-free delivery across UAT, TRG, and PROD. Finally, wrap up by emphasizing security, compliance, and maintainability, tying all contributions together to demonstrate a robust, high-quality, and scalable PL/SQL platform.

✅ Summary of Rearrangement Logic

Keep project context first to set the scale and impact.
Highlight Agile, CRs, and defect handling immediately after to show delivery reliability.
Put SSO/MFA & security next, as it's a major achievement.
Move into core PL/SQL development, performance optimization, and data handling - technical depth comes after business impact.
End with automation & job scheduling, showing operational efficiency.

Oracle Performance Views

Pranav Bakare — Sat, 13 Sep 2025 16:59:42 +0000

In Oracle, v$session, v$sql, and v$lock are key performance views used to monitor and troubleshoot the database. v$session represents all active sessions, similar to chefs in a kitchen, showing who is working and what they are waiting for. v$sql captures SQL statements being executed, like recipe cards, detailing the work each session is performing and how resource-intensive it is. v$lock monitors locks and waits, analogous to chefs waiting for shared resources like ovens or blenders. When troubleshooting, you first check v$session to see active workloads and session states. Next, you examine v$sql to identify heavy or inefficient queries affecting performance. Only then, if there are waiting sessions, you look at v$lock to find resource contention. This generalized flow—v$session → v$sql → v$lock—applies to almost any performance scenario. It ensures you understand who is active, what they are doing, and whether any are blocked. By following this order, you can quickly isolate performance issues. This approach works regardless of whether the problem is slow queries or blocked sessions.

Oracle Performance Views → Real-World Analogy

Imagine a restaurant kitchen running multiple orders simultaneously. The kitchen represents your Oracle database.

1️⃣ v$session → Who is working?

Think of v$session as the list of chefs in the kitchen.

It tells you which chef is active, what they are currently doing, and if they are waiting for something.

Example:

Chef A is chopping vegetables.

Chef B is waiting for the oven to be free.

Chef C is stirring a sauce.

SQL analogy:
Y
SELECT sid, username, status, sql_id, event
FROM v$session
WHERE status='ACTIVE';

Here, sid = chef ID, event = what the chef is waiting for.

2️⃣ v$sql → What task are they performing?

v$sql is like the recipe cards each chef is following.

It tells you how long a task takes, how many times it’s been executed, and which ones are heavy.

Example:

Chef A’s recipe: 10-minute salad → fast and light.

Chef B’s recipe: Baking a cake for 1 hour → resource-intensive.

Chef C’s recipe: Soup for 15 minutes → moderate.

SQL analogy:

SELECT sql_id, sql_text, executions, elapsed_time
FROM v$sql
WHERE sql_id IN (SELECT sql_id FROM v$session WHERE status='ACTIVE');

This tells you which queries (recipes) are consuming most resources.

3️⃣ v$lock → Are they waiting on someone else?

v$lock is like checking if any chef is waiting because another chef is using the same oven or blender.

Locks happen when two tasks need the same resource simultaneously.

Example:

Chef B cannot bake because Chef D is using the oven (lock).

Chef C can stir soup because they don’t need the oven (no lock).

SQL analogy:

SELECT s.sid, l.type, l.id1, l.id2, l.lmode, l.request
FROM v$lock l, v$session s
WHERE l.sid = s.sid;

Shows who is holding a resource and who is waiting.

4️⃣ Full General Flow in the Analogy

Check which chefs are active (v$session) → see workload and waits.
Check what each chef is doing (v$sql) → identify resource-heavy tasks.
Check if any chef is blocked (v$lock) → resolve contention.

By following this generalized approach, you always know:

Who is active → v$session

What’s heavy → v$sql

Who is blocked → v$lock