ADF how to: errors related to bind variables

I accidentally had found an old manual relating to errors related to "bind variables", so I decided share its fragments:

JBO-27122: SQL error during statement preparation

You need to ensure that the list of named bind variables that you reference in your SQL statement matches the list of named bind variables that you've defined on the Bind Variables page of the View Object Editor. Failure to have these two agree correctly can result in one of the following two errors at runtime.
If you use a named bind variable in your SQL statement but have not defined it, you'll receive an error like this:

(oracle.jbo.SQLStmtException) JBO-27122: SQL error during statement preparation.
## Detail 0 ##
(java.sql.SQLException) Missing IN or OUT parameter at index:: 1

 On the other hand, if you have defined a named bind variable, but then forgotten to reference it or mistyped its name in the SQL, then you will see an error like this:

oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.
## Detail 0 ##
java.sql.SQLException: Attempt to set a parameter name that does not occur in the SQL: LowUserId

Valid query returns no rows

If you do not supply a default value for your named bind variable, it defaults to the NULL value at runtime. This means that if you have a WHERE clause like:

USER_ID = :TheUserId

and you do not provide a default value for the TheUserId bind variable, it will default to having a NULL value and cause the query to return no rows. Where it makes sense for your application, you can leverage SQL functions like NVL(), CASE, DECODE(), or others to handle the situation as you require. In fact, the UserList view object uses a WHERE clause fragment like:

upper(FIRST_NAME) like upper(:TheName)||'%'

so that the query will match any name if the value of :TheName is null.

Error ORA-00904 when adding a Named Bind Variable at Runtime

Assume following situation. Using setNamedWhereClauseParam() method you created code:

ViewObject vo = am.findViewObject("EmployeeList");
vo.setNamedWhereClauseParam("TheName","Jon%");
vo.setNamedWhereClauseParam("HighUserId", new Number(100));
vo.executeQuery();

However, if you run this test program, you actually get a runtime error like this:

oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.
Statement: 
SELECT * FROM (select USER_ID, EMAIL, FIRST_NAME, LAST_NAME
from EMPLOYEES 
where (upper(FIRST_NAME) like upper(:TheName)||'%'
   or  upper(LAST_NAME)  like upper(:TheName)||'%')
  and USER_ID between :LowUserId and :HighUserId
order by EMAIL) QRSLT  WHERE (user_role = :TheUserRole)
## Detail 0 ##
java.sql.SQLException: ORA-00904: "USER_ROLE": invalid identifier

The root cause of this error is the mechanism that ADF view objects use by default to apply additional runtime WHERE clauses on top of read-only queries.

If you dynamically add an additional WHERE clause at runtime to a read-only view object, its query gets nested into an inline view before applying the additional WHERE clause. For example, suppose your query was defined as:

select USER_ID, EMAIL, FIRST_NAME, LAST_NAME
from EMPLOYEES 
where (upper(FIRST_NAME) like upper(:TheName)||'%'
   or  upper(LAST_NAME)  like upper(:TheName)||'%')
  and USER_ID between :LowUserId and :HighUserId
order by EMAIL 

At runtime, when you set an additional WHERE clause like user_role = :TheUserRole, the framework nests the original query into an inline view like this:

SELECT * FROM(
select USER_ID, EMAIL, FIRST_NAME, LAST_NAME
from EMPLOYEES 
where (upper(FIRST_NAME) like upper(:TheName)||'%'
   or  upper(LAST_NAME)  like upper(:TheName)||'%')
  and USER_ID between :LowUserId and :HighUserId
order by EMAIL) QRSLT

and then adds the dynamic WHERE clause predicate at the end, so that the final query the database sees is:

SELECT * FROM(
select USER_ID, EMAIL, FIRST_NAME, LAST_NAME
from EMPLOYEES 
where (upper(FIRST_NAME) like upper(:TheName)||'%'
   or  upper(LAST_NAME)  like upper(:TheName)||'%')
  and USER_ID between :LowUserId and :HighUserId
order by EMAIL) QRSLT
WHERE user_role = :TheUserRole

This query "wrapping" is necessary in the general case since the original query could be arbitrarily complex. In those cases, simply "gluing" the additional runtime onto the end of the query text could produce unexpected results. By nesting the original query verbatim into an inline view, the view object guarantees that your additional WHERE clause is correctly used to filter the results of the original query, regardless of how complex it is. The downside that you're seeing here with the ORA-00904 error is that the dynamically added WHERE clause can refer only to columns that have been selected in the original query.