Finding instances using wildcard search on title field in SOAINFRA schema

This post is about finding instances using wildcard search by querying SOAINFRA schema.

Edited on 6th Feb: Wild Card search in EM on instance title works in 11g and 12c . Tested with 11.1.1.7.8 and 12.2.1.

Specific composite instance can be located in EM using  composite instance’s title and composite sensor’s value. Please refer  Locate Instances using title and sensors in EM post for more information.

It is also possible to query SOAINFRA schema tables to get specific composite instance. One big advantage with back end querying is , wildcards can be used in the  query.

In 12c, “Title” values set using “setFlowInstanceTitle” is stored in “sca_flow_instance” table.

Connect to backend Database. Please refer Create Connection to JavaDB post, for connecting and querying in JavaDB.

Executing   select * from sca_flow_instance where title like ‘or%7’  will display instance id of all composites whose title match the wildcard criteria ‘or%7’

Query Sca_Flow_Instance table
Query Sca_Flow_Instance table

 

3 thoughts on “Finding instances using wildcard search on title field in SOAINFRA schema

  1. Hi

    We are using below mention query to get SOA infra data to create support ticket. Basically our requirement to get title and composite name,domain name etc to create support ticket.

    SELECT t1.CIKEY, t1.COMPOSITE_NAME, t1.DOMAIN_NAME, t1.COMPONENT_NAME, t1.COMPOSITE_REVISION, t1.FLOW_ID, t0.FLOW_ID, t0.TITLE FROM SCA_FLOW_INSTANCE t0, CUBE_INSTANCE t1 WHERE (((t1.FLOW_ID = #flowID) AND (t1.COMPONENT_NAME = #componentName)) AND (t0.FLOW_ID = t1.FLOW_ID))

    This query is working fine when running through SQL developer and giving result.But when we put same query under SOA database adapter and run through BPEL then query will not come with any output.The output parameter is empty.

    I short same query is working fine SQL developer but not through BPEL database adapter.Please help.

    1. Ranjit,
      If it works in SQL developer, I expect it to work via DB adapter also.
      Do you see any errors in soa log?. Also, enable logging for soa adapters and see whether any specific error is listed in the log file.

Leave a Reply

Your email address will not be published. Required fields are marked *