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.
