JIRA: Get workflows where the post-function refers to a plugin

Note, that JIRAWORKFLOWS.DESCRIPTOR is a valid XML, stored in a CLOB.

WITH W_JIRAWORKFLOWS AS
  ( SELECT WORKFLOWNAME,
      XMLTYPE.CREATEXML( REPLACE(DESCRIPTOR,
                '<!DOCTYPE workflow PUBLIC "-//OpenSymphony Group//DTD OSWorkflow 2.8//EN" "http://www.opensymphony.com/osworkflow/workflow_2_8.dtd">'
        )) AS DESCRIPTOR_XML
    FROM JIRAWORKFLOWS)
SELECT
  WS.NAME      AS WF_SCHEME_NAME,
  WSE.WORKFLOW AS WF_ENTITY_WORKFLOW,
  IT.PNAME     AS ISSUETYPE_NAME,
  XMLQUERY(
      'string-join(distinct-values(//post-functions/function/arg[@name="class.name" and not(starts-with(., "com.atlassian.jira.workflow"))]/text()), ",")'
      PASSING DESCRIPTOR_XML
      RETURNING CONTENT
    ).getStringVal() AS PLUGIN_CLASSNAMES
FROM WORKFLOWSCHEMEENTITY WSE
LEFT JOIN ISSUETYPE IT
ON IT.ID = WSE.ISSUETYPE
LEFT JOIN WORKFLOWSCHEME WS
ON WS.ID = WSE.SCHEME
INNER JOIN W_JIRAWORKFLOWS W ON W.WORKFLOWNAME = WSE.WORKFLOW
WHERE XMLEXISTS('//post-functions[function/arg[@name="class.name" and not(starts-with(., "com.atlassian.jira.workflow"))]]'
      PASSING BY VALUE DESCRIPTOR_XML);

This way you can see, what kind of Workflows you have, that has post-functions bound to plugins.

As you see, it is using Oracle XDB features, so you might need some rework when using another RDBMS. Also, the cutting out of the DTD is neccessary, as by default, XDB does not allow to load XML-s that have external references (here, the DTD has one).