prompt Enter an outgoing e-mail SMTP server host
define email_server_host = &1
begin
if sys_context('USERENV','SESSION_USER') != 'SYS' then
raise_application_error(-20000, 'This script must be run AS SYS');
end if;
end;
/
create or replace procedure sys.email_notification_procedure wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
8d1 40e
VeYguoLO7klQMOD091RnOY7N6yYwg2NcuiCDfI4Zxz8C6i6ZWT7mVZJ8aSwlXabiX5Ax6B9/
q8zsDVn2XkSDnC21vLdn5nYOHyoGN11uu06/4ssapd0sWsqzVTCSSTTQwesP6/owIvC7agcU
QaKx0KGRPVosicCYZzwoQ6JMTUCbI1rdZz+e7o5Tyz6758ZmoqyAHew/UhAPZnrpbkUlHr6b
yjKpvAyTTWdBrHQS5inCHQhrhst1GzPBY8KTiXrpoTD9coufGCvdENtDaAw1ASMqVyL7rgc3
RssAmgZwbiznWJAsfALzEX2sVhR2djz0Cti8/ECr/4F7RK26zboDMdjEO19Qtm4fHB3wSdIy
UFqdUavFMAXAiNky1oPmoqOA25+KleDvAnkMzBkBacuY3RzJq0prDL4C7u5kaW7TQdvqYsIH
2AoJ39tlJAZf1nFWiu3Ub++/+MiDUOD3l7fU6AB0LsLLMUsFpY5Vcro290JOIaeyUZbhZ7Wc
pfkwHGLu0wpSHEbT4YsjaQpLzM+xSCS4ik/wOMe1+H276sFPTEmWFkiwrVGQ5v22VciSRsUV
AhvP3QRSvg8qqnpmLlES3YK5wD9ttRa0yQiyaz/oGRRUVlmytAIw51VpHQme9DtdatzpbJzq
JsKbRRShbsihz5DJnIkKXi+5Vv+ezx/XyYYTdg9SO6VrrPJ13gtv1qyrbYjz+6W4ABYN8ETu
fl9+AEPR5yPdojuPn994XylBo9m5i/ImF+DVTkDA6lXjHunek4LJMS2WkO5jgpLgF1tYx7xA
i/A0/W2PAxwBaQC8JZmTXFkAG92oM7i0RaHUKMQ7s+cHUaKpcBuicIj28iM30bAUnKNrlc3D
cubPD6wZt1EBDGPzkSllE4W/DZjLatiSWBqrxRx1sNzTmjurRcANj5yXuTCUa0rq9RMf7vEI
zYn8CPy/IVjBZdeZSOknt3NjWH4FTjGfJwQLD4PS9svHf3rEYZp26ULMudQjWRYwYUokOcP9
39ij+GqqlpHqWQ==
/
show errors
grant execute on sys.email_notification_procedure to public;
begin
dbms_scheduler.drop_program (
program_name => 'sys.email_notification_program',
force => true);
exception when others then null;
end;
/
begin
dbms_scheduler.create_program (
program_name => 'sys.email_notification_program',
program_action=> 'sys.email_notification_procedure ',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 6,
enabled => FALSE) ;
dbms_scheduler.define_metadata_argument (
program_name => 'email_notification_program',
argument_position => 1 ,
argument_name => 'message',
metadata_attribute => 'EVENT_MESSAGE') ;
dbms_scheduler.define_program_argument (
program_name => 'email_notification_program',
argument_position => 2,
argument_name => 'recipient_address',
argument_type => 'varchar2') ;
dbms_scheduler.define_program_argument (
program_name => 'email_notification_program',
argument_position => 3,
argument_name => 'email_server_host',
argument_type => 'varchar2') ;
dbms_scheduler.define_program_argument (
program_name => 'email_notification_program',
argument_position => 4,
argument_name => 'sender_address',
argument_type => 'varchar2') ;
dbms_scheduler.define_program_argument (
program_name => 'email_notification_program',
argument_position => 5,
argument_name => 'subject_prefix',
argument_type => 'varchar2') ;
dbms_scheduler.define_program_argument (
program_name => 'email_notification_program',
argument_position => 6,
argument_name => 'email_server_port',
argument_type => 'number') ;
dbms_scheduler.enable ('email_notification_program');
end ;
/
grant execute on sys.email_notification_program to public;
create or replace procedure sys.add_job_email_notification wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
221f c06
LJgrI9/yuQ8GWaZAPQ3Rnz8kmYQwg82ruscT1JJ6Hup+gq1Eo1H3kcahHpdeNtVYNjfCqZYQ
OI4TGrlXK8X6eYKGLJognUxwH1Bs9AVOwCEc/c0//vjRQS2FY4eXFlgPDxedYhnJOgc613mf
xFSaNJi+7GQgb7+kA4SFChGJAv+J0wYHXUc4uSZqOd9+fPySgk9AoYswHIxScVsOF0uItkOM
pi/QSpAhWntTOLjRUvxFiSN3A/cptAgdMIvQ72/t6Kgi1Lneb87rX8zGAI0FZ1AoA1dS4Wd8
Vx51QBwBY3DN2PRSxFSLLCAkc3q1dbUNozS6D0PExFUKlVs176TtUUjx1wSk+B/93qLZOj15
3AYKD04e+AXPoYdiGfFpLWbTjF5QG20+2h0t3IxX3kkzO7gVxQ/595YDDx8KMrYSb3/bQURS
iwe9kGk8LeO2v0btAxZWzaTo6/MBXcaoKaS/U2Nmwya00c4TW3Jivqoc4c/4vB5GzHVc7g65
/qBzrPIQLGhqd7hp/r7ICAFuvvNN9fd1YJ78JaVHS/AEcX0d3rzgpmt88uQtKKcWAbOIAtJ6
C0os69jjZ9fLSJcnBWySDsYIF0ckHa0lfLPNMFZWR+nS5NuA2pxNBwDbMowpju3djVCNiv5p
DLiw7mhkH3IZwb86wLz/ZL2RLJOWhcWdmBsEppHLN6CH979tbEirxpBdwGDpYGuXyB16QODx
V4yp8RFxAzeeShV48/VjV7NTr6ZWKw+C6eIbywyRwin69rTgNRogCC6RuAiy2CGZTUpPogOT
RA8MTuAxTgvRvKzq2RC+qwiiWHsfiMNXHGXlbAZvmA66q9nJmhy0BzMMmKqRLHSKjISYJDDe
KEEsMXL41vH8BGaVtzdRXRpf3/vru+16q35GiLP0LJ9n4Ycy0YbieOzhWkeUu+ZzGKyCGDN4
bakfRAkUA8+LPhe29wvK7MpARiXbKORdwm06KR1GtW0Ow48e9Hm2dA6QhUSEYikMkQAoZy4Q
7u6vMOB6encMOQ+5/P1xFEYb5j5rdSTixxuEJnr0VAghFDl168r6PrItcllRFGZ67aghSY7g
B0VkA1i2xneG5oTrppcCCEvwNjRx4GJ8nQMO/PLPkxzUVuyskG5f6tG8HLdOXha6kRV2pXyk
8jVuUIT5bB5r7uaRSY6f0b61Xwtrieb4RtJppTvmCma702Hxp+n7VYBxLYqo5FDdMFkdfgpe
JOR4otFsQFEN0N0EvWojkd3iTSMEWYQAYRw1bXKZH7ZsjQ4JKJh8KqzKqaaEwrqPsyqrwNev
e40hRuINN+AmEbdxiFXjJ1IuQjpJwT/Aoy9NmyiDC4U+0NGNAGcHNFYWx9Pl7wBB5fBfMFZy
is1vvc5MVZlFleVpZre8YuBKWTmfJmJ3XEPZQk2Db5LCGjxfzRixYCShB++Gg/JsqZsv6DVp
weqZSkXa+H2heXeGvSGIER5NKjw6lzFzJXJwN56h0ZAHjbE4D+i570KIEpQoXpdU20XgNNa7
vZAYbE4xj48O/sJjOD2YLM/TpwkZhKqSJpD4+vSR1au/Ke955dsW9kfsXNyNPY5U2cLobmFA
YtERZEi0U9Fxe2r00m790V9lcqF2rViJVbsIXxSE8oP05+LzfKzunkwHoAEMYs/DLEE4Fnw8
TsRVElfNkbFOgTSYMPi6Pw3/b6oVcEfVrLN28xwce/y94WdQITv1uYssmxuIf2mlPcLbzjhu
fIICOrdYFa6n8AsURk31bjiQ0sUMBpYykh1Mkk1IL14BXl5hvDZeLm2KOiw6VXu6qpJ1txPa
4J6IqBKoAs8//F7ogbm0F1IlzeUMNx3rfi/p6Y0xDpCs4JKn6AUZJYy9LBz1CQ6J+4Bhhqs+
VfJT0U7EMItlDEzEQkZja8AjzmWv2qPkfQcfyEwA7Y4Pbev6HOU+AqGZji4WOWxD4DmwE5OP
U3fYNd04xj8nVMaQcqUCASFJVHwaxYbI0iJkBHYzP6ngyoooZfpZS9mgILj2idpXIJtvwy15
Z6l77BP0p2CYr6iPfuPmLVRbnHfFf51W8Jep2OuaQYDwEucvvC0z7Z+KXcPSfOUBxsnMn2PG
hyV6t1HQ5Dbyt5OaxNnr3jG8RbJhbzq8BEwzqeKtEQc/E56bJCZxlcewyZQRggAnj38uZUmh
dSTytvMeG97atiAA04QJ0cF4Cnpq4i2sArJrFylblAHgrM+7FY/N+w70xI5l7pWNAjLYp/NS
bWuN8yM1TGmpzrhF/s+A5RVkU6w2QLZCMluqtwZ7tHw2WEY1Q+9XaIXaJEGQpjaGYKaq4SXG
b0zEC3/HoZRueLirzXtBW+nPjphwrm4MvgCaGiGN1pKlNmThRWNzT55qkecLjskKWrh0So80
Quyttqkh/YLzln4GxFyPgsrwNLxw4u5VdyeIi4kt8ik+Bc8lBo15+WnaUS2V2rMwR8Y7p9Fb
Fdsg4XibzqHQxPDWhH85wIZaME19PcNU1SDwPw2uDs2wJUGeDRrl51arBt5y1mhA86hK/ThL
Phfwo5cI/7ZEh0CcsZx2p3AEE5Xu+70pPg/ctWYFCFKx++aHrCEo9lf2p44dtSqVuMFjKyy5
+J+dF+5Z9+4SW5f59so43A5T1O+B/QC8rQvzbnqwQwJ2GbyNTZXiWloESaVjQFNHv6QcpVg2
7lfkS1Tfj6PN0lQhhpvsRTWYbsI9EVjy16lPi9kd+QY4roTjcN5sr6Q2Dtf9Lqz/XSguNAbg
A2trU5k1pP1UYWWd3yRFjWuOOyI7RdfqEjHGQwZzwPmvtNw7It7hr5LYRZQPiY8wYExn6cwr
lRLCsFHeAZ2awf6+c9u08gegtG/wxJAQlDtJI1O5xaCjLvhAHw0fB5aHTRe6Y6x55/To6mvQ
aRtzxdHS6TwgvSRpwue8SVEAej/ScyTk+XjuSf0P2XDjAxQTGnbv2sesiWHOOHC7yL6SIYc9
ug+VOnCq//D+GQ/GOriq/5Kdps+kBcFPryv4ZDdkCxKgJAUUBiDm7lRxta77Clw2fzwhk8Bw
HJy1Ov1YNKd4
/
show errors
create or replace public synonym add_job_email_notification for
sys.add_job_email_notification;
grant execute on sys.add_job_email_notification to public;
create or replace procedure sys.remove_job_email_notification wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
ad9 4d4
qpXz6AXyFGIr0eqn4Y84+3I044Uwg+0rBUiDZy/Ngg9/5cMxojb7zGPlRlo5lkv07W2fsWmS
4LNAw8M+WkYz9NC7is5IwAqOKpdqwoLHi8DJn3pewNvkWn+0ZKrN+diGT5ggCJKxJ8csIL8H
11WL+PxLMXn9SB2Po8sL7gJ0qZl1EichgcNkvUFIVPR2hwP3N9KWtpgfLBNzorwvWa+Gm/dJ
GuOZ5MI0XvcXFjJCL4nd9sDOeqbtdwhanmq8BLH0IV5ZR5CnjFg8dmvN8NZn5qsii1mULcOk
145P79wUm6LpjstYFS+RBGU49X5Mw4R9nxY52A6GcyKK/6rYxB5z/Eu8RFbfdp+lSxkZ4ZSk
K/4ec+77uZ8S03tz1MguUxEOf/kLb3mqBPx1HrOJOnmVoHSLX2YyA8i6aVD3QXmzo8jDvQNa
3ecWZXqPyTHonsd3jw+2Gke88i6ZTXSWqxfL1aBD1D2jqNo9bYazQrt20DIlzTKYxUuo998U
oWz0CFJVgnQlXTXliiGmW9kpqVhls0vPLhr3cL2L+xNLBJl6RfQXQTxHxDWC7u/AK3oMOZl+
al54Uxcudhh3InnILfGoPPptebsxErJtPt8cWYkH8IU53spBpfqquSOPX45OdIT6JZvB0rca
8rTqUGvdT7JFdpsMr2+zxYhLsUYTcfgaJLhNLO+JxkEswAgk5v/v3xmlKvteSXT8kDcJZ6nK
iFE1DcQZym2q9h+cZi5c63qjkyahAo7TKqwQYE9NJgSx5+JRouEPFUkvTqKu7pu5G+vo13cw
l92sE945aUcsvK8VZiicbIjshnHJhW9RWRoggIGRZhkYWOrr8cVBPZNNK9gRbHX1uqFyrxqx
lwOVvw3SC6iXDSrZkxifgwUCZElC8r1Jxg0TZqklNaI1af7q9kp7kEzgwsLYzaYuI40Sl+wZ
OqyKSP+CKw588mRvUXCkQyl9vPPZoAp+E2VjGifV0PbJqv1vG+q5QrsGUU4p9wJcBT/Dtoxm
yZx9Q0OGuWTiCZ0LE5OPxtLMLaAWtEwwu8mgs+zvrjpI2dns2Cp7b4ynJMJNp7yfleuf8vuN
rYB0zrIdpLINzEhiZEgKbU8Wxu9Xf5kAWaEQuOe04ZHZiPwSg54X8uAkAjrsY5DsE8kJm9Dm
4a78N0+Z1D9CY8U81GFnygFlHb3ekxV5D/pY8LE7b6B59xMxI2HUWnQc+Lv54eNwFT4=
/
show errors
create or replace public synonym remove_job_email_notification for
sys.remove_job_email_notification;
grant execute on sys.remove_job_email_notification to public;
----------------------------------end job_email_notification.sql--------------------
exec add_job_email_notification(job_name => 'EXPORT_JOB', subject_prefix => 'Apex Job Notification', recipient_address => 'kshitij@apexsoftcell.com',sender_address =>'info@apexsoftcell.com', email_server_host => 'mail.apexsoftcell.com', email_server_port => 25);
exec DBMS_SCHEDULER.run_job ('EXPORT_JOB');
---------------------------------------start remove_job_notification.sql--------------
-- Run this script as SYS for example:
-- SQL> connect sys/sys_password as sysdba
-- SQL> @remove_job_notification
-- This script removes all objects created by the accompanying
-- job_notification.sql script. It also removes any job e-mail
-- notifications that have been added.
-- this must be run as SYS (to do privilege checking) so check this
begin
if sys_context('USERENV','SESSION_USER') != 'SYS' then
raise_application_error(-20000, 'This script must be run AS SYS');
end if;
end;
/
drop procedure sys.email_notification_procedure;
begin
dbms_scheduler.drop_program (
program_name => 'sys.email_notification_program',
force => true);
end;
/
drop public synonym add_job_email_notification;
drop procedure sys.add_job_email_notification;
drop public synonym remove_job_email_notification;
drop procedure sys.remove_job_email_notification;
-- now remove any e-mailer jobs that have been created
-- They won't run anyway since the program they point to has been removed.
BEGIN
FOR old_notify_job IN
( SELECT '"'||owner||'"."'||job_name ||'"' name FROM dba_scheduler_jobs WHERE
job_name like '%_EMAILER%'
and comments like 'Auto-generated job to send email alerts for job%'
and program_owner = 'SYS'
and program_name = 'EMAIL_NOTIFICATION_PROGRAM'
)
LOOP
dbms_scheduler.drop_job(old_notify_job.name, TRUE);
END LOOP;
END;
/
---------------------------------------end remove_job_notification.sql--------------
No comments:
Post a Comment