CREATE OR REPLACE PACKAGE BODY NSLIJHS_DBA AS -------------------------------- -- -- PACKAGE BODY -- -- Package : NSLIJHS_DBA -- Purpose : Container for custom Oracle procs in PSoft DBs -- -- Notes : See package definition for history, other details -- -------------------------------- -------- -- -- Process Purging Procedures -- -------- -- PROCEDURE zz_dbms_output(v_stmt in varchar2) -- IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN dbms_output.put_line(v_stmt); -- END zz_dbms_output; -- -- FUNCTION get_parm_value(v_parmlist in varchar2, v_parm in varchar2) RETURN varchar2 -- AS pos1 number := 0; BEGIN pos1 := instr(v_parmlist,v_parm)+length(v_parm); return trim(BOTH '"' from substr(v_parmlist,pos1,instr(v_parmlist,' ',pos1)-pos1)); -- END get_parm_value; -- -- FUNCTION determine_nvs_expiration(v_prcsinstance in number, v_contentid in number) RETURN number -- AS v_parm varchar2(1000) := ''; v_nvs_expire number := 0; BEGIN for c1 in (select b.prcstype,b.prcsname,a.parmlist,b.enddttm,b.rqstdttm from psprcsparms a, psprcsrqst b where a.prcsinstance = b.prcsinstance and a.prcsinstance = v_prcsinstance) loop if c1.prcstype = 'nVision-Report' then if c1.prcsname = 'NVSRUN' then v_parm := get_parm_value(c1.parmlist,'-NRN'); select sysdate - (nvl(c1.enddttm,c1.rqstdttm)+ num_expire_days) into v_nvs_expire from ps_zz_cdm_nvs_exp where prcstype = c1.prcstype and prcsname = c1.prcsname and report_id = v_parm; return floor(v_nvs_expire); -- DRILLDWN are expired based on the parent nVision Report /* elsif c1.prcsname = 'DRILLDWN' then v_parm := get_parm_value(c1.parmlist,'-NDI'); dbms_output.put_line('DRILLDWN'); for c2 in (select a.report_id from psnvsbookrequst a,ps_cdm_list b, ps_zz_nvs_vw c,psprcsrqst d where run_cntl_id = d.runcntlid and b.prcsinstance = d.prcsinstance and b.prcstype = c.prcstype and b.prcsname = c.prcsname and b.prcsinstance = substr(v_parm,1,instr(v_parm,'_')-1) and b.contentid = substr(v_parm,instr(v_parm,'_')+1) and a.report_id = c.report_id and instr(b.content_descr,c.descr) > 0) loop select sysdate - (nvl(c1.enddttm,c1.rqstdttm)+ num_expire_days) into v_nvs_expire from ps_zz_cdm_nvs_exp where prcstype = c1.prcstype and prcsname = c1.prcsname and report_id = c2.report_id; zz_dbms_output('Drilldown: '||c2.report_id||' Expire: '||v_nvs_expire); return floor(v_nvs_expire); end loop; */ end if; elsif c1.prcstype = 'nVision-ReportBook' then -- This is a Report Book if c1.prcsname = 'RPTBOOK' then v_parm := get_parm_value(c1.parmlist,'-R'); for c2 in (select a.report_id from psnvsbookrequst a,ps_cdm_list b, ps_zz_nvs_vw c where a.run_cntl_id = v_parm and b.prcsinstance = v_prcsinstance and b.prcsname = c.prcsname and b.contentid = v_contentid and a.report_id = c.report_id and instr(b.content_descr,c.descr) > 0) loop select sysdate - (nvl(c1.enddttm,c1.rqstdttm)+ num_expire_days) into v_nvs_expire from ps_zz_cdm_nvs_exp where prcstype = c1.prcstype and prcsname = c1.prcsname and report_id = c2.report_id; return floor(v_nvs_expire); end loop; end if; end if; end loop; return null; EXCEPTION WHEN NO_DATA_FOUND then --zz_dbms_output('No Child!'); return null; WHEN OTHERS then zz_dbms_output('Hmmm, needs debugging! '||SQLERRM); return null; -- END determine_nvs_expiration; -- PROCEDURE zz_cleanup_report_purge_table(v_prcsinstance in varchar2) -- -- zz_cleanup_report_purge_table -- -- * removes records for a process from all process related tables -- * also removes record from work table (ps_zz_cdm_delete) -- * no longer queries from ps_zz_prcs_tbl, each table is listed here -- * psprcsrqst, psprcsque, ps_cdm_auth, ps_cdm_list, psprcsparms -- * psprcsrqsttext, ps_pycycl_job_tbl, ps_prcsrqstdist -- * ps_message_log, ps_message_logparm (different key) -- * ps_zz_cdm_delete -- IS v_sqlstmt varchar2(2000); BEGIN v_sqlstmt := 'delete ps_message_log where process_instance = :v_prcs'; execute immediate v_sqlstmt using v_prcsinstance; v_sqlstmt := 'delete ps_message_logparm where process_instance = :v_prcs'; execute immediate v_sqlstmt using v_prcsinstance; v_sqlstmt := 'delete ps_prcsrqstdist where prcsinstance = :v_prcs'; execute immediate v_sqlstmt using v_prcsinstance; v_sqlstmt := 'delete ps_pycycl_job_tbl where prcsinstance = :v_prcs'; execute immediate v_sqlstmt using v_prcsinstance; v_sqlstmt := 'delete psprcsrqsttext where prcsinstance = :v_prcs'; execute immediate v_sqlstmt using v_prcsinstance; v_sqlstmt := 'delete psprcsparms where prcsinstance = :v_prcs'; execute immediate v_sqlstmt using v_prcsinstance; v_sqlstmt := 'delete ps_cdm_auth where prcsinstance = :v_prcs'; execute immediate v_sqlstmt using v_prcsinstance; v_sqlstmt := 'delete ps_cdm_list where prcsinstance = :v_prcs'; execute immediate v_sqlstmt using v_prcsinstance; v_sqlstmt := 'delete psprcsque where prcsinstance = :v_prcs'; execute immediate v_sqlstmt using v_prcsinstance; v_sqlstmt := 'delete psprcsrqst where prcsinstance = :v_prcs'; execute immediate v_sqlstmt using v_prcsinstance; v_sqlstmt := 'delete ps_zz_cdm_delete where prcsinstance = :v_prcs'; execute immediate v_sqlstmt using v_prcsinstance; commit; EXCEPTION WHEN OTHERS then raise_application_error(-20000, SQLERRM, TRUE); -- END zz_cleanup_report_purge_table; -- PROCEDURE zz_cleanup_zzcdmdelete_table(v_prcsinstance in varchar2) -- -- zz_cleanup_zzcdmdelete_table -- -- * removes record for a process from ps_zz_cdm_delete table -- * called for each row processed, in order to pare down the -- work table, since RESTART may be used upon abnormal exit -- IS v_sqlstmt varchar2(2000); BEGIN v_sqlstmt := 'delete ps_zz_cdm_delete where prcsinstance = :v_prcs'; execute immediate v_sqlstmt using v_prcsinstance; commit; EXCEPTION WHEN OTHERS then raise_application_error(-20000, SQLERRM, TRUE); -- END zz_cleanup_zzcdmdelete_table; -- -- PROCEDURE zz_analyze_prcs_tables -- -- * analyze the prcs related tables (same ones that are purged) -- IS BEGIN dbms_stats.gather_table_stats('SYSADM1','PS_MESSAGE_LOG',CASCADE=>TRUE); dbms_stats.gather_table_stats('SYSADM1','PS_MESSAGE_LOGPARM',CASCADE=>TRUE); dbms_stats.gather_table_stats('SYSADM1','PS_PRCSRQSTDIST',CASCADE=>TRUE); dbms_stats.gather_table_stats('SYSADM1','PS_PYCYCL_JOB_TBL',CASCADE=>TRUE); dbms_stats.gather_table_stats('SYSADM1','PSPRCSRQSTTEXT',CASCADE=>TRUE); dbms_stats.gather_table_stats('SYSADM1','PSPRCSPARMS',CASCADE=>TRUE); dbms_stats.gather_table_stats('SYSADM1','PS_CDM_AUTH',CASCADE=>TRUE); dbms_stats.gather_table_stats('SYSADM1','PS_CDM_LIST',CASCADE=>TRUE); dbms_stats.gather_table_stats('SYSADM1','PSPRCSQUE',CASCADE=>TRUE); dbms_stats.gather_table_stats('SYSADM1','PSPRCSRQST',CASCADE=>TRUE); dbms_stats.gather_table_stats('SYSADM1','PS_ZZ_CDM_DELETE',CASCADE=>TRUE); EXCEPTION WHEN OTHERS then raise_application_error(-20000, SQLERRM, TRUE); -- END zz_analyze_prcs_tables; -- -- -- zz_cleanup_orphaned_prcs -- -- * removes records for processes that exist in the output -- tables (and vice versa), but not the process history tables -- * dbms_output statements embedded for debugging -- -- Note: Hard coded default retention of 90 days -- PROCEDURE zz_cleanup_orphaned_prcs IS v_retentiondays number := 90; BEGIN select retentiondays into v_retentiondays from ps_prcssystem; for rec in (select a.prcsinstance from ps_cdm_list a where not exists (select 'x' from psprcsrqst b where b.prcsinstance=a.prcsinstance) and nvl(a.expiration_date,nvl(a.enddttm,a.rqstdttm) + nvl(v_retentiondays,90)) < sysdate) loop zz_cleanup_report_purge_table(rec.prcsinstance); end loop; for rec in (select a.prcsinstance from psprcsrqst a where not exists (select 'x' from ps_cdm_list b where b.prcsinstance=a.prcsinstance) and nvl(a.enddttm,a.rqstdttm) + nvl(v_retentiondays,90) < sysdate) loop zz_cleanup_report_purge_table(rec.prcsinstance); end loop; -- END zz_cleanup_orphaned_prcs; -- -- -- zz_populate_report_purge -- -- * initializes the work table by listing all the -- processes and the actions that should be taken -- * dbms_output statements embedded for debugging -- PROCEDURE zz_populate_report_purge IS v_retentiondays number := 90; v_purge_action ps_zz_cdm_delete.purge_action%TYPE; v_nvs_days_expired number := 0; v_uri_scheme ps_cdm_dist_node.uri_scheme%TYPE; BEGIN select retentiondays into v_retentiondays from ps_prcssystem; delete ps_zz_cdm_delete; commit; for rec in (select a.prcsinstance, a.prcsname, a.prcstype, nvl(a.enddttm,d.rqstdttm) enddttm, d.runstatus,a.contentid, a.outputdir, a.prcsoutputdir, b.num_expire_days,decode(e.uri_scheme,' ','ftp',e.uri_scheme) v_uri_scheme, sysdate - (nvl(a.enddttm,d.rqstdttm) + nvl(b.num_expire_days,v_retentiondays)) v_days_expired, sysdate - (nvl(a.enddttm,d.rqstdttm) + v_retentiondays) v_nvs_system_expired, 'NO' is_orphan from ps_cdm_list a, ps_zz_cdm_expire b, psprcsrqst d, ps_cdm_dist_node e where a.prcsname = b.prcsname(+) and a.prcstype = b.prcstype(+) and a.prcsinstance = d.prcsinstance and a.distnodename = e.distnodename union select d.prcsinstance, d.prcsname, d.prcstype, nvl(d.enddttm,d.rqstdttm) enddttm, d.runstatus,abs(dbms_random.random) contentid, ' ' outputdir, ' ' prcsoutputdir, b.num_expire_days,' ' v_uri_scheme, sysdate - (nvl(d.enddttm,d.rqstdttm) + nvl(b.num_expire_days,v_retentiondays)) v_days_expired, sysdate - (nvl(d.enddttm,d.rqstdttm) + v_retentiondays) v_nvs_system_expired, 'YES' is_orphan from ps_zz_cdm_expire b, psprcsrqst d where d.prcsname = b.prcsname(+) and d.prcstype = b.prcstype(+) and not exists (select 'x' from ps_cdm_list c where c.prcsinstance=d.prcsinstance)) loop if rec.v_days_expired >= 0 then v_purge_action := 'DELETE'; else v_purge_action := 'NO ACTION'; end if; v_nvs_days_expired := rec.v_days_expired; if rec.prcstype like 'nVision-Report%' then if rec.prcsname <> 'DRILLDWN' or rec.is_orphan = 'NO' then -- ignore orphan, those not existing in CDM_LIST v_nvs_days_expired := nvl(nslijhs_dba.determine_nvs_expiration(rec.prcsinstance,rec.contentid), rec.v_nvs_system_expired); --zz_dbms_output(' Name: '||rec.prcsname||' prcs '||rec.prcsinstance); end if; end if; if v_nvs_days_expired >= 0 then v_purge_action := 'DELETE'; end if; if (v_nvs_days_expired >= 0 OR rec.v_days_expired >= 0) then insert into ps_zz_cdm_delete ( prcsinstance, prcsname, prcstype, contentid, runstatus, prcsoutputdir, zz_outputdir, enddttm, num_expire_days, purge_action) values ( rec.prcsinstance, rec.prcsname, rec.prcstype, nvl(rec.contentid,dbms_random.random), nvl(rec.runstatus,' '), nvl(rec.prcsoutputdir,' '), decode(rec.outputdir,' ','NULL',rec.outputdir), rec.enddttm, v_nvs_days_expired, v_purge_action); --zz_dbms_output('prcsin: '||rec.prcsinstance||' name: '||rec.prcsname||' type:'||rec.prcstype||' contentid: '||rec.contentid||' days: '||v_nvs_days_expired); end if; end loop; commit; -- Analyze work table before update dbms_stats.gather_table_stats('SYSADM1','PS_ZZ_CDM_DELETE',CASCADE=>TRUE); -- Expiration dates of all Drilldowns should be based on the parent nVision Report for rec2 in (select prcsinstance,prcstype,prcsname,num_expire_days,purge_action from ps_zz_cdm_delete where prcstype in ('nVision-Report','nVision-ReportBook') and prcsname in ('NVSRUN','RPTBOOK','TRRATES')) loop update ps_zz_cdm_delete a set num_expire_days = rec2.num_expire_days, purge_action = rec2.purge_action where prcsinstance = rec2.prcsinstance and prcstype = rec2.prcstype and prcsname = 'DRILLDWN'; end loop; commit; EXCEPTION WHEN OTHERS then raise_application_error(-20000, SQLERRM, TRUE); -- END zz_populate_report_purge; -- -------- -- -- Peoplesoft Integrator Procedures -- -------- -- -- zz_ps_long_to_char -- -- * returns first 254 characters of the comments field -- FUNCTION zz_long_to_char (rcn varchar2, oprid varchar2, cid varchar2) RETURN varchar AS text_c1 varchar2(32767); date_c1 date; sql_cur varchar2(2000); BEGIN text_c1 := ' '; if cid is not null then date_c1 := to_date (cid, 'mm/dd/yyyy hh:mi:ss'); sql_cur := 'select comments_2000 from ps_comments_tbl where random_cmmt_nbr = ' || rcn || ' and oprid = ' || chr(39) || oprid || chr(39) || ' and TO_CHAR(comment_id, ' || chr(39) || 'mm/dd/yyyy hh:mi:ss' || chr(39) || ')= ' || chr(39)|| cid || chr(39) ; -- dbms_output.put_line (sql_cur); execute immediate sql_cur into text_c1; text_c1 := substr(text_c1, 1, 254); end if; return text_c1; EXCEPTION WHEN OTHERS THEN sql_cur := 'select comments_2000 from ps_comments_tbl where random_cmmt_nbr = ' || rcn || ' and oprid = ' || chr(39) || oprid || chr(39) || ' and TO_CHAR(comment_id, ' || chr(39) || 'YYYY-MM-DD-HH24.MI.SS.'||chr(34)||'000000'||chr(34) || chr(39) || ')= ' || chr(39)|| cid || chr(39) ; -- dbms_output.put_line (sql_cur); execute immediate sql_cur into text_c1; text_c1 := substr(text_c1, 1, 254); RETURN text_c1; END zz_long_to_char; ------------------------------------------------------------ -------- Tokenizer -------- Takes in A string, Starting value, and seperator -------- OUT variables: Result Token, position of next token ----------------------------------------------------------- procedure zz_tokenizer ( iStart IN NUMBER, sPattern in VARCHAR2, sBuffer in VARCHAR2, sResult OUT VARCHAR2, iNextPos OUT NUMBER) AS nPos1 number; nPos2 number; BEGIN nPos1 := Instr (sBuffer ,sPattern ,iStart); IF nPos1 = 0 then sResult := NULL ; ELSE nPos2 := Instr (sBuffer ,sPattern ,nPos1 + 1); IF nPos2 = 0 then sResult := Rtrim(Ltrim(Substr(sBuffer ,nPos1+1))); iNextPos := nPos2; else sResult := Substr(sBuffer ,nPos1 + 1 , nPos2 - nPos1 - 1); iNextPos := nPos2; END IF; END IF; END zz_tokenizer ; -- -- End Package Body -- -------------------------------- END NSLIJHS_DBA; /