-- ===================================================================================== -- ART_SEARCH toolkit v2 - search a string across ALL OIC artifacts in the imported repository. -- -- PREREQUISITES -- 1. As SYSTEM (once): GRANT CREATE PROCEDURE, CREATE VIEW TO scratch; -- 2. Run art_parse_all.sql first (creates/fills ART_PARSE, ART_TEXT, ART_JAR_ENTRY). -- INSTALL: connect as SCRATCH and run @art_search_install.sql -- -- USAGE -- SET SERVEROUTPUT ON -- EXEC art_search('DELETE_DOC'); -- exact case, everything (fast, ~seconds) -- EXEC art_search('delete_doc', 'N'); -- ignore case (slower: plain artifacts are converted one by one) -- EXEC art_search('delete_doc', 'N', 'XSLT,JCA'); -- ignore case, limited to artifact types (much faster) -- EXEC art_search('GenericSoapPort', 'Y', 'WSDL'); -- decoded WSDL/XSD come from ART_TEXT -- EXEC art_search('IdcService', 'Y', NULL, 20); -- up to 20 hits per artifact -- SELECT * FROM art_search_last_v; -- hits of the most recent run, with snippets -- SELECT * FROM art_search_by_integration_v; -- most recent run, one row per integration/version -- SELECT * FROM art_search_runs_v; -- history of runs -- SELECT * FROM art_catalog_v WHERE integration_name LIKE '%Import%'; -- browse artifacts with parse status -- SELECT art_get_text(artifact_pk) FROM art_catalog_v WHERE artifact_path LIKE '%req_0d3f65%'; -- read one artifact -- -- WHERE IT SEARCHES (sources in ART_SEARCH_HITS.SOURCE) -- ICS_ARTIFACTS plain-text artifacts in place (raw bytes) + decoded WSDL/XSD (ART_TEXT) -- LIBRARY_JS JavaScript from the unzipped library JARs (ART_JAR_ENTRY) -- ICS_RESOURCE.*, ICS_CONTAINER.CONTENT, ICS_SCHEDULE_PARAMS definition columns (p_other_tables='Y') -- A hit means the text occurs; read the snippet. Dynamic expressions built at runtime will not match. -- ART_SEARCH_HITS holds integration names and snippets: customer-confidential. -- ===================================================================================== CREATE TABLE IF NOT EXISTS art_search_hits ( run_id NUMBER NOT NULL, run_ts TIMESTAMP DEFAULT SYSTIMESTAMP, search_term VARCHAR2(400), case_sensitive CHAR(1), source VARCHAR2(40), project VARCHAR2(400), integration_name VARCHAR2(400), integration_code VARCHAR2(400), version VARCHAR2(50), persisted_state VARCHAR2(150), artifact_type VARCHAR2(200), artifact_path VARCHAR2(2000), enc VARCHAR2(20), hit_no NUMBER, position NUMBER, snippet VARCHAR2(1000) ); -- ---------- helper: base64(gzip) BLOB -> raw BLOB (plain content is returned unchanged) ---------- CREATE OR REPLACE FUNCTION art_decode(p_content BLOB) RETURN BLOB IS l_len PLS_INTEGER; l_pos PLS_INTEGER := 1; l_amt PLS_INTEGER; l_chunk RAW(32000); l_gz BLOB; l_out BLOB; BEGIN IF p_content IS NULL THEN RETURN NULL; END IF; l_len := DBMS_LOB.GETLENGTH(p_content); IF l_len < 4 OR DBMS_LOB.SUBSTR(p_content, 4, 1) <> UTL_RAW.CAST_TO_RAW('H4sI') THEN RETURN p_content; END IF; DBMS_LOB.CREATETEMPORARY(l_gz, TRUE); WHILE l_pos <= l_len LOOP l_amt := LEAST(32000, l_len - l_pos + 1); l_chunk := UTL_ENCODE.BASE64_DECODE(DBMS_LOB.SUBSTR(p_content, l_amt, l_pos)); DBMS_LOB.WRITEAPPEND(l_gz, UTL_RAW.LENGTH(l_chunk), l_chunk); l_pos := l_pos + l_amt; END LOOP; l_out := UTL_COMPRESS.LZ_UNCOMPRESS(l_gz); DBMS_LOB.FREETEMPORARY(l_gz); RETURN l_out; END art_decode; / -- ---------- helper: BLOB (UTF-8 text) -> CLOB ---------- CREATE OR REPLACE FUNCTION art_clob(p_blob BLOB) RETURN CLOB IS l_c CLOB; l_do INTEGER := 1; l_so INTEGER := 1; l_lc INTEGER := DBMS_LOB.DEFAULT_LANG_CTX; l_w INTEGER; BEGIN IF p_blob IS NULL THEN RETURN NULL; END IF; IF DBMS_LOB.SUBSTR(p_blob, 3, 1) = HEXTORAW('EFBBBF') THEN l_so := 4; END IF; -- skip UTF-8 BOM DBMS_LOB.CREATETEMPORARY(l_c, TRUE); DBMS_LOB.CONVERTTOCLOB(l_c, p_blob, DBMS_LOB.LOBMAXSIZE, l_do, l_so, NLS_CHARSET_ID('AL32UTF8'), l_lc, l_w); RETURN l_c; END art_clob; / -- ---------- read one artifact as readable text (uses ART_TEXT when the artifact was pre-decoded) ---------- CREATE OR REPLACE FUNCTION art_get_text(p_artifact_pk RAW) RETURN CLOB IS l_b BLOB; l_d BLOB; l_c CLOB; BEGIN BEGIN SELECT txt INTO l_c FROM art_text WHERE pk_id = p_artifact_pk; RETURN l_c; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; SELECT content INTO l_b FROM ics_artifacts WHERE pk_id = p_artifact_pk; l_d := art_decode(l_b); l_c := art_clob(l_d); RETURN l_c; END art_get_text; / -- ---------- browse artifacts with project / integration / version / parse status ---------- CREATE OR REPLACE VIEW art_catalog_v AS SELECT a.pk_id AS artifact_pk, TO_CHAR(c.name) AS project, c.container_id, TO_CHAR(r.name) AS integration_name, TO_CHAR(r.code) AS integration_code, r.version, r.persisted_state, r.resource_type AS integration_type, a.resource_type AS artifact_type, a.resource_path AS artifact_path, TO_CHAR(a.name) AS artifact_name, p.status AS parse_status, p.fmt AS format, p.searchable, DBMS_LOB.GETLENGTH(a.content) AS stored_bytes FROM ics_artifacts a LEFT JOIN art_parse p ON p.pk_id = a.pk_id LEFT JOIN ics_resource r ON r.ics_resource_id = a.ics_resource_id LEFT JOIN ics_container c ON c.container_id = r.container_id; -- ---------- result views ---------- CREATE OR REPLACE VIEW art_search_runs_v AS SELECT run_id, run_ts, search_term, case_sensitive, snippet AS run_info FROM art_search_hits WHERE source = 'SUMMARY' ORDER BY run_id DESC; CREATE OR REPLACE VIEW art_search_last_v AS SELECT run_id, search_term, source, project, integration_name, integration_code, version, persisted_state, artifact_type, artifact_path, enc, hit_no, position, snippet FROM art_search_hits WHERE run_id = (SELECT MAX(run_id) FROM art_search_hits) AND source <> 'SUMMARY' ORDER BY project, integration_name, version, artifact_path, hit_no; CREATE OR REPLACE VIEW art_search_by_integration_v AS SELECT project, integration_name, integration_code, version, persisted_state, COUNT(*) AS hits, LISTAGG(DISTINCT artifact_type, ', ') WITHIN GROUP (ORDER BY artifact_type) AS artifact_types FROM art_search_hits WHERE run_id = (SELECT MAX(run_id) FROM art_search_hits) AND source <> 'SUMMARY' GROUP BY project, integration_name, integration_code, version, persisted_state ORDER BY project, integration_name, version; -- ---------- the search procedure ---------- CREATE OR REPLACE PROCEDURE art_search( p_term IN VARCHAR2, p_case_sensitive IN VARCHAR2 DEFAULT 'Y', -- 'Y' exact case (fast), 'N' ignore case p_types IN VARCHAR2 DEFAULT NULL, -- comma list of artifact RESOURCE_TYPE (e.g. 'XSLT,JCA'); NULL = all p_max_per_artifact IN PLS_INTEGER DEFAULT 5, -- max hits recorded per artifact p_other_tables IN VARCHAR2 DEFAULT 'Y', -- also search ICS_RESOURCE / ICS_CONTAINER / ICS_SCHEDULE_PARAMS p_jars IN VARCHAR2 DEFAULT 'Y' -- also search the library JavaScript (ART_JAR_ENTRY) ) IS l_cs CHAR(1) := UPPER(SUBSTR(NVL(p_case_sensitive, 'Y'), 1, 1)); l_run NUMBER; l_raw RAW(2000); l_low CLOB; l_clob CLOB; l_pos PLS_INTEGER; l_start PLS_INTEGER; l_snip VARCHAR2(1000); l_err PLS_INTEGER := 0; l_rows PLS_INTEGER := 0; l_n PLS_INTEGER; l_hits PLS_INTEGER := 0; l_other PLS_INTEGER := 0; l_proj VARCHAR2(400); l_iname VARCHAR2(400); l_icode VARCHAR2(400); l_ver VARCHAR2(50); l_state VARCHAR2(150); l_tl VARCHAR2(4000); -- normalised type list: ',XSLT,JCA,' PROCEDURE add_hit(p_source VARCHAR2, p_id VARCHAR2, p_type VARCHAR2, p_path VARCHAR2, p_enc VARCHAR2, p_n NUMBER, p_p NUMBER, p_s VARCHAR2) IS BEGIN BEGIN SELECT TO_CHAR(c.name), TO_CHAR(rs.name), TO_CHAR(rs.code), rs.version, rs.persisted_state INTO l_proj, l_iname, l_icode, l_ver, l_state FROM ics_resource rs LEFT JOIN ics_container c ON c.container_id = rs.container_id WHERE rs.ics_resource_id = p_id; EXCEPTION WHEN NO_DATA_FOUND THEN l_proj := NULL; l_iname := NULL; l_icode := NULL; l_ver := NULL; l_state := NULL; END; INSERT INTO art_search_hits (run_id, search_term, case_sensitive, source, project, integration_name, integration_code, version, persisted_state, artifact_type, artifact_path, enc, hit_no, position, snippet) VALUES (l_run, p_term, l_cs, p_source, l_proj, l_iname, l_icode, l_ver, l_state, p_type, p_path, p_enc, p_n, p_p, SUBSTR(REGEXP_REPLACE(p_s, '[[:space:]]+', ' '), 1, 1000)); l_hits := l_hits + 1; END add_hit; -- hits inside a BLOB, exact case (raw byte match) PROCEDURE hits_blob(p_source VARCHAR2, p_id VARCHAR2, p_type VARCHAR2, p_path VARCHAR2, p_enc VARCHAR2, p_blob BLOB) IS l_s PLS_INTEGER := 1; l_p PLS_INTEGER; BEGIN FOR i IN 1 .. p_max_per_artifact LOOP l_p := DBMS_LOB.INSTR(p_blob, l_raw, l_s); EXIT WHEN l_p = 0; add_hit(p_source, p_id, p_type, p_path, p_enc, i, l_p, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(p_blob, 300, GREATEST(l_p - 100, 1)))); l_s := l_p + UTL_RAW.LENGTH(l_raw); END LOOP; END hits_blob; -- hits inside a CLOB (exact or ignore case) PROCEDURE hits_clob(p_source VARCHAR2, p_id VARCHAR2, p_type VARCHAR2, p_path VARCHAR2, p_enc VARCHAR2, p_c CLOB) IS l_s PLS_INTEGER := 1; l_p PLS_INTEGER; l_lw CLOB; BEGIN IF l_cs = 'N' THEN l_lw := LOWER(p_c); END IF; FOR i IN 1 .. p_max_per_artifact LOOP IF l_cs = 'Y' THEN l_p := DBMS_LOB.INSTR(p_c, p_term, l_s); ELSE l_p := DBMS_LOB.INSTR(l_lw, LOWER(p_term), l_s); END IF; EXIT WHEN l_p = 0; add_hit(p_source, p_id, p_type, p_path, p_enc, i, l_p, DBMS_LOB.SUBSTR(p_c, 300, GREATEST(l_p - 100, 1))); l_s := l_p + LENGTH(p_term); END LOOP; END hits_clob; BEGIN IF p_term IS NULL OR LENGTH(p_term) < 3 THEN RAISE_APPLICATION_ERROR(-20001, 'Search term must be at least 3 characters.'); END IF; IF l_cs NOT IN ('Y', 'N') THEN RAISE_APPLICATION_ERROR(-20002, 'p_case_sensitive must be Y or N.'); END IF; l_raw := UTL_RAW.CAST_TO_RAW(p_term); l_tl := CASE WHEN p_types IS NULL THEN NULL ELSE ',' || UPPER(REPLACE(p_types, ' ', '')) || ',' END; SELECT NVL(MAX(run_id), 0) + 1 INTO l_run FROM art_search_hits; -- 1) plain-text artifacts, searched in place (XSLT, JCA, XML, JSON, properties, serialized maps ...) SELECT COUNT(*) INTO l_n FROM art_parse p WHERE p.enc = 'PLAIN' AND p.searchable IN ('Y', 'R') AND (l_tl IS NULL OR INSTR(l_tl, ',' || UPPER(NVL(p.resource_type, '(NULL)')) || ',') > 0); l_rows := l_rows + l_n; IF l_cs = 'Y' THEN FOR r IN (SELECT p.ics_resource_id, NVL(p.resource_type, '(NULL)') rtype, p.resource_path, a.content FROM art_parse p JOIN ics_artifacts a ON a.pk_id = p.pk_id WHERE p.enc = 'PLAIN' AND p.searchable IN ('Y', 'R') AND (l_tl IS NULL OR INSTR(l_tl, ',' || UPPER(NVL(p.resource_type, '(NULL)')) || ',') > 0) AND DBMS_LOB.INSTR(a.content, l_raw) > 0) LOOP BEGIN hits_blob('ICS_ARTIFACTS', r.ics_resource_id, r.rtype, r.resource_path, 'PLAIN', r.content); EXCEPTION WHEN OTHERS THEN l_err := l_err + 1; END; END LOOP; ELSE FOR r IN (SELECT p.ics_resource_id, NVL(p.resource_type, '(NULL)') rtype, p.resource_path, a.content FROM art_parse p JOIN ics_artifacts a ON a.pk_id = p.pk_id WHERE p.enc = 'PLAIN' AND p.searchable = 'Y' AND (l_tl IS NULL OR INSTR(l_tl, ',' || UPPER(NVL(p.resource_type, '(NULL)')) || ',') > 0)) LOOP BEGIN l_clob := art_clob(r.content); hits_clob('ICS_ARTIFACTS', r.ics_resource_id, r.rtype, r.resource_path, 'PLAIN', l_clob); DBMS_LOB.FREETEMPORARY(l_clob); EXCEPTION WHEN OTHERS THEN l_err := l_err + 1; END; END LOOP; END IF; -- 2) decoded base64+gzip artifacts (WSDL, XMLSchema) from ART_TEXT SELECT COUNT(*) INTO l_n FROM art_text t JOIN art_parse p ON p.pk_id = t.pk_id WHERE (l_tl IS NULL OR INSTR(l_tl, ',' || UPPER(NVL(p.resource_type, '(NULL)')) || ',') > 0); l_rows := l_rows + l_n; FOR r IN (SELECT p.ics_resource_id, NVL(p.resource_type, '(NULL)') rtype, p.resource_path, t.txt FROM art_text t JOIN art_parse p ON p.pk_id = t.pk_id WHERE (l_tl IS NULL OR INSTR(l_tl, ',' || UPPER(NVL(p.resource_type, '(NULL)')) || ',') > 0) AND ((l_cs = 'Y' AND DBMS_LOB.INSTR(t.txt, p_term) > 0) OR (l_cs = 'N' AND DBMS_LOB.INSTR(LOWER(t.txt), LOWER(p_term)) > 0))) LOOP BEGIN hits_clob('ICS_ARTIFACTS', r.ics_resource_id, r.rtype, r.resource_path, 'BASE64_GZIP', r.txt); EXCEPTION WHEN OTHERS THEN l_err := l_err + 1; END; END LOOP; -- 3) library JavaScript (unzipped JARs) IF UPPER(NVL(p_jars, 'Y')) = 'Y' AND (l_tl IS NULL OR INSTR(l_tl, ',APILIBRART_JARFILE,') > 0) THEN SELECT COUNT(*) INTO l_n FROM art_jar_entry e WHERE e.txt IS NOT NULL; l_rows := l_rows + l_n; FOR r IN (SELECT p.ics_resource_id, e.jar_name || ' :: ' || e.entry_name entry_path, e.txt FROM art_jar_entry e JOIN art_parse p ON p.pk_id = e.pk_id WHERE e.txt IS NOT NULL AND ((l_cs = 'Y' AND DBMS_LOB.INSTR(e.txt, p_term) > 0) OR (l_cs = 'N' AND DBMS_LOB.INSTR(LOWER(e.txt), LOWER(p_term)) > 0))) LOOP BEGIN hits_clob('LIBRARY_JS', r.ics_resource_id, 'APILIBRART_JARFILE', r.entry_path, 'ZIP+JS', r.txt); EXCEPTION WHEN OTHERS THEN l_err := l_err + 1; END; END LOOP; END IF; -- 4) other tables that can hold flow text (set-based) IF UPPER(NVL(p_other_tables, 'Y')) = 'Y' THEN INSERT INTO art_search_hits (run_id, search_term, case_sensitive, source, project, integration_name, integration_code, version, persisted_state, artifact_type, enc, hit_no, position, snippet) SELECT l_run, p_term, l_cs, 'ICS_RESOURCE.CONTENT', TO_CHAR(c.name), TO_CHAR(x.name), TO_CHAR(x.code), x.version, x.persisted_state, x.resource_type, 'CLOB', 1, x.pos, SUBSTR(REGEXP_REPLACE(DBMS_LOB.SUBSTR(x.content, 300, GREATEST(x.pos - 100, 1)), '[[:space:]]+', ' '), 1, 1000) FROM (SELECT rs.*, DBMS_LOB.INSTR(CASE WHEN l_cs = 'Y' THEN rs.content ELSE LOWER(rs.content) END, CASE WHEN l_cs = 'Y' THEN p_term ELSE LOWER(p_term) END) pos FROM ics_resource rs WHERE rs.content IS NOT NULL) x LEFT JOIN ics_container c ON c.container_id = x.container_id WHERE x.pos > 0; l_other := l_other + SQL%ROWCOUNT; INSERT INTO art_search_hits (run_id, search_term, case_sensitive, source, project, integration_name, integration_code, version, persisted_state, artifact_type, enc, hit_no, position, snippet) SELECT l_run, p_term, l_cs, 'ICS_RESOURCE.RESOURCE_OBJECT', TO_CHAR(c.name), TO_CHAR(x.name), TO_CHAR(x.code), x.version, x.persisted_state, x.resource_type, 'BLOB', 1, x.pos, SUBSTR(REGEXP_REPLACE(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(x.resource_object, 300, GREATEST(x.pos - 100, 1))), '[[:space:]]+', ' '), 1, 1000) FROM (SELECT rs.*, CASE WHEN l_cs = 'Y' THEN DBMS_LOB.INSTR(rs.resource_object, UTL_RAW.CAST_TO_RAW(p_term)) ELSE GREATEST(DBMS_LOB.INSTR(rs.resource_object, UTL_RAW.CAST_TO_RAW(p_term)), DBMS_LOB.INSTR(rs.resource_object, UTL_RAW.CAST_TO_RAW(UPPER(p_term))), DBMS_LOB.INSTR(rs.resource_object, UTL_RAW.CAST_TO_RAW(LOWER(p_term)))) END pos FROM ics_resource rs WHERE rs.resource_object IS NOT NULL) x LEFT JOIN ics_container c ON c.container_id = x.container_id WHERE x.pos > 0; l_other := l_other + SQL%ROWCOUNT; INSERT INTO art_search_hits (run_id, search_term, case_sensitive, source, project, artifact_type, enc, hit_no, position, snippet) SELECT l_run, p_term, l_cs, 'ICS_CONTAINER.CONTENT', TO_CHAR(x.name), x.container_type, 'CLOB', 1, x.pos, SUBSTR(REGEXP_REPLACE(DBMS_LOB.SUBSTR(x.content, 300, GREATEST(x.pos - 100, 1)), '[[:space:]]+', ' '), 1, 1000) FROM (SELECT k.*, DBMS_LOB.INSTR(CASE WHEN l_cs = 'Y' THEN k.content ELSE LOWER(k.content) END, CASE WHEN l_cs = 'Y' THEN p_term ELSE LOWER(p_term) END) pos FROM ics_container k WHERE k.content IS NOT NULL) x WHERE x.pos > 0; l_other := l_other + SQL%ROWCOUNT; INSERT INTO art_search_hits (run_id, search_term, case_sensitive, source, project, integration_name, integration_code, version, artifact_type, artifact_path, enc, hit_no, position, snippet) SELECT l_run, p_term, l_cs, 'ICS_SCHEDULE_PARAMS', TO_CHAR(c.name), TO_CHAR(x.flow_code), TO_CHAR(x.flow_code), TO_CHAR(x.flow_version), 'SCHEDULE_PARAM', TO_CHAR(x.schedule_name) || '/' || TO_CHAR(x.param_name), 'CLOB', 1, x.pos, SUBSTR(REGEXP_REPLACE(DBMS_LOB.SUBSTR(x.param_value, 300, GREATEST(x.pos - 100, 1)), '[[:space:]]+', ' '), 1, 1000) FROM (SELECT sp.*, DBMS_LOB.INSTR(CASE WHEN l_cs = 'Y' THEN sp.param_value ELSE LOWER(sp.param_value) END, CASE WHEN l_cs = 'Y' THEN p_term ELSE LOWER(p_term) END) pos FROM ics_schedule_params sp WHERE sp.param_value IS NOT NULL) x LEFT JOIN ics_container c ON c.container_id = x.container_id WHERE x.pos > 0; l_other := l_other + SQL%ROWCOUNT; END IF; INSERT INTO art_search_hits (run_id, search_term, case_sensitive, source, snippet) VALUES (l_run, p_term, l_cs, 'SUMMARY', 'types=' || NVL(p_types, 'ALL') || ' artifacts_scanned=' || l_rows || ' artifact_hits=' || l_hits || ' other_table_hits=' || l_other || ' errors=' || l_err); COMMIT; DBMS_OUTPUT.PUT_LINE('run_id=' || l_run || ' artifacts_scanned=' || l_rows || ' artifact_hits=' || l_hits || ' other_table_hits=' || l_other || ' errors=' || l_err); END art_search; / PROMPT ART_SEARCH toolkit v2 installed. Try: SET SERVEROUTPUT ON then EXEC art_search('DELETE_DOC');