Scribd
Upload
6 views4 pages

SQL Reporting with Shell Scripting Guide

The document outlines a real-time shell scripting example for automating container movement data processing in a logistics project. It details the steps for cleaning, validating, and loading data from a CSV file into an Oracle database, along with error monitoring and alerting. Key tools used include awk, sed, grep, and sqlplus, and the process is encapsulated in a shell script named process_container_status.sh.

Uploaded by

leoleo4592
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views4 pages

SQL Reporting with Shell Scripting Guide

The document outlines a real-time shell scripting example for automating container movement data processing in a logistics project. It details the steps for cleaning, validating, and loading data from a CSV file into an Oracle database, along with error monitoring and alerting. Key tools used include awk, sed, grep, and sqlplus, and the process is encapsulated in a shell script named process_container_status.sh.

Uploaded by

leoleo4592
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Real-time Shell Scripting Example in Logistics Project

Using awk, sed, grep, sqlplus, mail

Real-time Flow: Container Movement Data Automation (End-to-End Example)

Scenario:

Terminals send daily container_status.csv via SFTP.


You need to:
Clean file
Validate data
Remove duplicates
Load into DB
Send report of invalid data
Monitor for missing gate_out_date
Automate with shell script
Use awk, sed, grep, sqlplus, mail

Incoming File Format: container_status.csv

container_number,gate_in_date,gate_out_date,appointment_status
MSKU123456,2025-06-05,2025-06-08,Completed
MSKU234567,2025-06-04,,Pending
APLU345678,2025-06-06,2025-06-07,Completed

High-Level Architecture:

Terminal FTP
|
v
UNIX Shell Script
- Clean CSV
- Validate container
- Deduplicate
- Load to DB
- Monitor Errors
|
v
Oracle DB
PL/SQL Proc
Audit Tables
|
v
Email Alert

Final Shell Script: process_container_status.sh

#!/bin/bash
INPUT_FILE="container_status.csv"
CLEAN_FILE="clean_container_status.csv"
VALID_FILE="valid_container_status.csv"
ERROR_FILE="error_report.log"
LOG_FILE="/app/logs/container_load.log"

echo "Starting Container Status Processing..." > $LOG_FILE

# Step 1: Remove blank lines


sed '/^$/d' $INPUT_FILE > [Link]
echo "Removed blank lines" >> $LOG_FILE

# Step 2: Clean container_number (remove special characters)


awk -F',' 'NR==1 {print $0} NR>1 {gsub(/[^A-Za-z0-9]/, "", $1); print $0}' OFS=','
[Link] > [Link]
echo "Cleaned container numbers" >> $LOG_FILE

# Step 3: Remove duplicate lines


sort [Link] | uniq > $CLEAN_FILE
echo "Removed duplicate records" >> $LOG_FILE

# Step 4: Validate container_number format (4 letters + 6 digits)


head -1 $CLEAN_FILE > $VALID_FILE
grep -E '^[A-Z]{4}[0-9]{6}' $CLEAN_FILE | grep -v '^container_number' >> $VALID_FILE
echo "Validated container_number formats" >> $LOG_FILE

# Step 5: Load data into Oracle DB


sqlplus -s user/pass@DB <<EOF >> $LOG_FILE
SET SERVEROUTPUT ON
BEGIN
load_container_status('valid_container_status.csv');
END;
/
EXIT;
EOF

echo "Data loaded into DB" >> $LOG_FILE

# Step 6: Monitor missing gate_out_date


sqlplus -s user/pass@DB <<EOF > $ERROR_FILE
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT 'Container: ' || container_number || ' missing Gate-Out Date'
FROM container_movements
WHERE gate_out_date IS NULL;
EXIT;
EOF

# Step 7: Send error mail if needed


if [ -s $ERROR_FILE ]; then
mail -s "Alert: Missing Gate-Out Dates" logistics_team@[Link] < $ERROR_FILE
echo "Sent email for missing gate_out_date" >> $LOG_FILE
else
echo "No missing gate_out_date found" >> $LOG_FILE
fi

# Cleanup
rm [Link] [Link]

echo "Container Status Processing Completed." >> $LOG_FILE

PL/SQL Procedure: load_container_status

CREATE OR REPLACE PROCEDURE load_container_status(p_file_name VARCHAR2) AS


v_line VARCHAR2(4000);
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN('DATA_DIR', p_file_name, 'R');

