We are using the TOAD utility. I am creating and testing procedure under my schema first before they get stored under the database schema (ASIDBA) my schema (JOYCEBA)
Here is my procedure code:
CREATE OR REPLACE PROCEDURE JOYCEBA.APRP100_EMAIL_REMINDER
IS
v_filehandle_log UTL_file.file_type;
BEGIN
v_filehandle_log :=UTL_file.FOPEN('e:\tasi\data','apr_email_reminder.log','w');
UTL_file.PUT_LINE(v_filehandle_log,'Begin EMAIL REMINDER Process');
DECLARE
TYPE db_view_fields is record(
requisition_number varchar2(15),
amendment_number varchar2(3),
requisition_yr varchar2(4),
approver_uid varchar2(12),
category varchar2(25));
db_view_rec db_view_fields;
email_address varchar2(45);
uid varchar2(12);
-- DEFINE FIELDS
v_email_count integer :=0;
--CURSOR
CURSOR email_reminder_fields IS
Select aat_requisition_number,aat_amendment_number,
aat_requisition_yr,aat_approver_uid,aat_category
FROM joyceba.v_apr_email_reminder;
BEGIN
OPEN email_reminder_fields;
LOOP
FETCH email_reminder_fields
INTO db_view_rec;
EXIT WHEN email_reminder_fields%NOTFOUND;
IF SQL%FOUND THEN
-- no update of email sent field since the actual procedure thats sends email will do this
-- IF CATEGORY = 'P' then look for e-mail address in control_points_table
-- otherwise look in security table
IF db_view_rec.category = 'P' THEN
SELECT acp_cp_distribution_list
INTO email_address
FROM
asidba.apr_control_points_table
WHERE acp_category =
db_view_rec.category;
uid:= db_view_rec.approver_uid;
asidba.MAILUSERS(email_address,
db_view_rec.requisition_number,
db_view_rec.amendment_number,
db_view_rec.requisition_yr,
uid,
db_view_rec.category 'APR approval reminder email');
-- ELSE
-- SELECT ast_email,ast_uid
-- INTO email_address,uid
-- FROM
--asidba.apr_requisition_table,asidba.apr_security_table
-- WHERE art_originator_uid =
---ast_uid;
--
END IF;
v_email_count := v_email_count + 1;
END IF;
END LOOP;
UTL_file.PUT_LINE(v_filehandle_log,
'Count of email reminders:'||to_char(v_email_count));
CLOSE email_reminder_fields;
EXCEPTION
WHEN TIMEOUT_ON_RESOURCE THEN
UTL_file.PUT_LINE(v_filehandle_log, 'Procedure TIMED OUT');
WHEN INVALID_CURSOR THEN
UTL_file.PUT_LINE(v_filehandle_log, 'Invalid cursor operation');
WHEN NOT_LOGGED_ON THEN
UTL_file.PUT_LINE(v_filehandle_log, 'Procedure could not connect to Oracle');
WHEN UTL_FILE.invalid_path THEN
UTL_file.PUT_LINE(v_filehandle_log, ' UTL_file error:invalid patH');
WHEN UTL_FILE.invalid_filehandle THEN
UTL_file.PUT_LINE(v_filehandle_log, ' UTL_file error:invalid filehandle');
WHEN UTL_FILE.write_error THEN
UTL_file.PUT_LINE(v_filehandle_log, ' UTL_file error: unable to write to output file');
WHEN OTHERS THEN
IF SQL%NOTFOUND THEN
UTL_file.PUT_LINE(v_filehandle_log,'NO data found, file created');
END IF;
UTL_file.PUT_LINE(v_filehandle_log, ' UTL_file ERROR unknown');
END;
UTL_file.PUT_LINE(v_filehandle_log,'EMAIL REMINDER process completed');
UTL_file.FClose(v_filehandle_log);
END;