REM ptutil.sql REM (c)Go-Faster Consultancy 2009 set echo on spool ptutil-pkg -------------------------------------------------------------------------------------------------------- --ALTER SESSION SET PLSQL_CCFLAGS = 'debug:true'; CREATE OR REPLACE PACKAGE ptutil AS /*------------------------------------------------------------------------------------------------------ -- Searches and replaces strings in Application Engine steps, and can build a Application Designer project -- that can be used to migrate the changed steps -- Parameters -- p_aename - search for AE or SQL names that match - this is standard LIKE search -- p_search_for - search for this string in code - nb this is interpretted as a regular expression. -- Certain characters are interpretted as having special meaning in regular expressions and need to be escaped. -- p_replace_with - replace with this string -- p_match - defaults to 'i' indicating case insensitive search, c=case sensitive, n=allows period match any character, -- p_project - create/append to project updated objects -- m=treat source and multiple lines, x=ignore whitespace -- - see REGEXP_SUBSTR and REGEXP_REPLACE in Oracle documentation -------------------------------------------------------------------------------------------------------- -- 17.09.2009 - initial version -- 22.09.2009 - added database type join and additional subquery to supress duplicates -- 24.09.2009 - added option to create project --------------------------------------------------------------------------------------------------------*/ PROCEDURE ae_regexp_replace ( p_aename IN VARCHAR2 , p_search_for IN VARCHAR2 , p_replace_with IN VARCHAR2 , p_match IN VARCHAR2 DEFAULT 'i' , p_projectname IN VARCHAR2 DEFAULT NULL ); /*------------------------------------------------------------------------------------------------------ -- This procedure compares SQL text definition of views in PeopleSoft with that in Oracle database -- Parameters -- p_recname_pattern - The names of the views that will be compared will be like this parameter -- p_project_name_src - The view record to be compared are in the specified App Designer project -- p_project_name_out - Views that differ will be added to this project, views that are the same will be removed -- p_verbose - If true, the procedure emits addition debug code via dbms_output.putline -- p_async - if true, the procedure is submitted via DBMS_JOB to run immediately -- - for this option parameter job_queue_processes must be to set to greater than 0 -- NB: There are certain constructions that will always cause a view to fail comparison -- 1. use of %Operator or %OprClass macros, because these are expanded at build time in App Designer to -- the identity of the operator who is logged into App Designer. -- 2. Use of SELECT *. This is not good practice anyway. The view should be changed to provide -- an explicit lost of columns -------------------------------------------------------------------------------------------------------- -- 06.11.2009 - completed working version of compare_views -- 09.11.2009 - added asynchronous and scheduled capability --------------------------------------------------------------------------------------------------------*/ PROCEDURE view_compare ( p_recname_pattern IN VARCHAR2 DEFAULT '' , p_projectname_src IN VARCHAR2 DEFAULT '' , p_projectname_out IN VARCHAR2 DEFAULT 'REBUILDVIEWS' , p_verbose IN BOOLEAN DEFAULT FALSE , p_async IN BOOLEAN DEFAULT FALSE , p_next_date IN DATE DEFAULT SYSDATE); END ptutil; / show errors /*=======================================================================================================*/ CREATE OR REPLACE PACKAGE BODY ptutil AS g_toolsrel VARCHAR2(20); PROCEDURE psstatus IS BEGIN SELECT toolsrel INTO g_toolsrel FROM psstatus; END psstatus; PROCEDURE inc_version ( p_objecttypename IN VARCHAR2 ) IS BEGIN UPDATE psversion SET version = version + 1 WHERE objecttypename IN (p_objecttypename); UPDATE pslock SET version = version + 1 WHERE objecttypename IN (p_objecttypename); END inc_version; /*-------------------------------------------------------------------------------------------------------*/ PROCEDURE ins_project_recname (p_projectname IN VARCHAR2 ,p_recname IN VARCHAR2 ) IS BEGIN --insert project item: app engine step INSERT INTO psprojectitem (PROJECTNAME, OBJECTTYPE, OBJECTID1, OBJECTVALUE1, OBJECTID2, OBJECTVALUE2, OBJECTID3, OBJECTVALUE3, OBJECTID4, OBJECTVALUE4, NODETYPE, SOURCESTATUS, TARGETSTATUS, UPGRADEACTION, TAKEACTION, COPYDONE) VALUES (UPPER(p_projectname),0,1,UPPER(RTRIM(p_recname)), 0,' ',0,' ', 0,' ',0,0, 0,0,1,0); EXCEPTION WHEN dup_val_on_index THEN NULL; --don't worry if can't insert project item record END ins_project_recname; /*-------------------------------------------------------------------------------------------------------*/ PROCEDURE ins_project_sqlid (p_projectname IN VARCHAR2 ,p_sqlid IN VARCHAR2 ,p_sqltype IN VARCHAR2 --1 for view, 2 for AE ) IS BEGIN --insert project item: app engine step INSERT INTO psprojectitem (PROJECTNAME, OBJECTTYPE, OBJECTID1, OBJECTVALUE1, OBJECTID2, OBJECTVALUE2, OBJECTID3, OBJECTVALUE3, OBJECTID4, OBJECTVALUE4, NODETYPE, SOURCESTATUS, TARGETSTATUS, UPGRADEACTION, TAKEACTION, COPYDONE) VALUES (UPPER(p_projectname),30,65,p_sqlid, 81,p_sqltype,0,' ', 0,' ',0,0, 0,0,1,0); EXCEPTION WHEN dup_val_on_index THEN NULL; --don't worry if can't insert project item record END ins_project_sqlid; /*-------------------------------------------------------------------------------------------------------*/ PROCEDURE ins_project_view (p_projectname IN VARCHAR2 ,p_recname IN VARCHAR2 ) IS BEGIN ins_project_recname(p_projectname, p_recname); ins_project_sqlid(p_projectname, p_recname,'2'); END ins_project_view; /*-------------------------------------------------------------------------------------------------------*/ PROCEDURE ins_projectdefn ( p_projectname IN VARCHAR2 , p_descr IN VARCHAR2 , p_descrlong IN VARCHAR2 DEFAULT '' , p_inc_version IN BOOLEAN DEFAULT FALSE ) IS l_sql VARCHAR2(4000); l_version INTEGER; l_version2 INTEGER; BEGIN IF p_inc_version THEN inc_version('SYS'); inc_version('PJM'); END IF; SELECT version INTO l_version FROM PSLOCK WHERE objecttypename IN ('PJM') FOR UPDATE OF version; SELECT version INTO l_version2 FROM psversion WHERE objecttypename IN ('PJM') FOR UPDATE OF version; l_version := GREATEST(l_version,l_version2); l_version2 := l_version; l_sql := 'INSERT INTO psprojectdefn (' ||'VERSION, PROJECTNAME, TGTSERVERNAME, TGTDBNAME, TGTOPRID, ' ||'TGTOPRACCT, REPORTFILTER, TGTORIENTATION, COMPARETYPE, KEEPTGT, ' ||'COMMITLIMIT, MAINTPROJ, COMPRELEASE, COMPRELDTTM,' || 'OBJECTOWNERID, ' --added 8.48 ||'LASTUPDDTTM, LASTUPDOPRID, PROJECTDESCR, ' ||'RELEASELABEL, RELEASEDTTM, DESCRLONG) ' ||'VALUES (:1,:2,'' '','' '','' '',' ||''' '',16232832,0,1,3,' ||'50,0,'' '', null,' || ''' '',' --added 8.48 ||'sysdate,''PS'',:3, '' '', NULL, :4)'; EXECUTE IMMEDIATE l_sql USING l_version, UPPER(p_projectname), p_descr, NVL(p_descrlong,'Generated by ptutil at ')||TO_CHAR(SYSDATE,'hh24:mi:ss dd.mm.yyyy'); dbms_output.put_line('Created PeopleSoft Application Designer Project: '||UPPER(p_projectname)); EXCEPTION WHEN dup_val_on_index THEN --don't worry if can't insert project defn record IF p_inc_version THEN UPDATE psprojectdefn SET version = l_version, lastupddttm = SYSDATE , descrlong = NVL(p_descrlong,'Generated by ptutil at ')||TO_CHAR(SYSDATE,'hh24:mi:ss dd.mm.yyyy') WHERE projectname = UPPER(p_projectname); END IF; dbms_output.put_line('Appending to PeopleSoft Application Designer Project: '||UPPER(p_projectname)); END ins_projectdefn; /*-------------------------------------------------------------------------------------------------------*/ PROCEDURE ae_regexp_replace ( p_aename IN VARCHAR2 , p_search_for IN VARCHAR2 , p_replace_with IN VARCHAR2 , p_match IN VARCHAR2 DEFAULT 'i' , p_projectname IN VARCHAR2 DEFAULT NULL ) is CURSOR c_stmt IS SELECT s.ae_applid||'.'||s.ae_section||'.'||s.ae_step||'.'||s.ae_stmt_type identifier , t.sqlid, t.sqltype, t.market, t.dbtype, t.effdt, t.seqnum, t.sqltext, s.descrlong FROM psaestmtdefn s , pssqldefn q , pssqltextdefn t WHERE q.sqlid = t.sqlid AND q.sqltype = t.sqltype AND q.sqltype = '1' --app engine step AND q.sqlid = RPAD(s.ae_applid,12,' ')||RPAD(s.ae_section,8,' ')||RPAD(s.ae_step,8,' ')||s.ae_stmt_type AND t.dbtype = s.dbtype AND s.dbtype IN(' ','2') AND s.dbtype = ( SELECT MAX(s1.dbtype) FROM psaestmtdefn s1 WHERE s1.ae_applid = s.ae_applid AND s1.ae_section = s.ae_section AND s1.market = s.market AND s1.dbtype IN(' ','2') AND s1.effdt = s.effdt AND s1.ae_step = s.ae_step AND s1.ae_stmt_type = s.ae_stmt_type ) AND t.dbtype = ( SELECT MAX(t1.dbtype) FROM pssqltextdefn t1 WHERE t1.sqlid = t.sqlid AND t1.sqltype = t.sqltype AND t1.market = t.market AND t1.dbtype IN(' ','2')) /*------------------------------*/ AND s.ae_applid LIKE p_aename /*------------------------------*/ UNION ALL SELECT t.sqlid identifier , t.sqlid, t.sqltype, t.market, t.dbtype, t.effdt, t.seqnum, t.sqltext, t.sqltext FROM pssqldefn q , pssqltextdefn t WHERE q.sqlid = t.sqlid AND q.sqltype = t.sqltype AND q.sqltype = '0' --SQL AND t.dbtype = ( SELECT MAX(t1.dbtype) FROM pssqltextdefn t1 WHERE t1.sqlid = t.sqlid AND t1.sqltype = t.sqltype AND t1.market = t.market AND t1.dbtype IN(' ','2')) /*------------------------------*/ AND t.sqlid LIKE p_aename /*------------------------------*/ ; v_stmt c_stmt%ROWTYPE; l_stmt CLOB; l_descrlong CLOB; l_comment VARCHAR2(100); l_continue BOOLEAN; l_updated BOOLEAN; l_msg VARCHAR2(80); l_status VARCHAR2(1); /*E=Error, W=Warning, I=Infomation*/ l_count_stmts NUMBER := 0; l_count_updates NUMBER := 0; l_count_error NUMBER := 0; l_pos_id NUMBER; BEGIN l_comment := TO_CHAR(SYSDATE,'dd.mm.yyyy')|| ' replaced '''||p_search_for||''' with '''||p_replace_with||'''.'; SAVEPOINT ae_regexp_replace; inc_version('SYS'); inc_version('SRM'); IF p_projectname IS NOT NULL THEN inc_version('PJM'); inc_version('AES'); END IF; --increment version numbers -- to start transaction OPEN c_stmt; LOOP FETCH c_stmt INTO v_stmt; EXIT WHEN c_stmt%NOTFOUND; l_continue := TRUE; l_updated := FALSE; l_status := ''; l_stmt := v_stmt.sqltext; l_count_stmts := l_count_stmts + 1; IF dbms_lob.getlength(l_stmt) >= 32000 THEN l_msg := 'Statement Too long!'; l_status := 'E'; l_continue := FALSE; END IF; IF l_continue THEN /*---------------------------------------------------------------------------------------------------- dbms_output.put_line(v_stmt.identifier||': '||l_stmt); LOOP --repeatedly search string for multiple replacements l_pos_id := INSTR(l_ustmt,UPPER(p_search_for)); IF l_pos_id > 0 THEN --dbms_output.put_line(v_stmt.identifier||': found at '||l_pos_id||'!'); l_stmt := SUBSTR(l_stmt,1,l_pos_id-1)||p_replace_with||substr(l_stmt,l_pos_id+LENGTH(p_search_for)); l_updated := TRUE; ELSE l_continue := l_updated; EXIT; END IF; END LOOP; /*----------------------------------------------------------------------------------------------------*/ l_pos_id := REGEXP_INSTR(l_stmt,p_search_for,1,1,0,p_match); IF l_pos_id > 0 THEN --dbms_output.put_line(v_stmt.identifier||': first found at '||l_pos_id||'!'); l_stmt := REGEXP_REPLACE(l_stmt,p_search_for,p_replace_with,1,0,p_match); l_updated := TRUE; END IF; END IF; IF l_continue THEN IF l_updated THEN --update the sql statement dbms_output.put_line(v_stmt.identifier); dbms_output.put_line(l_stmt); UPDATE pssqltextdefn SET sqltext = l_stmt WHERE sqlid = v_stmt.sqlid AND sqltype = v_stmt.sqltype AND market = v_stmt.market AND dbtype = v_stmt.dbtype AND effdt = v_stmt.effdt AND seqnum = v_stmt.seqnum; --update version number of pssqldefn UPDATE pssqldefn SET version = (SELECT version FROM psversion WHERE objecttypename = 'SRM') WHERE sqlid = v_stmt.sqlid AND sqltype = v_stmt.sqltype; --insert/append step comment l_descrlong := v_stmt.descrlong; IF l_descrlong IS NOT NULL THEN l_descrlong := l_descrlong || CHR(13) || CHR(10); END IF; l_descrlong := l_descrlong || l_comment; IF dbms_lob.getlength(l_descrlong) >= 32000 THEN l_msg := 'Description very long!'; l_status := 'W'; l_continue := TRUE; END IF; IF v_stmt.sqltype = '1' THEN --add comment if AE UPDATE psaestmtdefn SET descrlong = l_descrlong WHERE sqlid = v_stmt.sqlid AND dbtype = v_stmt.dbtype; END IF; IF p_projectname IS NOT NULL THEN IF v_stmt.sqltype = '1' THEN --update version on AE section UPDATE psaesectdefn SET version = (SELECT version FROM psversion WHERE objecttypename = 'AES') WHERE ae_applid = RTRIM(SUBSTR(v_stmt.sqlid,1,12)) AND ae_section = RTRIM(SUBSTR(v_stmt.sqlid,13,8)); --insert section into project BEGIN --insert project item: app engine step INSERT INTO psprojectitem (PROJECTNAME, OBJECTTYPE, OBJECTID1, OBJECTVALUE1, OBJECTID2, OBJECTVALUE2, OBJECTID3, OBJECTVALUE3, OBJECTID4, OBJECTVALUE4, NODETYPE, SOURCESTATUS, TARGETSTATUS, UPGRADEACTION, TAKEACTION, COPYDONE) VALUES (UPPER(p_projectname),34,66,RTRIM(SUBSTR(v_stmt.sqlid,1,12)), 77,RTRIM(SUBSTR(v_stmt.sqlid,13,8)),0,' ', 0,' ',0,0, 0,0,1,0); EXCEPTION WHEN dup_val_on_index THEN NULL; --don't worry if can't insert project item record END; END IF; ins_project_sqlid(p_projectname,v_stmt.sqlid,'1'); --insert project item - SQL ID END IF; l_count_updates := l_count_updates + 1; END IF; ELSIF l_msg IS NOT NULL THEN dbms_output.put_line(v_stmt.identifier||': '||l_msg); IF l_status = 'E' THEN l_count_error := l_count_error + 1; END IF; END IF; END LOOP; CLOSE c_stmt; dbms_output.put_line('Number of statements:'||l_count_stmts); dbms_output.put_line('Number of updates :'||l_count_updates); dbms_output.put_line('Number of errors :'||l_count_error); ---------------------------------------------------------- IF p_projectname IS NOT NULL AND l_count_updates > 0 THEN ins_projectdefn(p_projectname, 'Partitioned + Global Temp Tabs', 'Search and Replace generated by PTUTIL.AE_REGEXP_REPLACE on '); END IF; IF l_count_updates>0 THEN dbms_output.put_line('***Uncommitted Updates - Please either commit or rollback***'); ELSE ROLLBACK TO SAVEPOINT ae_regexp_replace; END IF; END ae_regexp_replace; /*------------------------------------------------------------------------------------------------------- --starting at posid, count forward until bracket depth drops back down to 0 ---------------------------------------------------------------------------------------------------------*/ FUNCTION find_end_bracket (p_clob CLOB ,p_pos_id INTEGER --start position, before opening bracket ) RETURN INTEGER IS l_pos_id INTEGER; --working position l_length INTEGER; --length of clob l_char VARCHAR2(1); --character at position l_2char VARCHAR2(2); --character at position l_bracket_depth INTEGER := 0; l_inquote BOOLEAN := FALSE; l_incomment BOOLEAN := FALSE; BEGIN l_length := LENGTH(p_clob); l_pos_id := p_pos_id; LOOP l_2char := SUBSTR(p_clob,l_pos_id,2); l_char := SUBSTR(l_2char,1,1); IF l_char IS NULL THEN EXIT; --some kind of problem, abandon loop ELSIF l_char = '''' AND NOT l_incomment THEN --if not in a comment do quote processing l_inquote := NOT l_inquote; END IF; IF l_inquote THEN NULL; --if in a quote then no comment processing ELSIF l_2char = '/*' THEN l_incomment := TRUE; ELSIF l_2char = '*/' THEN l_incomment := FALSE; END IF; IF NOT l_inquote AND NOT l_incomment THEN IF l_char = '(' THEN l_bracket_depth := l_bracket_depth + 1; ELSIF l_char = ')' THEN l_bracket_depth := l_bracket_depth - 1; IF l_bracket_depth <= 0 THEN --stop when find a bracket too EXIT; --we have returned to the small bracket nesting depth END IF; END IF; NULL; END IF; l_pos_id := l_pos_id + 1; IF l_pos_id > l_length THEN EXIT; --we have run off the end of the string END IF; END LOOP; RETURN l_pos_id; END find_end_bracket; /*------------------------------------------------------------------------------------------------------- --starting at posid, count forward until find character at depth ---------------------------------------------------------------------------------------------------------*/ FUNCTION find_char_at_depth (p_clob CLOB ,p_pos_id INTEGER --start position, before opening bracket ,p_search_char VARCHAR --character we are searching for ,p_search_level INTEGER DEFAULT 0 --bracket level we are searching at ) RETURN INTEGER IS l_pos_id INTEGER; --working position l_length INTEGER; --length of clob l_char VARCHAR2(1); --character at position l_2char VARCHAR2(2); --2 characters at position l_bracket_depth INTEGER := 0; l_inquote BOOLEAN := FALSE; l_incomment BOOLEAN := FALSE; BEGIN l_length := LENGTH(p_clob); l_pos_id := p_pos_id; LOOP l_2char := SUBSTR(p_clob,l_pos_id,2); l_char := SUBSTR(l_2char,1,1); IF l_char IS NULL THEN EXIT; --some kind of problem, abandon loop ELSIF l_char = '''' AND NOT l_incomment THEN --if not in a comment do quote processing l_inquote := NOT l_inquote; END IF; IF l_inquote THEN NULL; --if in a quote then no comment processing ELSIF l_2char = '/*' THEN l_incomment := TRUE; ELSIF l_2char = '*/' THEN l_incomment := FALSE; END IF; IF NOT l_inquote AND NOT l_incomment THEN IF l_char = p_search_char AND l_bracket_depth = p_search_level THEN EXIT; ELSIF l_char = '(' THEN l_bracket_depth := l_bracket_depth + 1; ELSIF l_char = ')' THEN l_bracket_depth := l_bracket_depth - 1; IF l_bracket_depth < 0 THEN --stop when find a bracket too far EXIT; --we have returned to the small bracket nesting depth END IF; END IF; END IF; l_pos_id := l_pos_id + 1; IF l_pos_id > l_length THEN EXIT; --we have run off the end of the string END IF; END LOOP; RETURN l_pos_id; END find_char_at_depth; /*-------------------------------------------------------------------------------------------------------*/ FUNCTION unregularise ( p_string IN VARCHAR2 ) RETURN VARCHAR2 IS BEGIN RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( p_string ,'(','\(') ,')','\)') ,'|','\|') ,'.','\.') ,'+','\+') ,'*','\*'); END unregularise; /*-------------------------------------------------------------------------------------------------------*/ FUNCTION single_space (p_clob IN CLOB ) RETURN CLOB IS l_clob CLOB := ''; l_length INTEGER; l_pos_id INTEGER := 1; l_char VARCHAR(1); l_2char VARCHAR2(2); --2 characters at position l_inquote BOOLEAN := FALSE; l_incomment BOOLEAN := FALSE; l_found_alpha BOOLEAN := FALSE; --set true when find first alpha numeric l_lastchar VARCHAR(1); BEGIN --dbms_output.put_line('Before Single Space:'||p_clob); l_length := LENGTH(p_clob); LOOP IF l_pos_id <= l_length THEN l_lastchar := l_char; l_2char := SUBSTR(p_clob,l_pos_id,2); l_char := SUBSTR(l_2char,1,1); IF l_char IS NULL THEN NULL; ELSIF l_char = '''' AND NOT l_incomment THEN l_inquote := NOT l_inquote; END IF; IF l_inquote THEN NULL; --if in a quote then no comment processing ELSIF l_2char = '/*' THEN l_incomment := TRUE; END IF; IF l_char IN(CHR(9),CHR(10),CHR(13)) THEN l_char := ' '; END IF; IF NOT l_found_alpha AND NOT l_incomment AND l_char IS NOT NULL AND l_char != ' ' THEN l_found_alpha := TRUE; END IF; IF l_char = ' ' AND (l_lastchar = ' ' OR l_pos_id = 1) AND NOT l_inquote THEN NULL; ELSIF l_found_alpha THEN l_clob := l_clob || l_char; END IF; IF l_inquote THEN NULL; --if in a quote then no comment processing ELSIF l_2char = '*/' THEN l_incomment := FALSE; IF l_found_alpha THEN l_clob := l_clob || '/'; END IF; l_pos_id := l_pos_id + 1; END IF; l_pos_id := l_pos_id + 1; ELSE EXIT; END IF; END LOOP; --dbms_output.put_line('After Single Space :'||l_clob); RETURN RTRIM(l_clob); END single_space; /*-------------------------------------------------------------------------------------------------------*/ FUNCTION get_subrec ( p_recname IN VARCHAR2 , p_alias IN VARCHAR2 ) RETURN CLOB IS l_clob CLOB; l_counter INTEGER := 0; BEGIN FOR i IN ( SELECT fieldname FROM psrecfielddb WHERE recname = UPPER(p_recname) ORDER BY fieldnum ) LOOP l_counter := l_counter + 1; IF l_counter > 1 THEN l_clob := l_clob || ', '; END IF; IF p_alias IS NOT NULL THEN l_clob := l_clob || p_alias || '.' ; END IF; l_clob := l_clob || i.fieldname; END LOOP; RETURN l_clob; END get_subrec; /*-------------------------------------------------------------------------------------------------------*/ FUNCTION get_EffdtCheck ( p_recname IN VARCHAR2 , p_external_alias IN VARCHAR2 , p_effdt IN VARCHAR2 ) RETURN CLOB IS l_clob CLOB; l_counter INTEGER := 0; l_sqltablename VARCHAR2(18); l_recname VARCHAR2(15); l_internal_alias VARCHAR2(15); l_pos_id INTEGER; --sort by fieldnum unless CURSOR c_keys (p_recname VARCHAR2) IS SELECT f.fieldname , ROW_NUMBER() OVER (ORDER BY DECODE(f.fieldname,'EFFDT',1,0), NVL(k.keyposn, f.fieldnum)) seq FROM psrecfielddb f LEFT OUTER JOIN pskeydefn k ON f.fieldname = k.fieldname AND f.recname_parent = k.recname AND k.indexid = '_' WHERE MOD(f.useedit,4) != 0 --bits 0(unique) or 1(duplicate key) AND f.recname = p_recname ORDER BY seq; v_keys c_keys%ROWTYPE; BEGIN --dbms_output.put_line('getEffdtCheck('||p_recname||','||p_external_alias||','||p_effdt||')'); l_pos_id := INSTR(p_recname,' '); IF l_pos_id > 0 THEN l_recname := LTRIM(RTRIM(SUBSTR(p_recname,1,l_pos_id-1))); l_internal_alias := LTRIM(RTRIM(SUBSTR(p_recname,l_pos_id+1))); --dbms_output.put_line('getEffdtCheck('||l_recname||','||l_internal_alias||')'); ELSE l_recname := LTRIM(RTRIM(p_recname)); l_internal_alias := l_recname; END IF; BEGIN SELECT DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) INTO l_sqltablename FROM psrecdefn r WHERE r.recname = UPPER(RTRIM(l_recname)); EXCEPTION WHEN no_data_found THEN l_sqltablename := UPPER('PS_'||l_recname); END; l_clob := 'FROM '||l_sqltablename||' '||l_internal_alias; OPEN c_keys(l_recname); LOOP FETCH c_keys INTO v_keys; EXIT WHEN c_keys%NOTFOUND; IF v_keys.fieldname = 'EFFDT' THEN l_clob := p_external_alias||'.'||v_keys.fieldname||'=(SELECT MAX('||v_keys.fieldname||') ' ||l_clob ||' AND '||l_internal_alias||'.'||v_keys.fieldname||'<='||p_effdt||')'; ELSE IF v_keys.seq = 1 THEN l_clob := l_clob||' WHERE '; ELSE l_clob := l_clob||' AND '; END IF; l_clob := l_clob||l_internal_alias||'.'||v_keys.fieldname||'='||p_external_alias||'.'||v_keys.fieldname; END IF; END LOOP; CLOSE c_keys; RETURN l_clob; END get_EffdtCheck; /*-------------------------------------------------------------------------------------------------------*/ PROCEDURE coalesce (p_clob IN OUT CLOB ) IS l_macro_name VARCHAR2(10) := 'Coalesce'; l_pos_id1 INTEGER; l_pos_id2 INTEGER; l_pos_id3 INTEGER; l_pos_id4 INTEGER; l_sqltext2 CLOB; l_sqlexpr VARCHAR2(1000); /*name of referenced SQL object, including paramters*/ l_counter INTEGER; l_param1 VARCHAR2(1000); l_param2 VARCHAR2(1000); BEGIN l_pos_id1 := 1; --start search at first character LOOP --repeatedly search for %Coalesce( l_counter := 0; l_sqltext2 := ''; l_pos_id1 := REGEXP_INSTR(p_clob,'\%'||l_macro_name||' *\(',l_pos_id1,1,1,'i'); IF l_pos_id1 > 0 THEN l_pos_id2 := find_end_bracket(p_clob,l_pos_id1-1); IF l_pos_id2 > 0 THEN l_sqlexpr := SUBSTR(p_clob,l_pos_id1,l_pos_id2-l_pos_id1); --dbms_output.put_line('Bracketted expression %'||l_macro_name||'('||l_sqlexpr||')'); l_pos_id2 := 1; LOOP l_counter := l_counter + 1; l_pos_id3 := find_char_at_depth(l_sqlexpr,l_pos_id2,','); l_pos_id4 := find_char_at_depth(l_sqlexpr,l_pos_id3,','); IF l_pos_id3 > l_pos_id2 THEN IF l_pos_id4 > l_pos_id3 THEN l_param1 := SUBSTR(l_sqlexpr,l_pos_id2,l_pos_id3-l_pos_id2); l_param2 := SUBSTR(l_sqlexpr,l_pos_id3+1,l_pos_id4-l_pos_id3-1); l_sqltext2 := l_sqltext2||'NVL('||l_param1||','; ELSIF l_pos_id4 = l_pos_id3 THEN l_param1 := SUBSTR(l_sqlexpr,l_pos_id2,l_pos_id3-l_pos_id2); l_param2 := SUBSTR(l_sqlexpr,l_pos_id3+1,l_pos_id4); l_sqltext2 := l_sqltext2||'NVL('||l_param1||','||l_param2||RPAD(')',l_counter,')'); EXIT; END IF; --dbms_output.put_line('Coalesce('||l_counter||':'||l_pos_id2||','||l_pos_id3||','||l_pos_id4||')'||l_sqltext2); END IF; l_pos_id2 := l_pos_id3+1; END LOOP; --dbms_output.put_line('Coalesce('||l_counter||':'||l_pos_id2||','||l_pos_id3||','||l_pos_id4||')'||l_sqltext2); l_sqlexpr := unregularise(l_sqlexpr); p_clob := REGEXP_REPLACE(p_clob,'\%'||l_macro_name||' *\('||l_sqlexpr||'\)',l_sqltext2,1,0,'i'); ELSE EXIT; --cannot find end of %SQL( bracket, just abandon END IF; ELSE EXIT; --cannot find %SQL( END IF; END LOOP; END coalesce; /*-------------------------------------------------------------------------------------------------------*/ FUNCTION get_join ( p_join_type IN VARCHAR2 , p_recname1 IN VARCHAR2 , p_recname2 IN VARCHAR2 , p_exclusion IN VARCHAR2 ) RETURN CLOB IS l_clob CLOB; l_counter INTEGER := 0; l_recname1 VARCHAR2(15); l_alias1 VARCHAR2(15); l_recname2 VARCHAR2(15); l_alias2 VARCHAR2(15); l_pos_id1 INTEGER; l_pos_id2 INTEGER; l_keyword VARCHAR2(5); l_exclusion VARCHAR2(100); k_common_keys CONSTANT VARCHAR2(20) := 'COMMON_KEYS'; k_common_fields CONSTANT VARCHAR2(20) := 'COMMON_FIELDS'; l_sql VARCHAR2(1000); TYPE t_sql IS REF CURSOR; c_fields t_sql; p_seq INTEGER; p_fieldname psrecfielddb.fieldname%TYPE; p_fieldtype psdbfield.fieldtype%TYPE; p_useedit psrecfielddb.useedit%TYPE; BEGIN --dbms_output.put_line('getJoin('||p_join_type||','||p_recname1||','||p_recname2||')'); l_pos_id1 := INSTR(p_recname1,' '); IF l_pos_id1 > 0 THEN l_recname1 := LTRIM(RTRIM(SUBSTR(p_recname1,1,l_pos_id1-1))); l_alias1 := LTRIM(RTRIM(SUBSTR(p_recname1,l_pos_id1+1))); ELSE l_recname1 := LTRIM(RTRIM(p_recname1)); l_alias1 := l_recname1; END IF; l_pos_id1 := INSTR(p_recname2,' '); IF l_pos_id1 > 0 THEN l_recname2 := LTRIM(RTRIM(SUBSTR(p_recname2,1,l_pos_id1-1))); l_alias2 := LTRIM(RTRIM(SUBSTR(p_recname2,l_pos_id1+1))); ELSE l_recname2 := LTRIM(RTRIM(p_recname2)); l_alias2 := l_recname2; END IF; IF p_exclusion IS NOT NULL THEN l_exclusion := ' AND NOT f.fieldname IN('; l_pos_id1 := 1; LOOP l_pos_id2 := INSTR(p_exclusion,',',l_pos_id1,1); --find next , IF l_pos_id2>l_pos_id1 THEN l_exclusion := l_exclusion||''''||LTRIM(RTRIM(SUBSTR(p_exclusion,l_pos_id1,l_pos_id2-l_pos_id1)))||''','; ELSE l_exclusion := l_exclusion||''''||LTRIM(RTRIM(SUBSTR(p_exclusion,l_pos_id1)))||''''; END IF; IF l_pos_id2 > 0 THEN l_pos_id1 := l_pos_id2 + 1; ELSE EXIT; END IF; END LOOP; l_exclusion := l_exclusion||')'; END IF; --dbms_output.put_line('Exclusion:'||l_exclusion); l_sql := 'SELECT f1.fieldname,' ||CASE p_join_type WHEN k_common_keys THEN 'ROW_NUMBER() OVER (ORDER BY NVL(k1.keyposn, f1.fieldnum))' WHEN k_common_fields THEN 'f1.fieldnum' END ||' seq, f.fieldtype, f1.useedit FROM psrecfielddb f1' ||CASE p_join_type WHEN k_common_keys THEN ' LEFT OUTER JOIN pskeydefn k1 ON f1.fieldname = k1.fieldname AND f1.recname_parent = k1.recname AND k1.indexid = ''_''' END ||',psrecfielddb f2' ||CASE p_join_type WHEN k_common_keys THEN ' LEFT OUTER JOIN pskeydefn k2 ON f2.fieldname = k2.fieldname AND f2.recname_parent = k2.recname AND k2.indexid = ''_''' END ||',psdbfield f WHERE f1.recname = :1 AND f2.recname = :2' ||CASE p_join_type WHEN k_common_keys THEN ' AND MOD(f1.useedit,4)!= 0 AND MOD(f2.useedit,4)!= 0' END ||' AND f1.fieldname = f2.fieldname AND f.fieldname = f1.fieldname' ||l_exclusion||' ORDER BY seq'; --dbms_output.put_line('Dynamic SQL:'||l_sql); OPEN c_fields FOR l_sql USING l_recname1, l_recname2; LOOP FETCH c_fields INTO p_fieldname, p_seq, p_fieldtype, p_useedit; EXIT WHEN c_fields%NOTFOUND; --dbms_output.put_line('Field:'||p_seq||p_fieldname); IF p_fieldtype IN(4,5,6) THEN l_clob := l_clob||l_keyword||'('||NVL(l_alias1,l_recname1)||'.'||p_fieldname||' = '||NVL(l_alias2,l_recname2)||'.'||p_fieldname; IF BITAND(p_useedit,POWER(2,8)) = 0 THEN --if bit 6 is NOT set l_clob := l_clob||' OR ('||NVL(l_alias1,l_recname1)||'.'||p_fieldname||' IS NULL AND '||NVL(l_alias2,l_recname2)||'.'||p_fieldname||' IS NULL)'; END IF; l_clob := l_clob||')'; ELSE l_clob := l_clob||l_keyword||NVL(l_alias1,l_recname1)||'.'||p_fieldname||' = '||NVL(l_alias2,l_recname2)||'.'||p_fieldname; END IF; l_keyword := ' AND '; END LOOP; CLOSE c_fields; RETURN l_clob; END get_join; /*-------------------------------------------------------------------------------------------------------*/ PROCEDURE join (p_clob IN OUT CLOB ) IS l_macro_name VARCHAR2(10) := 'Join'; l_pos_id1 INTEGER; l_pos_id2 INTEGER; l_pos_id3 INTEGER; l_pos_id4 INTEGER; l_sqltext2 CLOB := ''; l_sqlexpr VARCHAR2(1000); /*name of referenced SQL object, including paramters*/ l_param1 VARCHAR2(30); l_param2 VARCHAR2(30); l_param3 VARCHAR2(30); l_param4 VARCHAR2(30); BEGIN l_pos_id1 := 1; --start search at first character LOOP --repeatedly search for %TextIn( l_pos_id1 := REGEXP_INSTR(p_clob,'\%'||l_macro_name||' *\(',l_pos_id1,1,1,'i'); IF l_pos_id1 > 0 THEN l_pos_id2 := find_end_bracket(p_clob,l_pos_id1-1); IF l_pos_id2 > 0 THEN l_sqlexpr := SUBSTR(p_clob,l_pos_id1,l_pos_id2-l_pos_id1); --dbms_output.put_line('Bracketted expression %'||l_macro_name||': '||l_sqlexpr); --now find the first comma to see if there are parameters on the %SQL l_pos_id2 := INSTR(l_sqlexpr,',',1,1); l_pos_id3 := INSTR(l_sqlexpr,',',1,2); l_pos_id4 := INSTR(l_sqlexpr,',',1,3); IF l_pos_id2 > 0 THEN l_param1 := LTRIM(RTRIM(SUBSTR(l_sqlexpr,1,l_pos_id2-1))); IF l_pos_id3 > l_pos_id2 THEN l_param2 := LTRIM(RTRIM(SUBSTR(l_sqlexpr,l_pos_id2+1,l_pos_id3-l_pos_id2-1))); --don't rtrim alias IF l_pos_id4 > l_pos_id3 THEN l_param3 := LTRIM(RTRIM(SUBSTR(l_sqlexpr,l_pos_id3+1,l_pos_id4-l_pos_id3-1))); --don't rtrim alias l_param4 := LTRIM(RTRIM(SUBSTR(l_sqlexpr,l_pos_id4+1))); ELSE l_param3 := LTRIM(RTRIM(SUBSTR(l_sqlexpr,l_pos_id3+1))); END IF; ELSE l_param2 := LTRIM(RTRIM(SUBSTR(l_sqlexpr,l_pos_id2+1))); END IF; END IF; --dbms_output.put_line('@'||l_pos_id1||','||l_pos_id2||','||l_pos_id3||','||l_pos_id4||':'||l_sqlexpr); l_sqltext2 := get_Join(l_param1,l_param2,l_param3,l_param4); --get the referenced Joinord l_sqlexpr := unregularise(l_sqlexpr); p_clob := REGEXP_REPLACE(p_clob,'\%'||l_macro_name||' *\('||l_sqlexpr||'\)',l_sqltext2,1,0,'i'); ELSE EXIT; --cannot find end of %SQL( bracket, just abandon END IF; ELSE EXIT; --cannot find %SQL( END IF; END LOOP; END join; /*-------------------------------------------------------------------------------------------------------*/ PROCEDURE one_param (p_clob IN OUT CLOB ,p_macro_name IN VARCHAR2 ) IS l_pos_id1 INTEGER; l_pos_id2 INTEGER; l_sqltext2 CLOB := ''; l_sqlexpr VARCHAR2(1000); /*name of referenced SQL object, including paramters*/ BEGIN l_pos_id1 := 1; --start search at first character LOOP --repeatedly search for %TextIn( l_pos_id1 := REGEXP_INSTR(p_clob,'\%'||p_macro_name||' *\(',l_pos_id1,1,1,'i'); IF l_pos_id1 > 0 THEN l_pos_id2 := find_end_bracket(p_clob,l_pos_id1-1); IF l_pos_id2 > 0 THEN l_sqlexpr := SUBSTR(p_clob,l_pos_id1,l_pos_id2-l_pos_id1); --dbms_output.put_line('Bracketted expression %'||p_macro_name||': >>'||l_sqlexpr||'<<'); IF p_macro_name = 'Date' THEN l_sqltext2 := LTRIM(RTRIM(l_sqlexpr)); ELSIF p_macro_name = 'DateIn' THEN l_sqltext2 := 'TO_DATE('||LTRIM(RTRIM(l_sqlexpr))||',''YYYY-MM-DD'')'; ELSIF p_macro_name = 'DateOut' THEN l_sqltext2 := 'TO_CHAR('||LTRIM(RTRIM(l_sqlexpr))||',''YYYY-MM-DD'')'; ELSIF p_macro_name = 'DatePart' THEN l_sqltext2 := 'TO_DATE(TO_CHAR('||LTRIM(RTRIM(l_sqlexpr))||',''YYYY-MM-DD''),''YYYY-MM-DD'')'; ELSIF p_macro_name = 'NumToChar' THEN l_sqltext2 := 'TO_CHAR('||LTRIM(RTRIM(l_sqlexpr))||')'; ELSIF p_macro_name = 'Round' THEN l_sqltext2 := 'ROUND('||RTRIM(l_sqlexpr)||')'; ELSIF p_macro_name = 'Substring' THEN l_sqltext2 := 'SUBSTR('||LTRIM(RTRIM(l_sqlexpr))||')'; ELSIF p_macro_name = 'Table' THEN BEGIN SELECT DECODE(sqltablename,' ','PS_'||recname,sqltablename) INTO l_sqltext2 FROM psrecdefn WHERE recname = UPPER(RTRIM(l_sqlexpr)); EXCEPTION WHEN no_data_found THEN l_sqltext2 := l_sqlexpr; END; ELSIF p_macro_name = 'TextIn' THEN l_sqltext2 := RTRIM(l_sqlexpr); ELSIF p_macro_name = 'TimePart' THEN IF g_toolsrel <= '8.48' THEN l_sqltext2 := 'DECODE('||LTRIM(RTRIM(l_sqlexpr))||', NULL, TO_DATE(NULL), TO_DATE(''1900-01-01-'' || TO_CHAR(' ||LTRIM(RTRIM(l_sqlexpr))||',''HH24.MI.SS''), ''YYYY-MM-DD-HH24.MI.SS''))'; --pt8.48?? ELSIF g_toolsrel >= '8.49' THEN l_sqltext2 := 'DECODE('||LTRIM(RTRIM(l_sqlexpr))||', NULL, TO_TIMESTAMP(NULL), TO_TIMESTAMP(''1900-01-01-'' || TO_CHAR(CAST((' ||LTRIM(RTRIM(l_sqlexpr))||') AS TIMESTAMP),''HH24.MI.SS.FF''), ''YYYY-MM-DD-HH24.MI.SS.FF''))'; --pt8.48?? END IF; ELSIF p_macro_name = 'TrimSubstr' THEN l_sqltext2 := 'RTRIM(SUBSTR('||l_sqlexpr||'))'; ELSIF p_macro_name = 'Upper' THEN l_sqltext2 := 'UPPER('||RTRIM(l_sqlexpr)||')'; END IF; l_sqlexpr := unregularise(l_sqlexpr); p_clob := REGEXP_REPLACE(p_clob,'\%'||p_macro_name||' *\('||l_sqlexpr||'\)',l_sqltext2,1,0,'i'); ELSE EXIT; --cannot find end of %SQL( bracket, just abandon END IF; ELSE EXIT; --cannot find %SQL( END IF; END LOOP; END one_param; /*-------------------------------------------------------------------------------------------------------*/ PROCEDURE two_param (p_clob IN OUT CLOB ,p_macro_name IN VARCHAR2 ) IS l_pos_id1 INTEGER; l_pos_id2 INTEGER; l_pos_id3 INTEGER; l_sqltext2 CLOB := ''; l_sqlexpr VARCHAR2(1000); /*name of referenced SQL object, including paramters*/ l_part1 VARCHAR2(200); l_part2 VARCHAR2(200); BEGIN l_pos_id1 := 1; --start search at first character LOOP --repeatedly search for %TextIn( l_pos_id1 := REGEXP_INSTR(p_clob,'\%'||p_macro_name||' *\(',l_pos_id1,1,1,'i'); IF l_pos_id1 > 0 THEN l_pos_id2 := find_end_bracket(p_clob,l_pos_id1-1); IF l_pos_id2 > 0 THEN l_sqlexpr := SUBSTR(p_clob,l_pos_id1,l_pos_id2-l_pos_id1); --dbms_output.put_line('Bracketted expression %'||p_macro_name||': '||l_sqlexpr); l_pos_id3 := find_char_at_depth(l_sqlexpr,1,','); IF l_pos_id3 > 0 THEN l_part1 := SUBSTR(l_sqlexpr,1,l_pos_id3-1); l_part2 := SUBSTR(l_sqlexpr,l_pos_id3+1); IF p_macro_name = 'DateAdd' THEN l_sqltext2 := '(('||LTRIM(l_part1)||') + ('||RTRIM(l_part2)||'))'; ELSIF p_macro_name = 'DateDiff' THEN l_sqltext2 := '(('||RTRIM(l_part2)||') - ('||LTRIM(l_part1)||'))'; ELSIF p_macro_name = 'DecDiv' THEN l_sqltext2 := '(('||LTRIM(l_part1)||') / ('||RTRIM(l_part2)||'))'; ELSIF p_macro_name = 'DecMult' THEN l_sqltext2 := '(('||LTRIM(l_part1)||') * ('||RTRIM(l_part2)||'))'; ELSIF p_macro_name = 'DateTimeDiff' THEN IF g_toolsrel <= '8.48' THEN l_sqltext2 := 'ROUND((('||RTRIM(l_part2)||') - ('||LTRIM(l_part1)||')) * 1440, 0)'; --pt8.48 ELSIF g_toolsrel >= '8.49' THEN l_sqltext2 := 'ROUND((CAST(('||RTRIM(l_part2)||') AS DATE) - CAST(('||LTRIM(l_part1)||') AS DATE)) * 1440, 0)'; END IF; ELSIF p_macro_name = 'TimeAdd' THEN IF g_toolsrel <= '8.48' THEN l_sqltext2 := '(('||LTRIM(l_part1)||') + (('||RTRIM(l_part2)||')/1440))'; --pt8.48 ELSIF g_toolsrel >= '8.49' THEN l_sqltext2 := '(('||LTRIM(l_part1)||') + NUMTODSINTERVAL('||RTRIM(l_part2)||', ''MINUTE''))'; --pt8.49 END IF; END IF; END IF; l_sqlexpr := unregularise(l_sqlexpr); p_clob := REGEXP_REPLACE(p_clob,'\%'||p_macro_name||' *\('||l_sqlexpr||'\)',l_sqltext2,1,0,'i'); ELSE EXIT; --cannot find end of %SQL( bracket, just abandon END IF; ELSE EXIT; --cannot find %SQL( END IF; END LOOP; END two_param; /*-------------------------------------------------------------------------------------------------------*/ FUNCTION get_pssqltext ( p_sqlid IN VARCHAR2 , p_sqltype IN VARCHAR2 ) RETURN CLOB IS CURSOR c_sqltext (p_sqlid VARCHAR2, p_sqltype VARCHAR2) IS SELECT st.sqltext FROM pssqltextdefn st WHERE st.sqlid = p_sqlid AND st.sqltype = p_sqltype AND st.market = 'GBL' AND st.dbtype IN(' ','2') AND st.dbtype = ( SELECT MAX(st1.dbtype) FROM pssqltextdefn st1 WHERE st1.sqlid = st.sqlid AND st1.sqltype = st.sqltype AND st1.market = 'GBL' AND st1.dbtype IN(' ','2')) ORDER BY st.seqnum; v_sqltext c_sqltext%ROWTYPE; l_sqltext CLOB := ''; l_sqltext2 CLOB := ''; l_pos_id1 INTEGER; l_pos_id2 INTEGER; l_pos_id3 INTEGER; l_sqlexpr VARCHAR2(1000); /*name of referenced SQL object, including paramters*/ l_sqlid VARCHAR2(30); /*name of referenced SQL object only*/ l_alias VARCHAR2(15); /*sql table alias*/ l_sqlparmnum INTEGER; l_sqlparm VARCHAR2(100); /*substituion parameter*/ BEGIN --dbms_output.put_line('Processing:'||p_sqlid); --need to use old fashion loop on long, and concatenate sections OPEN c_sqltext(p_sqlid, p_sqltype); LOOP FETCH c_sqltext INTO v_sqltext; EXIT WHEN c_sqltext%NOTFOUND; l_sqltext := l_sqltext || v_sqltext.sqltext; END LOOP; --dbms_output.put_line(l_sqltext); --cater for oracle cleaning up view text during DDL --l_sqltext := TRANSLATE(l_sqltext,CHR(9)||CHR(10)||CHR(13),' '); --moved into single space IF INSTR(l_sqltext,'%')>0 THEN --recursively expand SQL objects l_pos_id1 := 1; --start search at first character LOOP --repeatedly search string for %SQL(...) l_pos_id1 := REGEXP_INSTR(l_sqltext,'\%Sql *\(',l_pos_id1,1,1,'i'); IF l_pos_id1 > 0 THEN l_pos_id2 := find_end_bracket(l_sqltext,l_pos_id1-1); IF l_pos_id2 > 0 THEN l_sqlexpr := SUBSTR(l_sqltext,l_pos_id1,l_pos_id2-l_pos_id1); --dbms_output.put_line('Found %SQL @'||l_pos_id1||'-'||l_pos_id2||':'||l_sqlexpr); l_pos_id1 := l_pos_id1+1; --start next search at end bracket --now find the first comma to see if there are parameters on the %SQL l_pos_id2 := find_char_at_depth(l_sqlexpr,1,','); IF l_pos_id2 > 0 THEN l_sqlid := UPPER(LTRIM(RTRIM(SUBSTR(l_sqlexpr,1,l_pos_id2-1)))); ELSE l_sqlid := UPPER(LTRIM(RTRIM(l_sqlexpr))); END IF; l_sqltext2 := get_pssqltext(l_sqlid,'0'); --get name of referenced object IF l_pos_id2 > 0 THEN --process the paramter l_sqlparmnum := 1; LOOP l_pos_id3 := find_char_at_depth(l_sqlexpr,l_pos_id2+1,','); IF l_pos_id3 > l_pos_id2 THEN l_sqlparm := RTRIM(SUBSTR(l_sqlexpr,l_pos_id2+1,l_pos_id3-1-l_pos_id2)); ELSE l_sqlparm := RTRIM(SUBSTR(l_sqlexpr,l_pos_id2+1)); END IF; l_sqltext2 := REGEXP_REPLACE(l_sqltext2,'%P\('||l_sqlparmnum||'\)',l_sqlparm,1,0,'i'); --dbms_output.put_line('Parameter '||l_sqlparmnum||'('||l_pos_id2||','||l_pos_id3||')='||l_sqlparm||'=>'||l_sqltext2); IF l_sqlparmnum > 10 OR l_pos_id3 <= l_pos_id2+1 THEN EXIT; --stop infinite loop ELSE l_pos_id2 := l_pos_id3; --move start of next param search up END IF; l_sqlparmnum := l_sqlparmnum + 1; END LOOP; END IF; l_sqlexpr := unregularise(l_sqlexpr); --dbms_output.put_line(l_sqlid||'='||l_sqltext2); l_sqltext := REGEXP_REPLACE(l_sqltext,'%SQL *\('||l_sqlexpr||'\)',l_sqltext2,1,0,'i'); ELSE EXIT; --cannot find end of %SQL( bracket, just abandon END IF; ELSE EXIT; --cannot find %SQL( END IF; END LOOP; coalesce(l_sqltext); one_param(l_sqltext,'Date'); two_param(l_sqltext,'DateAdd'); two_param(l_sqltext,'DateDiff'); one_param(l_sqltext,'DateIn'); one_param(l_sqltext,'DateOut'); one_param(l_sqltext,'DatePart'); two_param(l_sqltext,'DateTimeDiff'); two_param(l_sqltext,'DecDiv'); two_param(l_sqltext,'DecMult'); --%effdtcheck l_pos_id1 := 1; --start search at first character LOOP --repeatedly search for %EffdtCheck( l_pos_id1 := REGEXP_INSTR(l_sqltext,'\%EffdtCheck *\(',l_pos_id1,1,1,'i'); IF l_pos_id1 > 0 THEN l_pos_id2 := find_end_bracket(l_sqltext,l_pos_id1-1); IF l_pos_id2 > 0 THEN l_sqlexpr := SUBSTR(l_sqltext,l_pos_id1,l_pos_id2-l_pos_id1); --dbms_output.put_line('@'||l_pos_id1||','||l_pos_id2||':'||l_sqlexpr); --l_pos_id1 := l_pos_id2; --start next search at end bracket --now find the first comma to see if there are parameters on the %SQL l_pos_id2 := INSTR(l_sqlexpr,',',1,1); l_pos_id3 := INSTR(l_sqlexpr,',',1,2); IF l_pos_id2 > 0 THEN l_sqlid := LTRIM(RTRIM(SUBSTR(l_sqlexpr,1,l_pos_id2-1))); IF l_pos_id3 > l_pos_id2 THEN l_alias := LTRIM(SUBSTR(l_sqlexpr,l_pos_id2+1,l_pos_id3-l_pos_id2-1)); --don't rtrim alias l_sqlparm := LTRIM(RTRIM(SUBSTR(l_sqlexpr,l_pos_id3+1))); END IF; END IF; l_sqltext2 := get_EffdtCheck(l_sqlid,l_alias,l_sqlparm); --get the referenced EffdtCheckord --dbms_output.put_line(l_sqlid||'('||l_sqlparm||')='||l_sqltext2); l_sqlexpr := unregularise(l_sqlexpr); l_sqltext := REGEXP_REPLACE(l_sqltext,'%EffdtCheck *\('||l_sqlexpr||'\)',l_sqltext2,1,0,'i'); ELSE EXIT; --cannot find end of %SQL( bracket, just abandon END IF; ELSE EXIT; --cannot find %EffdtCheck( END IF; END LOOP; join(l_sqltext); one_param(l_sqltext,'NumToChar'); two_param(l_sqltext,'TimeAdd'); one_param(l_sqltext,'Round'); one_param(l_sqltext,'Substring'); one_param(l_sqltext,'Table'); one_param(l_sqltext,'TextIn'); one_param(l_sqltext,'TimePart'); one_param(l_sqltext,'TrimSubstr'); one_param(l_sqltext,'Upper'); --subrecords l_pos_id1 := 1; --start search at first character LOOP --repeatedly search for %Subrec( l_pos_id1 := REGEXP_INSTR(l_sqltext,'\%Subrec *\(',l_pos_id1,1,1,'i'); IF l_pos_id1 > 0 THEN l_pos_id2 := find_end_bracket(l_sqltext,l_pos_id1-1); IF l_pos_id2 > 0 THEN l_sqlexpr := SUBSTR(l_sqltext,l_pos_id1,l_pos_id2-l_pos_id1); --dbms_output.put_line('@'||l_pos_id1||','||l_pos_id2||':'||l_sqlexpr); --l_pos_id1 := l_pos_id2; --start next search at end bracket --now find the first comma to see if there are parameters on the %SQL l_pos_id2 := INSTR(l_sqlexpr,',',1); IF l_pos_id2 > 0 THEN l_sqlid := UPPER(LTRIM(RTRIM(SUBSTR(l_sqlexpr,1,l_pos_id2-1)))); l_alias := LTRIM(RTRIM(SUBSTR(l_sqlexpr,l_pos_id2+1))); ELSE l_sqlid := UPPER(LTRIM(RTRIM(l_sqlexpr))); l_alias := ''; -- there is no parameter END IF; l_sqltext2 := get_subrec(l_sqlid,l_alias); --get the referenced subrecord --dbms_output.put_line(l_sqlid||'('||l_alias||')='||l_sqltext2); l_sqlexpr := unregularise(l_sqlexpr); l_sqltext := REGEXP_REPLACE(l_sqltext,'%Subrec *\('||l_sqlexpr||'\)',l_sqltext2,1,0,'i'); ELSE EXIT; --cannot find end of %SQL( bracket, just abandon END IF; ELSE EXIT; --cannot find %SQL( END IF; END LOOP; --l_sqltext := REPLACE(l_sqltext,CHR(13),CHR(32)); --l_sqltext := REPLACE(l_sqltext,CHR(10),CHR(32)); --l_sqltext := REPLACE(l_sqltext,CHR(9),CHR(32)); --substitute fixed macro l_sqltext := REGEXP_REPLACE(l_sqltext,'%Concat','||',1,0,'i'); l_sqltext := REGEXP_REPLACE(l_sqltext,'%CurrentDateIn','TO_DATE(TO_CHAR(SYSDATE,''YYYY-MM-DD''),''YYYY-MM-DD'')',1,0,'i'); l_sqltext := REGEXP_REPLACE(l_sqltext,'%CurrentDateTimeIn','SYSDATE',1,0,'i'); l_sqltext := REGEXP_REPLACE(l_sqltext,'%DateNull','NULL',1,0,'i'); l_sqltext := REGEXP_REPLACE(l_sqltext,'%DateTimeNull','NULL',1,0,'i'); --l_sqltext := REGEXP_REPLACE(l_sqltext,'%OperatorID','''''',1,0,'i'); --l_sqltext := REGEXP_REPLACE(l_sqltext,'%Upper *\(','UPPER(',1,0,'i'); l_sqltext := REGEXP_REPLACE(l_sqltext,'%TimeNull','NULL',1,0,'i'); l_sqltext := REGEXP_REPLACE(l_sqltext,'%Truncate *\(','TRUNC(',1,0,'i'); --dbms_output.put_line(p_sqlid||'='||l_sqltext); END IF; RETURN l_sqltext; END get_pssqltext; /*-------------------------------------------------------------------------------------------------------*/ PROCEDURE view_compare_main ( p_recname_pattern IN VARCHAR2 DEFAULT '' , p_projectname_src IN VARCHAR2 DEFAULT '' , p_projectname_out IN VARCHAR2 DEFAULT 'REBUILDVIEWS' , p_verbose IN BOOLEAN DEFAULT FALSE ) IS CURSOR c_view_compare IS SELECT r.recname, v.view_name, v.text, v.text_length FROM psrecdefn r LEFT OUTER JOIN user_views v --must outer join in case view not created in database ON v.view_name = DECODE(r.sqltablename,' ','PS_'||r.recname, r.sqltablename) , pssqldefn sd WHERE r.rectype = 1 AND (p_projectname_src IS NULL OR EXISTS( SELECT 'x' FROM psprojectitem pi --must outer join in case view not in any project WHERE pi.objecttype = 0 --a record AND pi.objectid1 = 1 --a record AND pi.objectvalue1 = r.recname AND pi.projectname LIKE UPPER(p_projectname_src)) ) AND (r.recname like UPPER(RTRIM(p_recname_pattern)) OR p_recname_pattern IS NULL) AND sd.sqlid = r.recname AND sd.sqltype = 2 ORDER BY 1; v_view_compare c_view_compare%ROWTYPE; l_pos_id INTEGER; l_error_count INTEGER := 0; l_message VARCHAR2(100); l_db_length INTEGER; l_db_hexdump CLOB; l_db_view_text CLOB; /*Oracle view text*/ l_db_view_char VARCHAR2(1); l_ps_length INTEGER; l_ps_hexdump CLOB; l_ps_view_text CLOB; /*PeopleSoft view text*/ l_ps_view_char VARCHAR2(1); BEGIN psstatus; --get peopletools version OPEN c_view_compare; LOOP FETCH c_view_compare INTO v_view_compare; EXIT WHEN c_view_compare%NOTFOUND; --dbms_output.put_line('Before:'||v_view_compare.text); l_db_view_text := single_space(v_view_compare.text); --dbms_output.put_line('After:'||l_db_view_text); l_message := v_view_compare.recname||':'; IF v_view_compare.view_name IS NULL THEN l_message := 'View does not exist in database.'; ELSE l_ps_view_text := single_space(get_pssqltext(v_view_compare.recname,'2')); IF l_ps_view_text = l_db_view_text THEN DELETE FROM psprojectitem WHERE projectname = UPPER(p_projectname_out) AND objectvalue1 = v_view_compare.recname AND ((objecttype = 0 AND objectid1 = 1 AND objectid2 = 0) OR (objecttype = 30 AND objectid1 = 65 AND objectid2 = 81)); l_message := 'OK! PeopleSoft and Database View text match.'; ELSE l_message := 'View Texts do not match'; dbms_output.put_line(v_view_compare.recname||': '||l_message); IF p_verbose THEN l_db_length := LENGTH(l_db_view_text); l_ps_length := LENGTH(l_ps_view_text); l_pos_id := 1; l_db_hexdump := ''; l_ps_hexdump := ''; LOOP l_db_view_char := SUBSTR(l_db_view_text,l_pos_id,1); l_ps_view_char := SUBSTR(l_ps_view_text,l_pos_id,1); l_db_hexdump := l_db_hexdump || TO_CHAR(ASCII(l_db_view_char),'XX'); l_ps_hexdump := l_ps_hexdump || TO_CHAR(ASCII(l_ps_view_char),'XX'); IF l_pos_id > l_db_length AND l_pos_id > l_ps_length THEN EXIT; ELSIF l_db_view_char = l_ps_view_char THEN l_pos_id := l_pos_id + 1; ELSE EXIT; END IF; END LOOP; dbms_output.put_line('DB=('||v_view_compare.view_name||','||l_db_length||')'||l_db_view_text); dbms_output.put_line('PS=('||v_view_compare.recname||','||l_ps_length||')'||l_ps_view_text); dbms_output.put_line('DB='||l_db_hexdump); dbms_output.put_line('PS='||l_ps_hexdump); dbms_output.put_line(SUBSTR(l_db_view_text,1,l_pos_id)); dbms_output.put_line(l_db_view_char||'!='||l_ps_view_char); END IF; l_error_count := l_error_count + 1; ins_project_view(p_projectname_out, v_view_compare.recname); END IF; END IF; --dbms_output.put_line(v_view_compare.recname||': '||l_message); COMMIT; --loop is quite slow, so I don't want to hold locks END LOOP; CLOSE c_view_compare; /*do project header insert/update at the end of the loop*/ ins_projectdefn(p_projectname=>p_projectname_out, p_descr=>p_projectname_out ,p_descrlong=>'Generated by ptutil.view_compare at ' ,p_inc_version=>TRUE); END view_compare_main; /*-------------------------------------------------------------------------------------------------------*/ PROCEDURE view_compare ( p_recname_pattern IN VARCHAR2 DEFAULT '' , p_projectname_src IN VARCHAR2 DEFAULT '' , p_projectname_out IN VARCHAR2 DEFAULT 'REBUILDVIEWS' , p_verbose IN BOOLEAN DEFAULT FALSE , p_async IN BOOLEAN DEFAULT FALSE , p_next_date IN DATE DEFAULT SYSDATE ) IS l_what VARCHAR2(1000); l_job INTEGER; BEGIN IF p_async THEN l_what := 'ptutil.view_compare(p_recname_pattern=>'''||p_recname_pattern||''''|| ', p_projectname_src=>'''||p_projectname_src||''''|| ', p_projectname_out=>'''||p_projectname_out||''''|| ', p_async=>FALSE, p_verbose=>FALSE);'; dbms_job.submit(job=>l_job, what=>l_what, next_date=>p_next_date); COMMIT; IF p_verbose THEN dbms_output.put_line('Job '||l_job||' @ '||TO_CHAR(p_nexT_date,'hh24:mi:ss dd.mm.yyyy')||' = '||l_what); END IF; ELSE view_compare_main (p_recname_pattern => p_recname_pattern ,p_projectname_src => p_projectname_src ,p_projectname_out => p_projectname_out ,p_verbose => p_verbose ); END IF; END view_compare; /*-------------------------------------------------------------------------------------------------------*/ END ptutil; / show errors /*------------------------------------------------------------------------------------------------------- --Example usage for view_compare----------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------- spool ptutil set serveroutput on lines 100 timi on --The first time you run it you need to check every view --it can take 30-60 minutes to run through, so run it asycnhronously execute ptutil.view_compare(p_async=>TRUE); --use these queries to see if the scheduled job is running select * from user_jobs; select * from dba_jobs_running; pause --This command uses the REBUILDVIEW project as a source, as well as a target. So after the first run --you can rebuild the views in the generated project, and then you recheck just those views, and if --they are no longer a problem they are removed from the project. execute ptutil.view_compare(p_projectname_src=>'REBUILDVIEWS',p_verbose=>TRUE); pause --you can run the comparison for a single view execute ptutil.view_compare(p_recname_pattern=>'HR_ROST_CANR_VW',p_verbose=>TRUE); pause --This query lists off the views in the project --set pages 0 --select DISTINCT objectvalue1 from psprojectitem where projectname = 'REBUILDVIEWS' ORDER BY 1; /*------------------------------------------------------------------------------------------------------- --Example usage for view_compare----------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------*/ spool off