UTL_FILE.GET_LINE(v_file, v_line);

LOOP
BEGIN
UTL_FILE.GET_LINE(v_file, v_line);
INSERT INTO container_movements(container_number, gate_in_date, gate_out_date,
appointment_status)
VALUES (
REGEXP_SUBSTR(v_line, '^[^,]+'),
TO_DATE(REGEXP_SUBSTR(v_line, '[^,]+', 1, 2), 'YYYY-MM-DD'),
CASE WHEN REGEXP_SUBSTR(v_line, '[^,]+', 1, 3) IS NULL THEN NULL
ELSE TO_DATE(REGEXP_SUBSTR(v_line, '[^,]+', 1, 3), 'YYYY-MM-DD')
END,
REGEXP_SUBSTR(v_line, '[^,]+', 1, 4)
);
EXCEPTION
WHEN NO_DATA_FOUND THEN EXIT;
END;
END LOOP;

UTL_FILE.FCLOSE(v_file);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Load complete.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
UTL_FILE.FCLOSE_ALL;
END;
/

Key Points for Interview:

Why shell scripting?


- Automate manual file processing.
- Ensure data integrity before loading to DB.
- Reduce manual errors.
- Alert operations team proactively.

Where did you use awk/sed/grep?


- awk clean container_number column.
- sed remove blank lines.
- grep validate container format, scan logs for ORA errors.
How did you integrate with PL/SQL?
- Used sqlplus to call PL/SQL procedure to load validated data.

How did you monitor errors?


- Used grep to search logs for "ORA-".
- Queried DB for missing gate_out_date, and mailed report.

What you can say in your resume:

- Automated daily terminal data processing pipeline using UNIX shell scripting
integrated with Oracle PL/SQL.
- Used awk/sed/grep for data cleaning and validation; automated error monitoring and
alerting.
- Reduced manual intervention and improved data accuracy for detention and demurrage
calculation.
- Implemented full batch automation (file processing DB load monitoring email
reporting).

Common questions

Powered by AI

The shell script queries the database for containers missing a gate_out_date and subsequently generates an error report if such entries are found. It then checks the size of the error report file, and if it exists, an email alert is generated and sent to the logistics team. This automation ensures that the operations team is promptly notified of potential issues with container tracking, enhancing response times and operational efficiency .

The data flow begins with receiving container_status.csv via SFTP, followed by cleaning and validation steps using UNIX commands (sed, awk, grep) to ensure format consistency. Duplicates are removed, and validated data is loaded into the Oracle DB via a PL/SQL procedure executed using sqlplus. Each step - cleaning, deduplication, validation - contributes by ensuring only accurate and clean data is stored in the database, thereby maintaining data quality and reliability .

Within the automation script, awk is used to clean the container number column by removing special characters, sed is utilized to remove blank lines from the CSV file, and grep is employed to validate container formats and monitor logs for errors .

Error detection is handled by querying the database for missing gate_out_date entries and using grep to search logs for ORA errors. If errors are detected, an email alert is sent to the logistics team to report these issues, which ensures timely awareness and allows corrective actions .

Integrating sqlplus with shell scripts allows for the execution of PL/SQL procedures directly from the command line, facilitating the automated loading of validated data into the Oracle database. This integration streamlines the workflow by linking data validation and correction processes with database operations .

The script automates several steps previously done manually, such as file cleaning, validation, and loading into the database. By minimizing human intervention, it significantly reduces the likelihood of errors, while processes like automated alerting ensure rapid handling of discrepancies. Collectively, these automations streamline operations and enhance the precision and speed of logistics data management .

Deduplication is critical in preventing redundant data from distorting analyses and reports, thereby maintaining data integrity. In the processing pipeline, deduplication is achieved using the sort and uniq commands to identify and eliminate duplicate lines from the container status file .

Data validation is achieved by using grep to ensure the container number format matches the expected pattern (4 letters followed by 6 digits). This step filters out invalid records before further processing.

Shell scripting automates manual file processing, ensuring data integrity before loading to databases. It reduces manual errors and enables proactive alerts to the operations team, which enhances efficiency and reliability in handling data .

The shell script ensures accuracy and efficiency through multiple steps: it removes blank lines using sed, cleans the container number to eliminate special characters using awk, removes duplicate lines with sort and uniq, and validates the container number format with grep . These steps prepare the data for accurate loading into the database.

You might also like