Thursday, 1 December 2011

Querying on a non database field in Oracle Forms

Here is small piece of code for Querying on a non database field in the Custom Oracle Form.

Steps to be followed :


1.  Sample Block :  XXSS_BLK
               prd_name    -   Non database, read only field.
               prd_id         -   Database, hidden field
               start_date    -   Database field
               end_date     -   Database field.



2. Add a Pre-Query trigger on the block , where it contains a non database fields for querying.

3. Sample code for the Pre - Query trigger :

    l_where       VARCHAR2(250) := 'Where 1 = 1';

BEGIN
           l_where :=  l_where || 'AND prd_id IN (SELECT product_id FROM product '
|| ' WHERE product_name like '''||'%'||:XXSS_BLK.PRD_NAME||'%'')';


SET_BLOCK_PROPERTY('BLOCK_NAME',DEFAULT_WHERE,l_where);

END;

These steps should allow querying on the non database fields in the Custom Oracle form.

The same can be modified according to the requirements.

Hope this helps.