-- ===================================================================================== -- ART_PARSE_ALL - parse EVERY row of ICS_ARTIFACTS once, record a status per artifact, -- and materialise decoded text for the base64+gzip artifacts so every record is searchable. -- -- Needs only CREATE TABLE (no procedure/view privileges). Safe to re-run: it only processes -- artifacts that have no ART_PARSE row yet. To start over: TRUNCATE TABLE art_parse; TRUNCATE TABLE art_text; -- Runtime on the local Docker DB: ~15 min for ~136k artifacts (245 MB stored). -- -- STATUS VALUES (ART_PARSE.STATUS) -- PARSED_OK decoded + converted to text + valid XML (XMLTYPE) or valid JSON (IS JSON) -- PARSED_TEXT decoded + text, no XML/JSON structure (properties, plain text) -> searchable -- PARSED_EXTREF text is searchable; XML refers to an external DTD/schema URL the DB may not fetch (ORA-64498) -- PARSED_MALFORMED text is searchable; but not valid XML / JSON (see ERROR_MSG) -- EMPTY_BY_DESIGN *_Reference rows - OIC stores pointers only, no content -- PARSE_PARTIAL library JAR where at least one zip entry could not be extracted (see ERROR_MSG / ART_JAR_ENTRY.ERR) -- BINARY JAR (until the jar step below runs) / ZIP / Java-serialized / NUL bytes - not parsed (left over). Java-serialized rows -- (FMT=JAVASER, SEARCHABLE='R') can still be raw-byte searched for ASCII strings. -- NO_CONTENT content missing or whitespace only (left over) -- DECODE_FAILED base64/gzip decode error (left over) -- ERROR unexpected failure (left over, see ERROR_MSG) -- SEARCHABLE = 'Y' for every PARSED_* status, 'R' = raw-byte search only, 'J' = library jar searched via ART_JAR_ENTRY, -- 'N' = not searchable (includes PARSE_PARTIAL jars). -- ===================================================================================== CREATE TABLE IF NOT EXISTS art_parse ( pk_id RAW(16) PRIMARY KEY, ics_resource_id NVARCHAR2(400), resource_type VARCHAR2(200), ext VARCHAR2(30), resource_path VARCHAR2(2000), stored_bytes NUMBER, enc VARCHAR2(12), decoded_bytes NUMBER, fmt VARCHAR2(12), status VARCHAR2(30), searchable CHAR(1), error_msg VARCHAR2(500), parsed_ts TIMESTAMP DEFAULT SYSTIMESTAMP ); CREATE TABLE IF NOT EXISTS art_text (pk_id RAW(16) PRIMARY KEY, txt CLOB); -- decoded text of base64+gzip artifacts (WSDL, XSD) DECLARE c_max CONSTANT PLS_INTEGER := 1000000; -- max rows per run c_secs CONSTANT NUMBER := 1700; -- wall-clock cap per run; just re-run to continue l_t0 NUMBER := DBMS_UTILITY.GET_TIME; l_n PLS_INTEGER := 0; l_len PLS_INTEGER; l_enc VARCHAR2(12); l_status VARCHAR2(30); l_fmt VARCHAR2(12); l_srch CHAR(1); l_err VARCHAR2(500); l_blob BLOB; l_clob CLOB; l_dec NUMBER; l_ext VARCHAR2(30); l_head VARCHAR2(400); l_c1 VARCHAR2(1); l_x XMLTYPE; l_j PLS_INTEGER; l_so INTEGER; l_do INTEGER; l_lc INTEGER; l_w INTEGER; l_b64 RAW(4) := UTL_RAW.CAST_TO_RAW('H4sI'); FUNCTION art_decode(p_content BLOB) RETURN BLOB IS l_len PLS_INTEGER := DBMS_LOB.GETLENGTH(p_content); l_pos PLS_INTEGER := 1; l_amt PLS_INTEGER; l_chunk RAW(32000); l_gz BLOB; l_out BLOB; BEGIN 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; BEGIN FOR r IN (SELECT a.pk_id, a.ics_resource_id, a.resource_type, a.resource_path, a.content FROM ics_artifacts a WHERE NOT EXISTS (SELECT 1 FROM art_parse p WHERE p.pk_id = a.pk_id)) LOOP EXIT WHEN l_n >= c_max OR (DBMS_UTILITY.GET_TIME - l_t0)/100 > c_secs; l_n := l_n + 1; l_status := NULL; l_fmt := NULL; l_srch := 'N'; l_err := NULL; l_dec := NULL; l_enc := 'PLAIN'; l_len := NULL; l_ext := LOWER(REGEXP_SUBSTR(r.resource_path, '\.([A-Za-z0-9]+)$', 1, 1, NULL, 1)); BEGIN IF r.content IS NULL OR DBMS_LOB.GETLENGTH(r.content) = 0 THEN l_enc := 'NONE'; l_status := CASE WHEN r.resource_type LIKE '%\_Reference' ESCAPE '\' THEN 'EMPTY_BY_DESIGN' ELSE 'NO_CONTENT' END; ELSE l_len := DBMS_LOB.GETLENGTH(r.content); IF DBMS_LOB.SUBSTR(r.content, 4, 1) = l_b64 THEN l_enc := 'BASE64_GZIP'; END IF; IF r.resource_type = 'APILIBRART_JARFILE' THEN l_status := 'BINARY'; l_fmt := 'JAR'; l_dec := l_len; ELSE BEGIN IF l_enc = 'BASE64_GZIP' THEN l_blob := art_decode(r.content); ELSE l_blob := r.content; END IF; l_dec := DBMS_LOB.GETLENGTH(l_blob); EXCEPTION WHEN OTHERS THEN l_status := 'DECODE_FAILED'; l_err := SUBSTR(SQLERRM,1,500); END; IF l_status IS NULL THEN IF l_dec = 0 THEN l_status := 'NO_CONTENT'; ELSIF DBMS_LOB.SUBSTR(l_blob, 2, 1) = HEXTORAW('504B') THEN l_status := 'BINARY'; l_fmt := 'ZIP'; ELSIF DBMS_LOB.SUBSTR(l_blob, 4, 1) = HEXTORAW('ACED0005') THEN l_status := 'BINARY'; l_fmt := 'JAVASER'; l_srch := 'R'; -- Java serialized object: raw-byte search only ELSIF DBMS_LOB.INSTR(l_blob, HEXTORAW('00')) > 0 THEN l_status := 'BINARY'; l_fmt := 'BIN'; ELSE l_so := CASE WHEN DBMS_LOB.SUBSTR(l_blob, 3, 1) = HEXTORAW('EFBBBF') THEN 4 ELSE 1 END; -- skip UTF-8 BOM l_do := 1; l_lc := DBMS_LOB.DEFAULT_LANG_CTX; DBMS_LOB.CREATETEMPORARY(l_clob, TRUE); DBMS_LOB.CONVERTTOCLOB(l_clob, l_blob, DBMS_LOB.LOBMAXSIZE, l_do, l_so, NLS_CHARSET_ID('AL32UTF8'), l_lc, l_w); l_head := LTRIM(DBMS_LOB.SUBSTR(l_clob, 60, 1), CHR(9)||CHR(10)||CHR(13)||' '); l_c1 := SUBSTR(l_head, 1, 1); l_srch := 'Y'; IF l_ext = 'data' THEN -- notification templates ({FROM_PARAM_1}, HTML fragments): text, not XML/JSON l_fmt := 'TEXT'; l_status := 'PARSED_TEXT'; ELSIF l_c1 = '<' THEN l_fmt := 'XML'; BEGIN l_x := XMLTYPE(l_clob); l_status := 'PARSED_OK'; l_x := NULL; EXCEPTION WHEN OTHERS THEN IF SQLCODE = -64498 THEN l_status := 'PARSED_EXTREF'; ELSE l_status := 'PARSED_MALFORMED'; END IF; l_err := SUBSTR(SQLERRM,1,500); END; ELSIF l_c1 IN ('{','[') THEN l_fmt := 'JSON'; SELECT COUNT(*) INTO l_j FROM dual WHERE l_clob IS JSON; IF l_j = 1 THEN l_status := 'PARSED_OK'; ELSE l_status := 'PARSED_MALFORMED'; l_err := 'not valid JSON'; END IF; ELSIF l_head IS NULL THEN l_fmt := 'TEXT'; l_status := 'NO_CONTENT'; l_srch := 'N'; ELSE l_fmt := 'TEXT'; l_status := 'PARSED_TEXT'; END IF; IF l_enc = 'BASE64_GZIP' AND l_srch = 'Y' THEN INSERT INTO art_text (pk_id, txt) VALUES (r.pk_id, l_clob); END IF; DBMS_LOB.FREETEMPORARY(l_clob); END IF; IF l_enc = 'BASE64_GZIP' AND DBMS_LOB.ISTEMPORARY(l_blob) = 1 THEN DBMS_LOB.FREETEMPORARY(l_blob); END IF; END IF; END IF; END IF; EXCEPTION WHEN OTHERS THEN l_status := 'ERROR'; l_srch := 'N'; l_err := SUBSTR(SQLERRM,1,500); END; INSERT INTO art_parse (pk_id, ics_resource_id, resource_type, ext, resource_path, stored_bytes, enc, decoded_bytes, fmt, status, searchable, error_msg) VALUES (r.pk_id, r.ics_resource_id, r.resource_type, l_ext, r.resource_path, l_len, l_enc, l_dec, l_fmt, l_status, l_srch, l_err); IF MOD(l_n, 200) = 0 THEN COMMIT; END IF; END LOOP; COMMIT; END; / -- ------------------------------ LIBRARY JARs (APILIBRART_JARFILE) ------------------------------ -- Each OIC library .jar is a ZIP holding one JavaScript file (+ manifest). Unzipped in-database (no Java needed): -- the central directory is parsed, deflate entries are wrapped in a gzip header and inflated with UTL_COMPRESS. -- Result: ART_JAR_ENTRY (one row per zip entry, TXT = decoded text). -- Robustness (v2): every integer read is padded to a full 4-byte RAW (UTL_RAW.CAST_TO_BINARY_INTEGER is documented for -- the 4-byte binary representation), entry bytes are copied with DBMS_LOB.COPY (no 32,767-byte RAW limit), offsets and -- sizes are bounds-checked, ZIP64 is rejected explicitly, and the inflated size must match the size in the directory. -- A jar is only marked PARSED_OK when EVERY entry was extracted without error. CREATE TABLE IF NOT EXISTS art_jar_entry ( pk_id RAW(16), jar_name VARCHAR2(400), integration VARCHAR2(400), entry_name VARCHAR2(1000), method NUMBER, comp_size NUMBER, uncomp_size NUMBER, kind VARCHAR2(10), content BLOB, txt CLOB, err VARCHAR2(500) ); TRUNCATE TABLE art_jar_entry; DECLARE l_jar BLOB; l_len NUMBER; l_eocd PLS_INTEGER; l_ents PLS_INTEGER; l_cd NUMBER; l_q PLS_INTEGER; l_p NUMBER; l_meth PLS_INTEGER; l_csz NUMBER; l_usz NUMBER; l_crc RAW(4); l_nl PLS_INTEGER; l_xl PLS_INTEGER; l_cl PLS_INTEGER; l_lo NUMBER; l_name VARCHAR2(1000); l_lnl PLS_INTEGER; l_lxl PLS_INTEGER; l_dpos NUMBER; l_data BLOB; l_gz BLOB; l_hdr RAW(10) := HEXTORAW('1F8B0800000000000000'); l_kind VARCHAR2(10); l_clob CLOB; l_do INTEGER; l_so INTEGER; l_lc INTEGER; l_w INTEGER; l_err VARCHAR2(500); -- little-endian unsigned reads; the bytes are padded to 4 so the cast always receives a full 4-byte RAW FUNCTION u16(p NUMBER) RETURN NUMBER IS BEGIN IF p < 1 OR p + 1 > l_len THEN RAISE_APPLICATION_ERROR(-20011, 'read outside jar at ' || p); END IF; RETURN UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.CONCAT(DBMS_LOB.SUBSTR(l_jar, 2, p), HEXTORAW('0000')), UTL_RAW.LITTLE_ENDIAN); END u16; FUNCTION u32(p NUMBER) RETURN NUMBER IS -- NUMBER, so values >= 2^31 are not negative BEGIN RETURN u16(p) + u16(p + 2) * 65536; END u32; BEGIN FOR j IN (SELECT a.pk_id, TO_CHAR(a.name) jar_name, TO_CHAR(r.name) integ, a.content FROM ics_artifacts a LEFT JOIN ics_resource r ON r.ics_resource_id = a.ics_resource_id WHERE a.resource_type = 'APILIBRART_JARFILE') LOOP l_jar := j.content; l_len := DBMS_LOB.GETLENGTH(l_jar); BEGIN l_eocd := NULL; l_q := l_len - 21 + 1; -- end-of-central-directory record WHILE l_q >= 1 AND l_q >= l_len - 70000 LOOP IF DBMS_LOB.SUBSTR(l_jar, 4, l_q) = HEXTORAW('504B0506') THEN l_eocd := l_q; EXIT; END IF; l_q := l_q - 1; END LOOP; IF l_eocd IS NULL THEN RAISE_APPLICATION_ERROR(-20010, 'no EOCD'); END IF; l_ents := u16(l_eocd + 10); l_cd := u32(l_eocd + 16) + 1; l_p := l_cd; IF l_ents = 65535 OR u32(l_eocd + 16) = 4294967295 THEN RAISE_APPLICATION_ERROR(-20012, 'ZIP64 not supported'); END IF; IF l_cd < 1 OR l_cd > l_eocd THEN RAISE_APPLICATION_ERROR(-20013, 'central directory offset out of range'); END IF; FOR e IN 1 .. l_ents LOOP l_err := NULL; l_clob := NULL; l_data := NULL; l_kind := NULL; l_name := NULL; l_meth := NULL; l_csz := NULL; l_usz := NULL; l_nl := NULL; BEGIN IF l_p + 46 > l_len OR DBMS_LOB.SUBSTR(l_jar, 4, l_p) <> HEXTORAW('504B0102') THEN RAISE_APPLICATION_ERROR(-20014, 'bad central directory entry ' || e); END IF; l_meth := u16(l_p+10); l_crc := DBMS_LOB.SUBSTR(l_jar, 4, l_p+16); l_csz := u32(l_p+20); l_usz := u32(l_p+24); l_nl := u16(l_p+28); l_xl := u16(l_p+30); l_cl := u16(l_p+32); l_lo := u32(l_p+42) + 1; l_name := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(l_jar, LEAST(l_nl, 1000), l_p+46)); IF l_csz = 4294967295 OR l_usz = 4294967295 OR l_lo = 4294967296 THEN RAISE_APPLICATION_ERROR(-20012, 'ZIP64 entry not supported'); END IF; IF l_lo + 30 > l_len OR DBMS_LOB.SUBSTR(l_jar, 4, l_lo) <> HEXTORAW('504B0304') THEN RAISE_APPLICATION_ERROR(-20015, 'bad local header'); END IF; l_lnl := u16(l_lo+26); l_lxl := u16(l_lo+28); l_dpos := l_lo + 30 + l_lnl + l_lxl; IF l_dpos + l_csz - 1 > l_len THEN RAISE_APPLICATION_ERROR(-20016, 'entry data beyond end of jar'); END IF; IF SUBSTR(l_name, -1) = '/' THEN l_kind := 'DIR'; ELSE IF l_meth = 0 THEN -- stored DBMS_LOB.CREATETEMPORARY(l_data, TRUE); IF l_csz > 0 THEN DBMS_LOB.COPY(l_data, l_jar, l_csz, 1, l_dpos); END IF; ELSIF l_meth = 8 THEN -- deflate: gzip header + raw data + CRC32 + ISIZE DBMS_LOB.CREATETEMPORARY(l_gz, TRUE); DBMS_LOB.WRITEAPPEND(l_gz, 10, l_hdr); IF l_csz > 0 THEN DBMS_LOB.COPY(l_gz, l_jar, l_csz, 11, l_dpos); END IF; DBMS_LOB.WRITEAPPEND(l_gz, 4, l_crc); DBMS_LOB.WRITEAPPEND(l_gz, 4, DBMS_LOB.SUBSTR(l_jar, 4, l_p+24)); l_data := UTL_COMPRESS.LZ_UNCOMPRESS(l_gz); DBMS_LOB.FREETEMPORARY(l_gz); ELSE RAISE_APPLICATION_ERROR(-20017, 'unsupported method ' || l_meth); END IF; IF DBMS_LOB.GETLENGTH(l_data) <> l_usz THEN RAISE_APPLICATION_ERROR(-20018, 'size mismatch: got ' || DBMS_LOB.GETLENGTH(l_data) || ', expected ' || l_usz); END IF; IF DBMS_LOB.GETLENGTH(l_data) >= 4 AND DBMS_LOB.SUBSTR(l_data,4,1) = HEXTORAW('CAFEBABE') THEN l_kind := 'CLASS'; -- compiled Java ELSIF DBMS_LOB.INSTR(l_data, HEXTORAW('00')) > 0 THEN l_kind := 'BIN'; ELSE l_kind := 'TEXT'; l_do := 1; l_so := 1; l_lc := DBMS_LOB.DEFAULT_LANG_CTX; DBMS_LOB.CREATETEMPORARY(l_clob, TRUE); IF DBMS_LOB.GETLENGTH(l_data) > 0 THEN DBMS_LOB.CONVERTTOCLOB(l_clob, l_data, DBMS_LOB.LOBMAXSIZE, l_do, l_so, NLS_CHARSET_ID('AL32UTF8'), l_lc, l_w); END IF; END IF; END IF; EXCEPTION WHEN OTHERS THEN l_err := SUBSTR(SQLERRM, 1, 500); l_kind := 'ERR'; END; INSERT INTO art_jar_entry (pk_id, jar_name, integration, entry_name, method, comp_size, uncomp_size, kind, content, txt, err) VALUES (j.pk_id, j.jar_name, j.integ, l_name, l_meth, l_csz, l_usz, l_kind, l_data, l_clob, l_err); IF l_kind = 'ERR' AND l_nl IS NULL THEN EXIT; END IF; -- directory entry unreadable: cannot advance l_p := l_p + 46 + l_nl + l_xl + l_cl; END LOOP; EXCEPTION WHEN OTHERS THEN l_err := SUBSTR(SQLERRM, 1, 500); INSERT INTO art_jar_entry (pk_id, jar_name, integration, kind, err) VALUES (j.pk_id, j.jar_name, j.integ, 'ERR', l_err); END; END LOOP; COMMIT; END; / -- A jar is PARSED_OK / searchable only when it has entries and none of them failed; otherwise PARSE_PARTIAL / not searchable. -- (Entries that did extract stay in ART_JAR_ENTRY and are still searched by ART_SEARCH; the jar is listed in the "left over" report.) UPDATE art_parse p SET status = CASE WHEN EXISTS (SELECT 1 FROM art_jar_entry e WHERE e.pk_id = p.pk_id) AND NOT EXISTS (SELECT 1 FROM art_jar_entry e WHERE e.pk_id = p.pk_id AND e.kind = 'ERR') THEN 'PARSED_OK' ELSE 'PARSE_PARTIAL' END, fmt = 'ZIP+JS', searchable = CASE WHEN EXISTS (SELECT 1 FROM art_jar_entry e WHERE e.pk_id = p.pk_id) AND NOT EXISTS (SELECT 1 FROM art_jar_entry e WHERE e.pk_id = p.pk_id AND e.kind = 'ERR') THEN 'J' ELSE 'N' END, error_msg = (SELECT SUBSTR(MIN(e.err), 1, 500) FROM art_jar_entry e WHERE e.pk_id = p.pk_id AND e.kind = 'ERR') WHERE p.resource_type = 'APILIBRART_JARFILE'; COMMIT; -- ------------------------------ COVERAGE REPORT ------------------------------ -- 1) reconciliation: must show 0 rows "not yet processed" SELECT (SELECT COUNT(*) FROM ics_artifacts) artifacts, (SELECT COUNT(*) FROM art_parse) processed, (SELECT COUNT(*) FROM ics_artifacts a WHERE NOT EXISTS (SELECT 1 FROM art_parse p WHERE p.pk_id=a.pk_id)) not_yet_processed FROM dual; -- 2) parsed vs left, by status SELECT status, searchable, COUNT(*) n, ROUND(100*RATIO_TO_REPORT(COUNT(*)) OVER (),1) pct FROM art_parse GROUP BY status, searchable ORDER BY n DESC; -- 3) by type and status SELECT NVL(resource_type,'(none)') resource_type, status, COUNT(*) n FROM art_parse GROUP BY resource_type, status ORDER BY 1, 3 DESC; -- 4) everything that is NOT searchable (the "what is left" list) SELECT status, resource_type, fmt, ext, resource_path, stored_bytes, error_msg FROM art_parse WHERE searchable NOT IN ('Y','J') AND status <> 'EMPTY_BY_DESIGN' ORDER BY status, resource_type, resource_path; -- ------------------------------ SEARCH EVERY SEARCHABLE RECORD ------------------------------ -- Exact-case, all artifacts (plain ones searched in place on raw bytes, decoded WSDL/XSD in ART_TEXT). -- Usage in SQLcl: DEFINE term = DELETE_DOC then run the statement below. -- (Case-insensitive: use EXEC art_search('term','N') from art_search_install.sql, or LOWER(t.txt) for the decoded part.) /* SELECT p.resource_type, p.status, p.resource_path, r.name integration, r.version, c.name project FROM art_parse p JOIN ics_artifacts a ON a.pk_id = p.pk_id LEFT JOIN ics_resource r ON r.ics_resource_id = p.ics_resource_id LEFT JOIN ics_container c ON c.container_id = r.container_id WHERE p.searchable IN ('Y','R') AND p.enc = 'PLAIN' -- ('J' jars are covered by the ART_JAR_ENTRY branch below) AND DBMS_LOB.INSTR(a.content, UTL_RAW.CAST_TO_RAW('&term')) > 0 UNION ALL SELECT p.resource_type, p.status, p.resource_path, r.name, r.version, c.name FROM art_parse p JOIN art_text t ON t.pk_id = p.pk_id LEFT JOIN ics_resource r ON r.ics_resource_id = p.ics_resource_id LEFT JOIN ics_container c ON c.container_id = r.container_id WHERE DBMS_LOB.INSTR(t.txt, '&term') > 0 UNION ALL SELECT 'LIBRARY_JS', e.kind, e.jar_name || ' :: ' || e.entry_name, e.integration, NULL, NULL FROM art_jar_entry e WHERE e.txt IS NOT NULL AND DBMS_LOB.INSTR(e.txt, '&term') > 0; */