JBO-25058: Definition vc_temp_1 of type Variable is not found in ...

"JBO-25058: Definition vc_temp_1 of type Variable is not found in ...".
What can be the reason?
ADF creates/reuses application module (AM) instance for each user request. It is passivated (saved) after each HTTP request (if configured accordingly). When in application there are used some queries with view criteria, filter variables are passivated  for according AM as well. During the next HTTP request of the same user it is recalled and used in processing.
But... There are couple important AM parameters:

  • Idle Instance Timeout (s) = 600 (default)
  • Pool Polling Interval (s) = 600 (default)

In my case I've got JBO-25058 error when trying to re-execute query with view criteria after 600s. Simply my referenced AM module with view criteria variable was purged before...
So the fix was simple - just increased mentioned AM parameters to 1800s. I've choosed such period because session timeout in my app is the same. That means user will not experience such error because session simply ends before.

How to create XMLIndex? When its worth to use it?

How to create XML index?

Very general way with all default applied as follows:

create index index_name on table_name (xmltype_column) indextype is xdb.xmlindex;

Check the name of PATH table

select path_table_name from user_xml_indexes  where table_name = 'table_name' and index_name = 'index_name'; 

Check what secondary indexes are used

select c.index_name, c.column_name, c.column_position, e.column_expression
  from user_ind_columns c left outer join user_ind_expressions e on (c.index_name = e.index_name)
where c.table_name in (select path_table_name from user_xml_indexes
where index_name ='index_name')
order by c.index_name, c.column_name;

How to turn off the use of XMLIndex?

Use one of optimizer hints:
/*+ NO_XMLINDEX_REWRITE */
/*+ NO_XMLINDEX_REWRITE_IN_SELECT */
/*+ NO_XML_QUERY_REWRITE */

When to use XML index ?
We can benefit from XMLIndex when queries with filter predicates from inside XML document exist. And there is no outside key, which could return particular XML doc for further processing.

Example:

select t.id, xt.id, xt.name
from
test_table t,
xmltable(
xmlnamespaces(default 'http://my.namespace.com'),
'/element' passing t.xml_content
columns
id path '/activity/@id',
name path '/activity/name'
) xt
where t.xml_content is not null
) where xt.name ='BUBU' and t.id='ID'

Here we have "xt.name" from inside XML. If would use only this filter criteria with "t.id" absent, XMLIndex would be very beneficial to find particular row with "/activity/name/BUBU". For DB engine it should be quite easy to do that, because "/activity/name" will be available in index path table (if created by default). If "t.id" in filter predicate is present, "test_table" is first filtered on "t.id", then particular XML doc is passed for xpath processing. Therefore we not experience any XMLIdex advantages.

Thus - use XMLIndex when can't uniquely identify row with XML content using only columns of outside table (i.e. column "id" of table "test_table").

Learn more about XMLIndex from Oracle XML DB Developer's guide.