Tuesday, February 7, 2012

Oracle Searching at Forms, Dynamic Way

We have 20 or more or less then 20 filed in our form. User like to search in the form based on all fields or frequently uses field. If user doesn't want to use "Query Mode" of Forms. How can we achieve this ?
1. Do we create a form with text field for referencing database filed ?
2. Write a complex Query using like operator or lexical parameter ? Or any other way ?

My choice is LOV (List of Values) and a Puss Button.

Create a LOV selecting column as you need. LOV return item should be one and better to be ID column. Don't assign it to any column.
Now at your search button create two trigger.

1. When-Button-Pressed and write something like to post the block state to enter query.
-------------------------------------------------------------------------------------------
BEGIN
 IF :SYSTEM.MODE = 'NORMAL' THEN 
  GO_BLOCK('ITEM_MST');
  CLEAR_BLOCK(NO_COMMIT);
  ENTER_QUERY;
 ELSIF :SYSTEM.MODE = 'ENTER-QUERY' THEN
  ENTER_QUERY;
 ELSIF :SYSTEM.MODE = 'QUERY' THEN 
  GO_BLOCK('ITEM_MST');
  CLEAR_BLOCK(NO_COMMIT);
  ENTER_QUERY;
 END IF;
 EXCEPTION 
  WHEN OTHERS THEN NULL;
END;
-----------------------------------------------------------------------------------------------------
2. When-Mouse-Click and call the LOV and execute query.
------------------------------------------------------------------------------------------------------
BEGIN 
 IF :SYSTEM.MODE = 'ENTER-QUERY' THEN 
  IF SHOW_LOV('CATEGORY_SEARCH') THEN 
   EXECUTE_QUERY;
  END IF;
 END IF;
 IF :System.Record_Status = 'NEW' THEN 
      Exit_Form(No_Validate); 
  END IF; 
END;
-------------------------------------------------------------------------------
 
You have done. 
Need more clarification ? Post your comments. :) 

1 comment: