Execution Setup Under Reports Tab Fails with "oracle.jbo.SQLStmtException: JBO-27122" (Doc ID 1587802.1)

Last updated on FEBRUARY 20, 2023

Applies to:

Symptoms

On : Oracle Life Sciences Data Hub Release 2.2.0.4 and higher.

Users unable to select/run Execution Setups under Reports tab. On selecting an Execution Setup under the Reports tab,
the following exception is raised on UI:

"You have encounters an unexpected error. Please contact the System Administrator for assistance".

ERROR STACK
-----------------------
Error Page
Exception Details.
 oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT lov.position AS POSITION,
  lov.value AS LOOKUP_CODE,
  lookup.meaning AS MEANING,
  lookup.tag
FROM cdr_lookups lookup, cdr_lov_values lov
WHERE lov.company_id = :1
  AND lov_id = :2
  AND lov_ver = :3
  AND lov.value = lookup.lookup_code
  AND lookup.lookup_type = :4
  AND (lookup.lookup_code <> '$RUNMODES$INCREMENTAL' OR :5 <> 'N')
  AND (lookup.lookup_type <> 'CDR_BLIND_BREAK' OR lookup.lookup_code <> '$YESNO$YES' OR :6 <> 'N')
  AND (lookup.lookup_code <> '$CURRENCYTYPE$SNAPSHOT' OR :7 <> 'N')
  AND (lookup.lookup_code <> '$CURRENCYTYPE$BACKCHAIN' OR :8 <> 'N')
  AND (lookup.lookup_code <> '$SUBMISSTYPES$TRIGGERED' OR :9 <> 'N')) QRSLT ORDER BY position
  at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
  at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1169)
  at oracle.apps.fnd.framework.webui.OAPageBean.renderDocument(OAPageBean.java:3247)
  at oracle.apps.fnd.framework.webui.OAPageBean.renderDocument(OAPageBean.java:3042)
  at _OA._jspService(_OA.java:228)
  at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
  at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:379)
  at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
  at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
  at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
  at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
  at com.evermind.server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:259)
  at com.evermind.server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:51)
  at com.evermind.server.http.ServletRequestDispatcher$2.oc4jRun(ServletRequestDispatcher.java:193)
  at oracle.oc4j.security.OC4JSecurity.doPrivileged(OC4JSecurity.java:284)
  at com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:198)
  at com.evermind.server.http.EvermindPageContext.forward(EvermindPageContext.java:395)
  at _OA._jspService(_OA.java:221)
  at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
  at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:379)
  at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
  at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
  at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
  at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26)
  at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
  at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318)
  at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
  at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
  at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
  at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
  at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
  at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
  at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
  at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
  at java.lang.Thread.run(Thread.java:619)
## Detail 0 ##
java.sql.SQLException: Missing IN or OUT parameter at index:: 1
  at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1752)
  at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3444)
  at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
  at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
  at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:860)
  at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
  at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3723)
  at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
  at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4560)
  at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:743)
...

STEPS
-----------------------
This issue can be reproduced if you have multiple versions of an Execution Setup and the latest version is set to "Runnable" and
the older version is set to "Runnable Active".  Here are the steps:

1.  Create a new Work Area in the Application Area and assigned Default subtype classification.
2.  Create a SAS Program in the Work Area and assigned Default subtype classification.
3.  Create an Execution Setup ES_SASPrg1 for the SAS Program and assigned Default subtype classification
4.  Open the new Execution setup ES_SASPrg1  and select "Assign classification" from Action drop-down list.
5.  Click Update and select 'Explicit" assignment type.  Under the Search and Add Value, click the '+' icon and add the appropriate Hierarchy Terms.
6.  Select View Version History from Actions menu and select "Set as Active" for ES_SASPrg1 and verified that ES_SASPrg1 has "Runnable Active" status.
7.  Check out Execution Setup ES_SASPrg1 so that it creates another version of ES_SASPrg1.
8.  Select View Version History from Actions drop-down, there should be 2 versions listed as follows:

Name                Version            Status
ES_SASPrg1      2                    Runnable
ES_SASPrg1      1                    Runnable Active

7.  Navigate to Reports tab > select DefaultHierarchy > click Go
8.  Click on the icon under Action for ES_SASPrg1 Execution Set-Up.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.