Afternoon all,
So I started a new job and I am working with PLSQL for the first time in many years. I understand basic SQL calls, select statements ETC. I am currently trying to adjust a package which I inherited from the previous admin. At a basic level, when the script sees a well that it doesn't recognize, it just stops. I would like it to just skip over the unknown well, spit out a text exception report, and continue on and finish the script. Can anyone give me some pointers? I think it just needs a minor adjustment... Please see below, my own write up and then the script:
Step-by-Step:
Get Database Instance Name:
Executes SQL to get the database instance name into l_instance.
Get Email Addresses:
A cursor c_admin retrieves email addresses associated with the 'Well Conversion Report'.
Concatenates these email addresses into l_to.
Call well_conversion_check:
Executes well_conversion_check procedure with the previous day's date.
Exception Handling:
Sends an email using ATDB.email.EMAIL_PROBLEM if an error occurs.
well_conversion_check Procedure
This procedure checks well conversion activities and identifies new wells or changes in well status.
Step-by-Step:
Get Database Instance Name:
Executes SQL to get the database instance name into l_instance.
Initialize Cursors:
c_convert_codes: Retrieves well events with specific reason codes after January 1, 2007.
c_new_wells: Retrieves new well events with reason code 350.
c_convert_examine: Examines well events for a specific well ID and date.
Check Conversion Activities:
Loops through each record in c_convert_codes.
Retrieves the activity type for each well event.
Checks if the well status changes from producing to injecting or vice versa and stores warnings or alerts.
Identify New Wells:
Loops through each record in c_new_wells to identify new wells that went online.
Send Email Report:
Calls email_report procedure with alerts, new wells, and warnings.
submit_job Procedure
This procedure schedules the job to run the RUN_JOB procedure.
Step-by-Step:
Check Existing Jobs:
Counts the number of existing jobs with the same l_what.
Submit New Job:
If no existing job is found, submits a new job with DBMS_JOB.submit.
delete_job Procedure
This procedure deletes the scheduled job from the job queue.
Step-by-Step:
Get Job Numbers:
Retrieves job numbers with the same l_what.
Remove Jobs:
Loops through each job number and removes it using DBMS_JOB.REMOVE.
Summary
This package handles:
Scheduling and running a job daily.
Checking well conversion activities and new well events.
Generating reports and sending email notifications if any issues are detected.
Managing the job submission and deletion in the job queue.
*********************
CREATE OR REPLACE PACKAGE BODY ATDB.WELL_CONVERSION_REPORT_JOB
AS
l_what sys.user_jobs.what%type := 'begin ATDB.WELL_CONVERSION_REPORT_JOB.RUN_JOB; end;';
l_when sys.user_jobs.interval%type := 'TRUNC(SYSDATE+1) + 13/48';
procedure RUN_JOB
is
l_sql varchar2(64);
l_instance varchar2(64);
l_to VARCHAR2 (32767);
l_semicolon CHAR(1);
CURSOR c_admin
IS
select mail_address from atdb.mail_report_xref a, atdb.mail_addresses b
where report_name='Well Conversion Report'
and a.USER_NAME = b.USER_NAME;
begin
l_sql := 'select sys_context(''userenv'',''db_name'') from dual';
EXECUTE IMMEDIATE l_sql
INTO l_instance;
--Get the list of email addresses
FOR eachrec IN c_admin
LOOP
l_to := l_to || l_semicolon || eachrec.MAIL_ADDRESS;
l_semicolon := ';';
END LOOP;
well_conversion_check(TO_CHAR(sysdate - 1, 'DD-MON-YY'));
EXCEPTION
WHEN OTHERS
THEN
ATDB.email.EMAIL_PROBLEM('Well Conversion Report',l_to,'ATDB_' || l_instance || '@conocophillips.com','ERROR: Well Conversion Report');
end;
Procedure well_conversion_check(p_date date)
is
activity atdb.well_daily_observation.activity_type%TYPE;
new_activity atdb.well_daily_observation.activity_type%TYPE;
l_flag char(1) := 'F';
l_si_hrs number;
l_sys_date date := p_date;
warnings HASH_TABLE;
alerts HASH_TABLE;
new_wells HASH_TABLE;
l_sql varchar2(64);
l_instance varchar2(64);
CURSOR c_convert_codes
IS
SELECT well_id, datetime_event, event_id, material_cd
FROM atdb.well_event
WHERE reason_code_id IN
('210', '211', '212', '213', '214', '215', '216', '217','354','355','356','393','394','395','396','400')
AND datetime_event > '01-JAN-07';
CURSOR c_new_wells
IS
SELECT well_id, count_total
FROM
(SELECT COUNT (well_id) AS count_total,well_id
FROM atdb.well_event
WHERE reason_code_id = 350 and TO_CHAR (datetime_event, 'DD-MON-YY') <= l_sys_date
AND well_id IN (
SELECT well_id
FROM atdb.well_event
WHERE reason_code_id IN ('350')
AND datetime_event >= l_sys_date
AND datetime_event < TO_CHAR (l_sys_date + 1, 'DD-MON-YY')) group by well_id) where count_total = 1;
CURSOR c_convert_examine (
l_well_id atdb.well_event.well_id%TYPE,
l_convert_date DATE
)
IS
select well_id, datetime_event,well_status, material_cd from atdb.well_event where well_id= l_well_id and datetime_event > l_convert_date;
BEGIN
l_sql := 'select sys_context(''userenv'',''db_name'') from dual';
EXECUTE IMMEDIATE l_sql
INTO l_instance;
FOR eachrec IN c_convert_codes
LOOP
SELECT activity_type
INTO activity
FROM atdb.well_daily_observation a, atdb.oper_set b
WHERE a.oper_set_id = b.oper_set_id
AND b.well_id = eachrec.well_id
AND date_obs =
TO_CHAR (eachrec.datetime_event - 1, 'DD-MON-YY');
l_flag := 'F';
new_activity := eachrec.material_cd;
FOR each_event IN c_convert_examine (eachrec.well_id,
eachrec.datetime_event
)
LOOP
IF each_event.well_status = 'ON'
THEN
if TO_CHAR (each_event.datetime_event, 'DD-MON-YY') <> l_sys_date then
exit;
end if;
new_activity := each_event.material_cd;
--Find well that have gone from producers to injectors
IF activity = 'OIL PRODUCING'
OR activity = 'GAS LIFT OIL'
OR activity = 'ESP OIL'
OR activity = 'WATER JET LIFT OIL'
OR activity = 'GAS PRODUCING'
THEN
IF new_activity <> 'OIL PRODUCING'
AND new_activity <> 'GAS LIFT OIL'
AND new_activity <> 'ESP OIL'
AND new_activity <> 'WATER JET LIFT OIL'
AND new_activity <> 'GAS PRODUCING'
THEN
warnings(each_event.well_id || eachrec.datetime_event) := each_event.well_id || ' was converted from ' || activity || ' to ' || new_activity
|| ' on '
|| eachrec.datetime_event
|| ' and opened on '
|| each_event.datetime_event;
DBMS_OUTPUT.put_line ( 'WARNING: ' || each_event.well_id || ' was converted from ' || activity || ' to ' || new_activity
|| ' on '
|| eachrec.datetime_event
|| ' and opened on '
|| each_event.datetime_event
);
END IF;
end if;
--Find well that have gone from injectors to producers
if new_activity = 'OIL PRODUCING'
OR new_activity = 'GAS LIFT OIL'
OR new_activity = 'ESP OIL'
OR new_activity = 'WATER JET LIFT OIL'
OR new_activity = 'GAS PRODUCING'
THEN
IF activity <> 'OIL PRODUCING'
AND activity <> 'GAS LIFT OIL'
AND activity <> 'ESP OIL'
AND activity <> 'WATER JET LIFT OIL'
AND activity <> 'GAS PRODUCING'
THEN
warnings(each_event.well_id || eachrec.datetime_event) := each_event.well_id || ' was converted from ' || activity || ' to ' || new_activity
|| ' on '
|| eachrec.datetime_event
|| ' and opened on '
|| each_event.datetime_event;
DBMS_OUTPUT.put_line ( 'WARNING: ' || each_event.well_id || ' was converted from ' || activity || ' to ' || new_activity
|| ' on '
|| eachrec.datetime_event
|| ' and opened on '
|| each_event.datetime_event
);
END IF;
end if;
l_flag := 'T';
EXIT;
END IF;
END LOOP;
if l_flag = 'F' and TO_CHAR (eachrec.datetime_event, 'DD-MON-YY') = l_sys_date then
alerts(eachrec.well_id || eachrec.datetime_event) := eachrec.well_id || ' was converted from ' || activity || ' to ' || new_activity
|| ' on '
|| eachrec.datetime_event;
DBMS_OUTPUT.put_line ( 'Alert: ' || eachrec.well_id || ' was converted from ' || activity || ' to ' || new_activity
|| ' on '
|| eachrec.datetime_event
);
end if;
END LOOP;
FOR each_open_event IN c_new_wells
loop
new_wells(each_open_event.well_id) := 'WARNING: A new well, ' || each_open_event.well_id || ', went online on ' || l_sys_date;
DBMS_OUTPUT.put_line ( 'WARNING: A new well, ' || each_open_event.well_id || ', went online on ' || l_sys_date );
end loop;
email_report(alerts, new_wells, warnings);
END;
/************************************************************************
submit_job
Purpose:
Submits the DBMS job if the job has not been submitted yet.
************************************************************************/
PROCEDURE submit_job
is
l_cnt NUMBER;
jobno NUMBER;
BEGIN
SELECT COUNT (*)
INTO l_cnt
FROM user_jobs
WHERE what = l_what;
IF l_cnt = 0
THEN
DBMS_JOB.submit
(jobno,
l_what,
SYSDATE,
l_when
);
COMMIT;
END IF;
END;
/************************************************************************
delete_job
Purpose:
Deletes the DBMS job from the que.
************************************************************************/
PROCEDURE delete_job
is
CURSOR c_job_numbers
IS
SELECT Job
FROM user_jobs
WHERE what = l_what;
begin
FOR eachrec IN c_job_numbers
LOOP
DBMS_JOB.REMOVE(eachrec.Job);
END LOOP;
COMMIT;
end;
FUNCTION mytime (p_date DATE)
RETURN VARCHAR2
IS
BEGIN
RETURN TO_CHAR (p_date, 'YYYY.MM.DD HH24:MI:SS');
END;
PROCEDURE email_write_mime_header (
p_conn IN OUT NOCOPY UTL_SMTP.connection
, p_mime_type IN VARCHAR2
, p_value IN VARCHAR2
)
IS
BEGIN
UTL_SMTP.write_data (p_conn
, p_mime_type || ': ' || p_value || UTL_TCP.crlf
);
END email_write_mime_header;
/* ***********************************************************************
email_write_line
Purpose:
Write a line of text to the email connection
Arguments:
p_conn - email connection
p_line - text to write
*********************************************************************** */
PROCEDURE email_write_line (
p_conn IN OUT NOCOPY UTL_SMTP.connection
, p_line IN VARCHAR2
)
IS
BEGIN
UTL_SMTP.write_data (p_conn, p_line || UTL_TCP.crlf);
END email_write_line;
/* ***********************************************************************
email_report
Purpose:
Email a problem that has occurred
Arguments:
/*********************************************************************** */
PROCEDURE email_report (
p_alert_list HASH_TABLE
, p_new_well_list HASH_TABLE
, p_warning_list HASH_TABLE
)
IS
l_mailhost VARCHAR2 (64) := atdb.email.get_mailhost_smtp;
l_from VARCHAR2 (64);
l_to VARCHAR2 (32767);
l_mail_conn UTL_SMTP.connection;
l_subject VARCHAR2 (64) := 'Conversion and New Well Report';
l_semicolon CHAR (1) := NULL;
l_instance VARCHAR (64);
l_sql VARCHAR (64);
l_message VARCHAR (300);
CURSOR c_admin
IS
select mail_address from atdb.mail_addresses a, atdb.mail_report_xref b where a.user_name = b.user_name and b.report_name ='Well Conversion Report';
BEGIN
l_sql := 'select sys_context(''userenv'',''db_name'') from dual';
EXECUTE IMMEDIATE l_sql
INTO l_instance;
l_from := 'ATDB_' || l_instance || '@conocophillips.com';
l_mail_conn := UTL_SMTP.open_connection (l_mailhost, 25);
UTL_SMTP.helo (l_mail_conn, l_mailhost);
UTL_SMTP.mail (l_mail_conn, l_from);
--UTL_SMTP.rcpt (l_mail_conn, l_to);
FOR eachrec IN c_admin
LOOP
UTL_SMTP.rcpt (l_mail_conn, eachrec.mail_address);
l_to := l_to || l_semicolon || eachrec.mail_address;
l_semicolon := ';';
END LOOP;
UTL_SMTP.open_data (l_mail_conn);
email_write_mime_header (l_mail_conn, 'From', l_from);
email_write_mime_header (l_mail_conn, 'To', l_to);
email_write_mime_header (l_mail_conn, 'Subject', l_subject);
-- Send an empty line to denotes end of MIME headers and
-- beginning of message body.
UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf);
email_write_line (l_mail_conn
, 'Conversion and New Well Report for ' || mytime (SYSDATE)
);
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, 'Conversion Warnings:');
l_message := p_warning_list.first;
if l_message is null then
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, 'NONE');
end if;
loop
exit when l_message is null;
email_write_line (l_mail_conn, p_warning_list(l_message));
l_message := p_warning_list.next(l_message);
end loop;
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, 'New Well Warnings:');
l_message := p_new_well_list.first;
if l_message is null then
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, 'NONE');
end if;
loop
exit when l_message is null;
email_write_line (l_mail_conn, p_new_well_list(l_message));
l_message := p_new_well_list.next(l_message);
end loop;
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, 'Alerts:');
l_message := p_alert_list .first;
if l_message is null then
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, 'NONE');
end if;
loop
exit when l_message is null;
email_write_line (l_mail_conn, p_alert_list(l_message));
l_message := p_alert_list .next(l_message);
end loop;
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn
, 'This job is scheduled and run by the ' || l_instance ||' Oracle instance.'
);
UTL_SMTP.close_data (l_mail_conn);
UTL_SMTP.quit (l_mail_conn);
END email_report;
end;
So I started a new job and I am working with PLSQL for the first time in many years. I understand basic SQL calls, select statements ETC. I am currently trying to adjust a package which I inherited from the previous admin. At a basic level, when the script sees a well that it doesn't recognize, it just stops. I would like it to just skip over the unknown well, spit out a text exception report, and continue on and finish the script. Can anyone give me some pointers? I think it just needs a minor adjustment... Please see below, my own write up and then the script:
Step-by-Step:
Get Database Instance Name:
Executes SQL to get the database instance name into l_instance.
Get Email Addresses:
A cursor c_admin retrieves email addresses associated with the 'Well Conversion Report'.
Concatenates these email addresses into l_to.
Call well_conversion_check:
Executes well_conversion_check procedure with the previous day's date.
Exception Handling:
Sends an email using ATDB.email.EMAIL_PROBLEM if an error occurs.
well_conversion_check Procedure
This procedure checks well conversion activities and identifies new wells or changes in well status.
Step-by-Step:
Get Database Instance Name:
Executes SQL to get the database instance name into l_instance.
Initialize Cursors:
c_convert_codes: Retrieves well events with specific reason codes after January 1, 2007.
c_new_wells: Retrieves new well events with reason code 350.
c_convert_examine: Examines well events for a specific well ID and date.
Check Conversion Activities:
Loops through each record in c_convert_codes.
Retrieves the activity type for each well event.
Checks if the well status changes from producing to injecting or vice versa and stores warnings or alerts.
Identify New Wells:
Loops through each record in c_new_wells to identify new wells that went online.
Send Email Report:
Calls email_report procedure with alerts, new wells, and warnings.
submit_job Procedure
This procedure schedules the job to run the RUN_JOB procedure.
Step-by-Step:
Check Existing Jobs:
Counts the number of existing jobs with the same l_what.
Submit New Job:
If no existing job is found, submits a new job with DBMS_JOB.submit.
delete_job Procedure
This procedure deletes the scheduled job from the job queue.
Step-by-Step:
Get Job Numbers:
Retrieves job numbers with the same l_what.
Remove Jobs:
Loops through each job number and removes it using DBMS_JOB.REMOVE.
Summary
This package handles:
Scheduling and running a job daily.
Checking well conversion activities and new well events.
Generating reports and sending email notifications if any issues are detected.
Managing the job submission and deletion in the job queue.
*********************
CREATE OR REPLACE PACKAGE BODY ATDB.WELL_CONVERSION_REPORT_JOB
AS
l_what sys.user_jobs.what%type := 'begin ATDB.WELL_CONVERSION_REPORT_JOB.RUN_JOB; end;';
l_when sys.user_jobs.interval%type := 'TRUNC(SYSDATE+1) + 13/48';
procedure RUN_JOB
is
l_sql varchar2(64);
l_instance varchar2(64);
l_to VARCHAR2 (32767);
l_semicolon CHAR(1);
CURSOR c_admin
IS
select mail_address from atdb.mail_report_xref a, atdb.mail_addresses b
where report_name='Well Conversion Report'
and a.USER_NAME = b.USER_NAME;
begin
l_sql := 'select sys_context(''userenv'',''db_name'') from dual';
EXECUTE IMMEDIATE l_sql
INTO l_instance;
--Get the list of email addresses
FOR eachrec IN c_admin
LOOP
l_to := l_to || l_semicolon || eachrec.MAIL_ADDRESS;
l_semicolon := ';';
END LOOP;
well_conversion_check(TO_CHAR(sysdate - 1, 'DD-MON-YY'));
EXCEPTION
WHEN OTHERS
THEN
ATDB.email.EMAIL_PROBLEM('Well Conversion Report',l_to,'ATDB_' || l_instance || '@conocophillips.com','ERROR: Well Conversion Report');
end;
Procedure well_conversion_check(p_date date)
is
activity atdb.well_daily_observation.activity_type%TYPE;
new_activity atdb.well_daily_observation.activity_type%TYPE;
l_flag char(1) := 'F';
l_si_hrs number;
l_sys_date date := p_date;
warnings HASH_TABLE;
alerts HASH_TABLE;
new_wells HASH_TABLE;
l_sql varchar2(64);
l_instance varchar2(64);
CURSOR c_convert_codes
IS
SELECT well_id, datetime_event, event_id, material_cd
FROM atdb.well_event
WHERE reason_code_id IN
('210', '211', '212', '213', '214', '215', '216', '217','354','355','356','393','394','395','396','400')
AND datetime_event > '01-JAN-07';
CURSOR c_new_wells
IS
SELECT well_id, count_total
FROM
(SELECT COUNT (well_id) AS count_total,well_id
FROM atdb.well_event
WHERE reason_code_id = 350 and TO_CHAR (datetime_event, 'DD-MON-YY') <= l_sys_date
AND well_id IN (
SELECT well_id
FROM atdb.well_event
WHERE reason_code_id IN ('350')
AND datetime_event >= l_sys_date
AND datetime_event < TO_CHAR (l_sys_date + 1, 'DD-MON-YY')) group by well_id) where count_total = 1;
CURSOR c_convert_examine (
l_well_id atdb.well_event.well_id%TYPE,
l_convert_date DATE
)
IS
select well_id, datetime_event,well_status, material_cd from atdb.well_event where well_id= l_well_id and datetime_event > l_convert_date;
BEGIN
l_sql := 'select sys_context(''userenv'',''db_name'') from dual';
EXECUTE IMMEDIATE l_sql
INTO l_instance;
FOR eachrec IN c_convert_codes
LOOP
SELECT activity_type
INTO activity
FROM atdb.well_daily_observation a, atdb.oper_set b
WHERE a.oper_set_id = b.oper_set_id
AND b.well_id = eachrec.well_id
AND date_obs =
TO_CHAR (eachrec.datetime_event - 1, 'DD-MON-YY');
l_flag := 'F';
new_activity := eachrec.material_cd;
FOR each_event IN c_convert_examine (eachrec.well_id,
eachrec.datetime_event
)
LOOP
IF each_event.well_status = 'ON'
THEN
if TO_CHAR (each_event.datetime_event, 'DD-MON-YY') <> l_sys_date then
exit;
end if;
new_activity := each_event.material_cd;
--Find well that have gone from producers to injectors
IF activity = 'OIL PRODUCING'
OR activity = 'GAS LIFT OIL'
OR activity = 'ESP OIL'
OR activity = 'WATER JET LIFT OIL'
OR activity = 'GAS PRODUCING'
THEN
IF new_activity <> 'OIL PRODUCING'
AND new_activity <> 'GAS LIFT OIL'
AND new_activity <> 'ESP OIL'
AND new_activity <> 'WATER JET LIFT OIL'
AND new_activity <> 'GAS PRODUCING'
THEN
warnings(each_event.well_id || eachrec.datetime_event) := each_event.well_id || ' was converted from ' || activity || ' to ' || new_activity
|| ' on '
|| eachrec.datetime_event
|| ' and opened on '
|| each_event.datetime_event;
DBMS_OUTPUT.put_line ( 'WARNING: ' || each_event.well_id || ' was converted from ' || activity || ' to ' || new_activity
|| ' on '
|| eachrec.datetime_event
|| ' and opened on '
|| each_event.datetime_event
);
END IF;
end if;
--Find well that have gone from injectors to producers
if new_activity = 'OIL PRODUCING'
OR new_activity = 'GAS LIFT OIL'
OR new_activity = 'ESP OIL'
OR new_activity = 'WATER JET LIFT OIL'
OR new_activity = 'GAS PRODUCING'
THEN
IF activity <> 'OIL PRODUCING'
AND activity <> 'GAS LIFT OIL'
AND activity <> 'ESP OIL'
AND activity <> 'WATER JET LIFT OIL'
AND activity <> 'GAS PRODUCING'
THEN
warnings(each_event.well_id || eachrec.datetime_event) := each_event.well_id || ' was converted from ' || activity || ' to ' || new_activity
|| ' on '
|| eachrec.datetime_event
|| ' and opened on '
|| each_event.datetime_event;
DBMS_OUTPUT.put_line ( 'WARNING: ' || each_event.well_id || ' was converted from ' || activity || ' to ' || new_activity
|| ' on '
|| eachrec.datetime_event
|| ' and opened on '
|| each_event.datetime_event
);
END IF;
end if;
l_flag := 'T';
EXIT;
END IF;
END LOOP;
if l_flag = 'F' and TO_CHAR (eachrec.datetime_event, 'DD-MON-YY') = l_sys_date then
alerts(eachrec.well_id || eachrec.datetime_event) := eachrec.well_id || ' was converted from ' || activity || ' to ' || new_activity
|| ' on '
|| eachrec.datetime_event;
DBMS_OUTPUT.put_line ( 'Alert: ' || eachrec.well_id || ' was converted from ' || activity || ' to ' || new_activity
|| ' on '
|| eachrec.datetime_event
);
end if;
END LOOP;
FOR each_open_event IN c_new_wells
loop
new_wells(each_open_event.well_id) := 'WARNING: A new well, ' || each_open_event.well_id || ', went online on ' || l_sys_date;
DBMS_OUTPUT.put_line ( 'WARNING: A new well, ' || each_open_event.well_id || ', went online on ' || l_sys_date );
end loop;
email_report(alerts, new_wells, warnings);
END;
/************************************************************************
submit_job
Purpose:
Submits the DBMS job if the job has not been submitted yet.
************************************************************************/
PROCEDURE submit_job
is
l_cnt NUMBER;
jobno NUMBER;
BEGIN
SELECT COUNT (*)
INTO l_cnt
FROM user_jobs
WHERE what = l_what;
IF l_cnt = 0
THEN
DBMS_JOB.submit
(jobno,
l_what,
SYSDATE,
l_when
);
COMMIT;
END IF;
END;
/************************************************************************
delete_job
Purpose:
Deletes the DBMS job from the que.
************************************************************************/
PROCEDURE delete_job
is
CURSOR c_job_numbers
IS
SELECT Job
FROM user_jobs
WHERE what = l_what;
begin
FOR eachrec IN c_job_numbers
LOOP
DBMS_JOB.REMOVE(eachrec.Job);
END LOOP;
COMMIT;
end;
FUNCTION mytime (p_date DATE)
RETURN VARCHAR2
IS
BEGIN
RETURN TO_CHAR (p_date, 'YYYY.MM.DD HH24:MI:SS');
END;
PROCEDURE email_write_mime_header (
p_conn IN OUT NOCOPY UTL_SMTP.connection
, p_mime_type IN VARCHAR2
, p_value IN VARCHAR2
)
IS
BEGIN
UTL_SMTP.write_data (p_conn
, p_mime_type || ': ' || p_value || UTL_TCP.crlf
);
END email_write_mime_header;
/* ***********************************************************************
email_write_line
Purpose:
Write a line of text to the email connection
Arguments:
p_conn - email connection
p_line - text to write
*********************************************************************** */
PROCEDURE email_write_line (
p_conn IN OUT NOCOPY UTL_SMTP.connection
, p_line IN VARCHAR2
)
IS
BEGIN
UTL_SMTP.write_data (p_conn, p_line || UTL_TCP.crlf);
END email_write_line;
/* ***********************************************************************
email_report
Purpose:
Email a problem that has occurred
Arguments:
/*********************************************************************** */
PROCEDURE email_report (
p_alert_list HASH_TABLE
, p_new_well_list HASH_TABLE
, p_warning_list HASH_TABLE
)
IS
l_mailhost VARCHAR2 (64) := atdb.email.get_mailhost_smtp;
l_from VARCHAR2 (64);
l_to VARCHAR2 (32767);
l_mail_conn UTL_SMTP.connection;
l_subject VARCHAR2 (64) := 'Conversion and New Well Report';
l_semicolon CHAR (1) := NULL;
l_instance VARCHAR (64);
l_sql VARCHAR (64);
l_message VARCHAR (300);
CURSOR c_admin
IS
select mail_address from atdb.mail_addresses a, atdb.mail_report_xref b where a.user_name = b.user_name and b.report_name ='Well Conversion Report';
BEGIN
l_sql := 'select sys_context(''userenv'',''db_name'') from dual';
EXECUTE IMMEDIATE l_sql
INTO l_instance;
l_from := 'ATDB_' || l_instance || '@conocophillips.com';
l_mail_conn := UTL_SMTP.open_connection (l_mailhost, 25);
UTL_SMTP.helo (l_mail_conn, l_mailhost);
UTL_SMTP.mail (l_mail_conn, l_from);
--UTL_SMTP.rcpt (l_mail_conn, l_to);
FOR eachrec IN c_admin
LOOP
UTL_SMTP.rcpt (l_mail_conn, eachrec.mail_address);
l_to := l_to || l_semicolon || eachrec.mail_address;
l_semicolon := ';';
END LOOP;
UTL_SMTP.open_data (l_mail_conn);
email_write_mime_header (l_mail_conn, 'From', l_from);
email_write_mime_header (l_mail_conn, 'To', l_to);
email_write_mime_header (l_mail_conn, 'Subject', l_subject);
-- Send an empty line to denotes end of MIME headers and
-- beginning of message body.
UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf);
email_write_line (l_mail_conn
, 'Conversion and New Well Report for ' || mytime (SYSDATE)
);
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, 'Conversion Warnings:');
l_message := p_warning_list.first;
if l_message is null then
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, 'NONE');
end if;
loop
exit when l_message is null;
email_write_line (l_mail_conn, p_warning_list(l_message));
l_message := p_warning_list.next(l_message);
end loop;
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, 'New Well Warnings:');
l_message := p_new_well_list.first;
if l_message is null then
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, 'NONE');
end if;
loop
exit when l_message is null;
email_write_line (l_mail_conn, p_new_well_list(l_message));
l_message := p_new_well_list.next(l_message);
end loop;
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, 'Alerts:');
l_message := p_alert_list .first;
if l_message is null then
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, 'NONE');
end if;
loop
exit when l_message is null;
email_write_line (l_mail_conn, p_alert_list(l_message));
l_message := p_alert_list .next(l_message);
end loop;
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn, NULL);
email_write_line (l_mail_conn
, 'This job is scheduled and run by the ' || l_instance ||' Oracle instance.'
);
UTL_SMTP.close_data (l_mail_conn);
UTL_SMTP.quit (l_mail_conn);
END email_report;
end;