[SOLVED] ORA-1008 if form variable referenced in ORDER BY Clause

ORA-1008 if form variable referenced in ORDER BY Clause

TLDR; Is there anything I can set in an Oracle Form that would let me bind a placeholder to a Data Block's ORDER BY Clause?

I'm developing a form using Oracle Form Builder 10.1.2.3.0 (because it's interfacing with a system that makes other form types undesirable).

It has a Data Block with Query Data Source Type = Table.

Its WHERE Clause allows the user to be flexible in the search, producing rows of varying interest. I want rows with a perfect match to appear before those that are not.

To implement this specification, I wrote the form's WHERE Clause and ORDER BY Clause to reflect this SQL*Plus example:

var sf varchar2(30)
exec :sf := 'X'

with mdual as (
   select case when level=1 then dummy else dummy || level end dummy
   from dual
   connect by level <= 2
)
select *
from  mdual
where :sf is null or dummy like '%' || upper(:sf) || '%'
order by case when :sf = dummy then 0 else 1 end asc, dummy;

The form variable reference is not as simple as :sf and the WHERE Clause is a bit more complicated as is the ORDER BY Clause but this type of query is valid. When executed in SQL*Plus, it produces exactly the type of result I desire. You can reverse the first sort expression to prove it.

When I execute the form, I get an ORA-1008 until I comment the first ORDER BY expression.

My conclusion is that Oracle Forms binds placeholder references in a WHERE Clause but not an ORDER BY Clause.

I could experiment with setting the Query Data Source Type to Procedure and pass the procedure the filter field but a view has more utility than a procedure and so I'd prefer to keep using the view that I've defined for the Query Data Source Type.

Is there a way I can coerce Oracle Forms to do what I consider the right thing?

Solution

You can use SET_BLOCK_PROPERTY built-in function in order to make it dynamical and depending on a local or bind variable such as

DECLARE
  v_orderby := ' CASE WHEN '||:sf||' = ''dummy'' THEN 0 ELSE 1 END, dummy';
BEGIN
  SET_BLOCK_PROPERTY('block1',ORDER_BY, v_orderby);
  EXECUTE_QUERY;
END;

which might be invoked from a trigger such as WHEN-NEW-BLOCK-INSTANCE after sending cursor to this block by using another action such as clicking on a button or pressing a key such as enter etc.