Oracle PLSQL: JIRA identifiers clowd matched against ticket keys (for analysis)

jira.identifiers.keycloud.sql

SELECT	distinct
	upper(CAST (keywords.str AS varchar2(32))) AS keyword,
	JP.pkey || '-' || JI.issuenum AS issuekey
FROM
	JIRAACTION jA
JOIN jiraissue JI ON
	JA.issueid = JI.id
JOIN project JP ON
	JI.project = JP.id
CROSS JOIN lateral
	(SELECT
		regexp_substr(ja.actionbody, '(^|\s)(\w+_\w+)($|\s)', 1, LEVEL, NULL, 2) str
	FROM 
		dual
	CONNECT BY
		LEVEL <= regexp_count(ja.actionbody,'(^|\s)\w+_\w+($|\s)')
	ORDER BY LEVEL ASC 
	) keywords
WHERE
	JA.actiontype = 'comment'
	AND REGEXP_LIKE(JA.actionbody, '(^|\s)\w+_\w+($|\s)')
	AND NOT regexp_like(keywords.str, '^_+$')
--	AND rownum < 400
ORDER BY issuekey