Long time ago, I had to create procedure which will unlock a user, send the password to some and it's status to others and check if the user is used or not. In a nutshell, the requirments for that procedures were:
It took me around 4 months to finish all the details of the package as a lot of new requirments were coming, but here is the final result :)
CREATE OR REPLACE PACKAGE manage_fixer AS
FUNCTION Parsing(text in Varchar2) return varchar2;
FUNCTION RendrText(text IN VARCHAR2) return varchar2;
/*
Use following code when updates of RendrText required:
Update RenderText and test via Parsing_test
-- ==========================================
PROCEDURE Parsing_test(Test in Varchar2);
PROCEDURE renderText (text IN VARCHAR2);
*/
PROCEDURE SEND_MAIL(p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_subject IN VARCHAR2,
p_message IN VARCHAR2);
PROCEDURE SDM(p_r_from in varchar2,
text in VARCHAR2,
p_sbj in VARCHAR2,
o_message in VARCHAR2,
p_all_to in VARCHAR2,
p_all_cc in VARCHAR2);
PROCEDURE SEND_MAIL_O(p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_subject IN VARCHAR2,
p_message IN VARCHAR2,
recs IN VARCHAR2,
recs2 IN VARCHAR2);
PROCEDURE turnFixer (
OnOff IN VARCHAR2,
p_from IN VARCHAR2,
p_sbj IN VARCHAR2,
p_to IN VARCHAR2, -- password requestor
p_to_sec IN VARCHAR2, -- all others to be notified as TO
p_cc_sec IN VARCHAR2, -- all others to be notified as CC
p_cc_oo IN VARCHAR2 default null, -- non-standard TO e-mail addresses
DATE_WN IN VARCHAR2, -- Request timestamp
DATE_CT IN VARCHAR2, -- Date confirmed
CNFT_BY IN VARCHAR2);
END manage_fixer;
CREATE OR REPLACE PACKAGE BODY manage_fixer
AS
function Parsing(text in Varchar2) return varchar2
is
v_next_column binary_integer;
v_rec_em_len binary_integer;
v_rec_emails varchar2(2000);
v_sngl_rec_em varchar2(200);
type array is varray(100) of varchar2(2000);
array1 array := array();
Names varchar2(2000);
i Number;
begin
v_rec_emails := text;
v_rec_em_len := length(v_rec_emails);
v_rec_emails := v_rec_emails||';';
v_next_column := 1;
i := 1;
IF instr(v_rec_emails,';') = 0 THEN
v_sngl_rec_em := v_rec_emails;
v_rec_em_len := 1;
END IF;
WHILE v_next_column <= v_rec_em_len
LOOP
v_sngl_rec_em := substr(v_rec_emails,v_next_column,instr(v_rec_emails,';',v_next_column) - v_next_column);
v_next_column := instr(v_rec_emails,';',v_next_column)+1;
array1.extend;
IF trim(v_sngl_rec_em) like 'Change%' THEN
v_sngl_rec_em := '[email protected]';
ELSIF trim(v_sngl_rec_em) like 'FC_Adminin%' THEN
v_sngl_rec_em := '[email protected]';
ELSIF trim(v_sngl_rec_em) like 'iFlex%' THEN
v_sngl_rec_em := '[email protected]';
ELSIF trim(v_sngl_rec_em) like 'DB %' THEN
v_sngl_rec_em := '[email protected]';
ELSE
v_sngl_rec_em := Rendrtext(text => v_sngl_rec_em);
END IF;
array1(i) := v_sngl_rec_em||'; ';
Names := Names||array1(i);
i := i + 1;
END LOOP;
return(Names);
end Parsing;
function RendrText(text IN VARCHAR2) return varchar2
is
v_next_column binary_integer;
v_rec_em_len binary_integer;
v_rec_emails varchar2(2000);
v_sngl_rec_em varchar2(2000);
type array is varray(100) of varchar(2000);
array1 array := array();
Names varchar2(2000);
i Number;
begin
v_rec_emails := trim(text);
v_rec_em_len := length(v_rec_emails);
v_rec_emails := v_rec_emails||' ';
v_next_column := 1;
i := 1;
IF instr(v_rec_emails,' ') = 0 THEN
v_sngl_rec_em := v_rec_emails;
v_rec_em_len := 1;
END IF;
WHILE v_next_column <= v_rec_em_len
LOOP
v_sngl_rec_em := substr(v_rec_emails,v_next_column,instr(v_rec_emails,' ',v_next_column) - v_next_column);
v_next_column := instr(v_rec_emails,' ',v_next_column)+1;
array1.extend;
array1(i) := v_sngl_rec_em;
i := i + 1;
END LOOP;
dbms_output.put_line(names);
Names := array1(2)||'.'||array1(1)||'@company.bg';
return(names);
end RendrText;
/* Use following code when updates of RendrText required:
Update RenderText and test via Parsing_test
-- ==========================================
procedure Parsing_test(Test in Varchar2) is
v_next_column binary_integer;
v_rec_em_len binary_integer;
v_rec_emails varchar2(2000);
v_sngl_rec_em varchar2(2000);
type array is varray(100) of varchar(2000);
array1 array := array();
Names varchar2(2000);
i Number;
begin
v_rec_emails := Test;
v_rec_em_len := length(v_rec_emails);
v_rec_emails := v_rec_emails||';';
v_next_column := 1;
i := 1;
IF instr(v_rec_emails,';') = 0 THEN
v_sngl_rec_em := v_rec_emails;
v_rec_em_len := 1;
END IF;
WHILE v_next_column <= v_rec_em_len
LOOP
v_sngl_rec_em := substr(v_rec_emails,v_next_column,instr(v_rec_emails,';',v_next_column) - v_next_column);
v_next_column := instr(v_rec_emails,';',v_next_column)+1;
array1.extend;
IF trim(v_sngl_rec_em) like 'Change Management (MG)' THEN
v_sngl_rec_em := '[email protected]';
ELSIF trim(v_sngl_rec_em) like 'FC_Administrators' THEN
v_sngl_rec_em := '[email protected]';
ELSIF trim(v_sngl_rec_em) like 'iFlex Support Team' THEN
v_sngl_rec_em := '[email protected]';
ELSIF trim(v_sngl_rec_em) like 'DB Admins (MG)' THEN
v_sngl_rec_em := '[email protected]';
ELSE
v_sngl_rec_em := Rendrtext(text => v_sngl_rec_em);
END IF;
dbms_output.put_line(v_sngl_rec_em);
array1(i) := v_sngl_rec_em||'; ';
Names := Names||array1(i);
dbms_output.put_line(Names);
i := i + 1;
END LOOP;
dbms_output.put_line(Names);
end Parsing_test;
PROCEDURE renderText (text IN VARCHAR2)
IS
v_next_column binary_integer;
v_rec_em_len binary_integer;
v_rec_emails varchar2(2000);
v_sngl_rec_em varchar2(2000);
type array is varray(100) of varchar(2000);
array1 array := array();
Names varchar2(2000);
i Number;
BEGIN
v_rec_emails := trim(text);
v_rec_em_len := length(v_rec_emails);
v_rec_emails := v_rec_emails||' ';
v_next_column := 1;
i := 1;
IF instr(v_rec_emails,' ') = 0 THEN
v_sngl_rec_em := v_rec_emails;
v_rec_em_len := 1;
END IF;
WHILE v_next_column <= v_rec_em_len
LOOP
v_sngl_rec_em := substr(v_rec_emails,v_next_column,instr(v_rec_emails,' ',v_next_column) - v_next_column);
v_next_column := instr(v_rec_emails,' ',v_next_column)+1;
array1.extend;
array1(i) := v_sngl_rec_em;
i := i + 1;
END LOOP;
Names := array1(2)||'.'||array1(1)||'@company.bg';
dbms_output.put_line(names);
END renderText;
*/
PROCEDURE SEND_MAIL
-- Used for password requestor/recipient
( p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_subject IN VARCHAR2,
p_message IN VARCHAR2)
as
l_mailhost VARCHAR2(255) := 'mail.company.bg';
l_sender_pref VARCHAR2(500);
l_mail_conn utl_smtp.connection;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_raw_data(l_mail_conn,utl_raw.cast_to_raw(name || ': ' || header || utl_tcp.CRLF));
-- ------------------- ------------
-- ^ ca aa eceeca ee?eeeoa a subject-a ^
END;
BEGIN
select rtrim(i.host_name)||':'|| rtrim(i.instance_name)||':'||rtrim(i.instance_number)||':'||user
into l_sender_pref
from v$instance i;
l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
utl_smtp.helo(l_mail_conn, 'mail.company.bg');
utl_smtp.mail(l_mail_conn, p_sender);
utl_smtp.rcpt(l_mail_conn, p_recipient);
utl_smtp.open_data(l_mail_conn );
send_header('From', p_sender);
send_header('To: ', p_recipient);
send_header('Subject', p_subject);
send_header('Cc: ',p_recipient);
utl_smtp.write_raw_data(l_mail_conn,utl_raw.cast_to_raw(utl_tcp.CRLF || p_message || utl_tcp.CRLF));
-- ------------------ ------------
-- ^ ca aa eceeca ee?eeeoa a subject-a ^
utl_smtp.close_data(l_mail_conn );
utl_smtp.quit(l_mail_conn);
end SEND_MAIL;
PROCEDURE SEND_MAIL_O (p_sender IN VARCHAR2,
-- Used for all other e-mailing
p_recipient IN VARCHAR2,
p_subject IN VARCHAR2,
p_message IN VARCHAR2,
recs IN VARCHAR2,
recs2 IN VARCHAR2)
as
l_mailhost VARCHAR2(255) := 'mail.company.bg';
l_sender_pref VARCHAR2(255);
l_mail_conn utl_smtp.connection;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_raw_data(l_mail_conn,utl_raw.cast_to_raw (name || ': ' || header || utl_tcp.CRLF));
END;
BEGIN
select rtrim(i.host_name)||':'|| rtrim(i.instance_name)||':'||rtrim(i.instance_number)||':'||user
-- ------------------- ------------
-- ^ ca aa eceeca ee?eeeoa a subject-a ^
into l_sender_pref
from v$instance i;
l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
utl_smtp.helo(l_mail_conn, 'mail.company.bg');
utl_smtp.mail(l_mail_conn, p_sender);
utl_smtp.rcpt(l_mail_conn, p_recipient);
utl_smtp.open_data(l_mail_conn );
send_header('From: ',p_sender);
send_header('To: ',recs);
send_header('Subject', p_subject);
send_header('Cc: ',recs2);
utl_smtp.write_raw_data(l_mail_conn,utl_raw.cast_to_raw(utl_tcp.CRLF || p_message || utl_tcp.CRLF));
-- ------------------- ------------
-- ^ ca aa eceeca ee?eeeoa a subject-a ^
utl_smtp.close_data(l_mail_conn );
utl_smtp.quit(l_mail_conn);
end SEND_MAIL_O;
PROCEDURE SDM(p_r_from in varchar2,
-- Used for nonstandard e-mail addresses
-- They must be semi-colon separated! (Except the last one)
text in VARCHAR2,
p_sbj in VARCHAR2,
o_message in VARCHAR2,
p_all_to in VARCHAR2,
p_all_cc in VARCHAR2)
IS
v_next_column binary_integer;
v_rec_em_len binary_integer;
v_rec_emails varchar2(2000);
v_sngl_rec_em varchar2(2000);
BEGIN
v_rec_emails := trim(text);
v_rec_em_len := length(v_rec_emails);
v_rec_emails := v_rec_emails||';';
v_next_column := 1;
IF instr(v_rec_emails,';') = 0 THEN
v_sngl_rec_em := v_rec_emails;
v_rec_em_len := 1;
END IF;
WHILE v_next_column <= v_rec_em_len
LOOP
v_sngl_rec_em := substr(v_rec_emails,v_next_column,instr(v_rec_emails,';',v_next_column) - v_next_column);
v_next_column := instr(v_rec_emails,';',v_next_column)+1;
send_mail_O(p_sender => p_r_from,p_recipient => v_sngl_rec_em,p_subject => p_sbj,p_message => o_message,recs => p_all_to,recs2 => p_all_cc);
END LOOP;
END SDM;
PROCEDURE turnFixer (
OnOff IN VARCHAR2,
p_from IN VARCHAR2,
p_sbj IN VARCHAR2,
p_to IN VARCHAR2, -- password requestor
p_to_sec IN VARCHAR2, -- all others to be notified as TO
p_cc_sec IN VARCHAR2, -- all others to be notified as CC
p_cc_oo IN VARCHAR2, -- non-standard TO e-mail addresses
DATE_WN IN VARCHAR2, -- Request timestamp
DATE_CT IN VARCHAR2, -- Date confirmed
CNFT_BY IN VARCHAR2) -- Confirmator
IS
cnt INTEGER;
sts INTEGER; -- 0 means Locked, 1 means Unlocked
pwd VARCHAR2(7);
p_msg VARCHAR2(35);
o_message VARCHAR2(500);
w_message VARCHAR2(500);
c_message VARCHAR2(500);
p_r_to VARCHAR2(2000);
p_r_from VARCHAR2(2000);
v_sngl_rec_em varchar2(2000);
p_all_mails VARCHAR2(2000);
p_all_to VARCHAR2(2000);
p_all_cc VARCHAR2(2000);
p_to1 VARCHAR2(2000);
v_rec_emails varchar2(2000);
DATE_CT_N DATE;
DATE_TG DATE;
v_next_column binary_integer;
v_rec_em_len binary_integer;
BEGIN
dbms_output.put_line('Password requested at: '||DATE_WN);
p_all_to := Parsing(text => p_to_sec);
p_all_cc := Parsing(text => p_cc_sec);
p_to1 := p_to_sec ||';'|| p_cc_sec;
v_rec_emails := p_to1;
v_rec_em_len := length(v_rec_emails);
v_rec_emails := v_rec_emails||';';
p_r_to := p_to;
p_r_to := Rendrtext(text => p_r_to);
p_r_from := p_from;
p_r_from := Rendrtext(text => p_r_from);
v_next_column := 1;
o_message := 'The Fixer''s pasword was sent';
w_message := 'Please, logoff FIXER';
c_message := 'The Fixer was locked';
DATE_TG := to_date(DATE_WN,'DD.MM.YYYY HH24:MI:SS');
DATE_CT_N := to_date(DATE_CT,'DD.MM.YYYY HH24:MI:SS');
EXECUTE IMMEDIATE 'LOCK TABLE FIXER_STATUS IN EXCLUSIVE MODE';
IF instr(v_rec_emails,';') = 0 THEN
v_sngl_rec_em := v_rec_emails;
v_rec_em_len := 1;
END IF;
SELECT COUNT(*)
INTO cnt
FROM gv$session
WHERE username='FIXER';
-- Check Fixer_status table is correct, in case anybody has altered account status manually
-- update Fixer_Status
-- set status = (select DECODE(account_status,'LOCKED',0,1) from dba_users where username='FIXER');
SELECT status
INTO sts
FROM Fixer_Status;
CASE upper(OnOff)
WHEN 'ON' THEN
BEGIN
IF cnt <> 0 THEN
dbms_output.put_line('The FIXER account is already in use. Check with your colleagues!');
ELSIF sts = 1 THEN
dbms_output.put_line('The password was already sent');
ELSIF sts = 0 and cnt = 0 THEN
-- ^ keep this! ^
pwd := dbms_random.string(null,7);
p_msg := 'The new Fixer''s password is '||pwd;
EXECUTE IMMEDIATE 'alter user FIXER identified by '||pwd;
EXECUTE IMMEDIATE 'alter user FIXER account unlock';
EXECUTE IMMEDIATE 'Update fixer_status set status = 1';
EXECUTE IMMEDIATE 'commit';
-- Send the password ==================
send_mail(p_sender => p_r_from,p_recipient => p_r_to,p_subject => p_sbj,p_message => p_msg);
-- ====================================
dbms_output.put_line('E-mail to '||p_r_to||' with the password was sent');
EXECUTE IMMEDIATE 'Insert into fixer_history(N,Incident#,Incident_received_at,Account_which_is_requested,Account_requested_by,Account_requested_at,Confirmed_by,Confirmed_at,Account_enabled_by,Account_enabled_at) values(fixer.nextval,:1,:2,:3,:4,:5,:6,:7,:8,:9)'
using p_sbj,DATE_TG,'FIXER@FIXER',p_to,DATE_TG,CNFT_BY,DATE_CT_N,p_from,sysdate;
EXECUTE IMMEDIATE 'commit';
dbms_output.put_line('The history was applied');
-- Notify all others =======================================
WHILE v_next_column <= v_rec_em_len
LOOP
v_sngl_rec_em := substr(v_rec_emails,v_next_column,instr(v_rec_emails,';',v_next_column) - v_next_column);
v_next_column := instr(v_rec_emails,';',v_next_column)+1;
IF trim(v_sngl_rec_em) like 'Change%' THEN
v_sngl_rec_em := '[email protected]';
ELSIF trim(v_sngl_rec_em) like 'FC_Admin%' THEN
v_sngl_rec_em := '[email protected]';
ELSIF trim(v_sngl_rec_em) like 'iFlex%' THEN
v_sngl_rec_em := '[email protected]';
ELSIF trim(v_sngl_rec_em) like 'DB%' THEN
v_sngl_rec_em := '[email protected]';
ELSE
v_sngl_rec_em := Rendrtext(text => v_sngl_rec_em);
END IF;
send_mail_O(p_sender => p_r_from,p_recipient => v_sngl_rec_em,p_subject => p_sbj,p_message => o_message,recs => p_all_to,recs2 => p_all_cc);
p_all_mails := v_sngl_rec_em ||' and '||p_all_mails;
END LOOP;
dbms_output.put_line('Confirmation e-mail was sent to '||p_all_mails||' and '||p_cc_oo);
-- =========================================================
-- Notify non-standard e-mails >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
IF p_cc_oo is not null THEN
SDM(p_r_from,p_cc_oo,p_sbj,o_message,p_all_to,p_all_cc);
END IF;
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
ELSE
dbms_output.put_line('Error, please check the fixer''s table. Sts='||sts||' cnt='||cnt);
-- Should never come here!
END IF;
END;
WHEN 'OFF' THEN
BEGIN
IF cnt <> 0 THEN
send_mail(p_sender => p_r_from,p_recipient => p_r_to,p_subject => p_sbj,p_message => w_message);
-- Maybe send this e-mail to others
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
dbms_output.put_line('The FIXER account is in use and isn''t locked please try again after some time. Email to '||p_to||' with logoff request was sent.');
ELSIF sts = 0 THEN
dbms_output.put_line('The Fixer was already locked');
ELSIF sts = 1 and cnt = 0 THEN
EXECUTE IMMEDIATE 'alter user fixer account lock';
EXECUTE IMMEDIATE 'update fixer_status set status = 0';
EXECUTE IMMEDIATE 'commit';
send_mail(p_sender => p_r_from,p_recipient => p_r_to,p_subject => p_sbj,p_message => c_message);
dbms_output.put_line('E-mail to '||p_r_to||' with locking information was sent');
dbms_output.put_line('Update fixer_history set ACCOUNT_DISABLED_BY ='''||p_from||''',ACCOUNT_DISABLED_AT = '''||sysdate||''' where INCIDENT# = '''||p_sbj||''';');
EXECUTE IMMEDIATE 'Update fixer_history set ACCOUNT_DISABLED_BY ='''||p_from||''',ACCOUNT_DISABLED_AT = sysdate where INCIDENT# = '''||p_sbj||'''';
EXECUTE IMMEDIATE 'commit';
dbms_output.put_line('The history was applied');
WHILE v_next_column <= v_rec_em_len
LOOP
v_sngl_rec_em := substr(v_rec_emails,v_next_column,instr(v_rec_emails,';',v_next_column) - v_next_column);
v_next_column := instr(v_rec_emails,';',v_next_column)+1;
IF trim(v_sngl_rec_em) like 'Change%' THEN
v_sngl_rec_em := '[email protected]';
ELSIF trim(v_sngl_rec_em) like 'FC_Adminin%' THEN
v_sngl_rec_em := '[email protected]';
ELSIF trim(v_sngl_rec_em) like 'iFlex%' THEN
v_sngl_rec_em := '[email protected]';
ELSIF trim(v_sngl_rec_em) like 'DB%' THEN
v_sngl_rec_em := '[email protected]';
ELSE
v_sngl_rec_em := Rendrtext(text => v_sngl_rec_em);
END IF;
send_mail_O(p_sender => p_r_from,p_recipient => v_sngl_rec_em,p_subject => p_sbj,p_message => c_message,recs => p_all_to,recs2 => p_all_cc);
p_all_mails := v_sngl_rec_em ||' and '||p_all_mails;
END LOOP;
dbms_output.put_line('The locking e-mail was sent to '||p_all_mails||' and '||p_cc_oo);
IF p_cc_oo is not null THEN
SDM(p_r_from,p_cc_oo,p_sbj,c_message,p_all_to,p_all_cc);
END IF;
ELSE
dbms_output.put_line('Error, please check the fixer''s table');
END IF;
END;
END CASE;
END turnFixer;
END manage_fixer;
That package is called as follows:
store set origenv replace set longchunksize 300 set feed on; set serveroutput on set ver on; prompt Enter values: prompt ============ prompt for OnOrOff - OnOff prompt for From - p_from prompt for Subject - p_sbj prompt for To - p_to prompt for MAIL_ADDRESSES_IN_CC - p_to_sec prompt for MAIL_ADDRESSES_IN_BB - p_cc_sec prompt for DIFFERENT_MAIL_ADDRESSES - p_cc_oo prompt for DATE_WNT - DATE_WN prompt for DATE_CM - DATE_CT prompt for CNF_BY - CNFT_BY define ON_OR_OFF =&OnOrOff define FRM =&From define SBJ =&Subject define TOT =&To define TOOTH =&MAIL_ADDRESSES_IN_CC define CCOTH =&MAIL_ADDRESSES_IN_BB define CCOO =&DIFFERENT_MAIL_ADDRESSES define DTGT =&DATE_WNT define DTCT =&DATE_CM define CNFT =&CNF_BY col xx format a400 exec MANAGE_FIXER_2.turnfixer(OnOff => '&ON_OR_OFF',p_from => '&FRM',p_sbj => '&SBJ',p_to => '&TOT',p_to_sec =>'&TOOTH',p_cc_sec =>'&CCOTH',p_cc_oo => '&CCOO',DATE_WN => '&DTGT', DATE_CT => '&DTCT',CNFT_BY => '&CNFT'); @origenv undef On_Or_Off undef Frm undef sbj undef To Undef ToOth undef CcOth undef CCOO undef DTGT undef DTCT undef CNFT