long

 

set markup html on

set heading off

set linesize 1000

set pagesize 50000

-- Spool the output to an HTML file

spool long_running_requests.html

-- Add a title to the HTML file

SELECT '<h2>Monitoring Long-Running Concurrent Requests</h2>' FROM DUAL;

-- Add the HTML table header

SELECT

    '<table>' ||

    '<tr>' ||

    '<th>Concurrent Program</th>' ||

    '<th>Run Time (Minutes)</th>' ||

    '<th>Request ID</th>' ||

    '<th>Parent ID</th>' ||

    '<th>Request Date</th>' ||

    '<th>Start Date</th>' ||

    '<th>Completion Date</th>' ||

    '<th>End-to-End Mins</th>' ||

    '<th>Lag Time (Seconds)</th>' ||

    '<th>Requested By</th>' ||

    '<th>Phase</th>' ||

    '<th>Status</th>' ||

    '<th>Arguments</th>' ||

    '<th>Priority</th>' ||

    '</tr>' AS HTML_HEADER

FROM DUAL;

-- The actual query to generate the table rows

SELECT

    '<tr>' ||

    '<td>' || c.user_concurrent_program_name || '</td>' ||

    '<td>' || round(((sysdate-a.actual_start_date)*24*60),2) || '</td>' ||

    '<td>' || a.request_id || '</td>' ||

    '<td>' || NVL(TO_CHAR(a.parent_request_id), 'N/A') || '</td>' ||

    '<td>' || TO_CHAR(a.request_date, 'DD-MON-YYYY HH24:MI:SS') || '</td>' ||

    '<td>' || TO_CHAR(a.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') || '</td>' ||

    '<td>' || TO_CHAR(a.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') || '</td>' ||

    '<td>' || round((a.actual_completion_date-a.request_date)*24*60, 2) || '</td>' ||

    '<td>' || round((a.actual_start_date-a.request_date)*24*60*60, 2) || '</td>' ||

    '<td>' || d.user_name || '</td>' ||

    '<td>' || a.phase_code || '</td>' ||

    '<td>' || a.status_code || '</td>' ||

    '<td>' || a.argument_text || '</td>' ||

    '<td>' || a.priority || '</td>' ||

    '</tr>'

FROM

    apps.fnd_concurrent_requests a,

    apps.fnd_concurrent_programs b,

    apps.FND_CONCURRENT_PROGRAMS_TL c,

    apps.fnd_user d

WHERE

    a.concurrent_program_id = b.concurrent_program_id

    AND b.concurrent_program_id = c.concurrent_program_id

    AND a.requested_by = d.user_id

    AND a.status_code = 'R'

ORDER BY

    run_time_minutes DESC;

-- Close the table and stop spooling

SELECT '</table>' FROM DUAL;

spool off

exit

#!/bin/bash

# Database connection string

DB_CONN="username/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=your_host)(PORT=your_port))(CONNECT_DATA=(SERVICE_NAME=your_service_name)))"

# The email recipient and subject

EMAIL_TO="recipient@example.com"

EMAIL_SUBJECT="Monitoring: Long-Running Concurrent Requests Report"

# Run the SQL script to generate the HTML file

sqlplus -s $DB_CONN @long_running_requests.sql

# Send the email with the HTML file as the body

cat long_running_requests.html | mailx -s "$EMAIL_SUBJECT" -a "Content-Type: text/html" "$EMAIL_TO"

# Clean up the generated file

rm long_running_requests.html

echo "Monitoring report sent successfully."