Oracle Forms - searching on a non-database item

Question

I have a data block OBJECTS with four items:

• ID
• TYPE_ID (foreign key, named ID in the table TYPES)
• TYPE_DESCRIPTION (non-database item, named DESCRIPTION in the table TYPES)
• STA_ID (non-relevant)

In the form I want to be able to query on TYPE_DESCRIPTION (non-database item), by typing a phrase, like "S%" or "%P" and get a result for all the columns, filtered by TYPE_DESCRIPTION.

Screenshots: https://i.stack.imgur.com/quxR2.jpg

Answer 1

I found a solution to the problem. Here's the code for the PRE_QUERY trigger:

declare 
where_str varchar2(512) := 'where 1 = 1';
begin

if (:OBJECTS.TYPE_DESCRIPTION is not null) then
  if instr(:OBJECTS.TYPE_DESCRIPTION, '%') <> 0 then
    where_str := where_str ||
    'and OBJECTS.TYPE_ID in (select TYPES.ID from TYPES ' ||
    'where TYPES.DESCRIPTION like '''|| replace(:OBJECTS.TYPE_DESCRIPTION, '''', '''''') ||''')';
  else
    where_str := where_str ||
    'and OBJECTS.TYPE_ID in (select TYPES.ID from TYPES ' ||
    'where TYPES.DESCRIPTION='''|| replace(:OBJECTS.TYPE_DESCRIPTION, '''', '''''') ||''')';
  end if;
end if; 

set_block_property('OBJECTS', default_where, where_str);

end;

http://oracleebsgeeks.blogspot.com/2011/12/querying-on-non-database-field-in.html