Setting the search flag

Recently, I encountered a problem where I needed to make all the fields that were used in request types searchable. By default we don't make all fields searchable. We use our judgement, unless otherwise governed by a Requirement Specification Document, to mark the fields as searchable. Considering that there were many request types and number of fields per request type were large, I didn't think it was a great solution to do all the work manually. A little bit of thinking and I came up with this SQL procedure that would mark all the visible fields on a request type as searchable.

CREATE OR REPLACE PROCEDURE <procedure_name>IS
BEGIN
For i in
(select PARAMETER_SET_FIELD_ID, DISPLAY_FLAG from knta_parameter_set_fields a where parameter_set_context_id
IN
(Select parameter_set_context_id
from knta_parameter_set_contexts
where context_value
IN
(SELECT TO_CHAR(REQUEST_TYPE_ID) FROM KCRT_REQUEST_TYPES WHERE REQUEST_TYPE_NAME IN('<Request_Type_Name>'))
)
)

LOOP
update knta_parameter_set_fields set SEARCHABLE_FLAG = i.DISPLAY_FLAG WHERE
parameter_set_field_ID =
i.PARAMETER_SET_FIELD_ID;
END LOOP;
commit;

EXCEPTION

WHEN others then
rollback;
dbms_output.put_line ('Update of the Field Security Tables
Failed :'sqlcode':'sqlerrm );
END
<procedure_name>;
/

Notice that the name of the request type to work on is hardcoded in the procedure. One enhancement I would suggest is -- make the procedure accept the name of the request type to set the flag in. The script is straight-forward, so I will not include an explanation. Hopefully, you can use it.

- Ranjeet Rain