You can create the procedure below, then call it with the 4 parameters (Current Saved Search Name, New Saved Search Name, Current Username, New UserName) . So, using this you can
1) Copy it to another user by using the format CopySearch('Saved Search Name', 'Saved Search Name', 'Current Username', 'New Username')
2) Make a copy for yourself by using the format CopySearch('Saved Search Name', 'New Search Name', 'Current Username', 'Current Username')
CREATE OR REPLACE
PROCEDURE CopySearch(from_search_name IN VARCHAR2, to_search_name IN VARCHAR2, from_user IN VARCHAR2, to_user IN VARCHAR2) IS
v_new_search_id NUMBER;
v_current_search_id NUMBER;
v_new_search_filter_id NUMBER;
v_max_seq NUMBER;
v_from_userid NUMBER;
v_to_userid NUMBER;
BEGIN
SELECT user_id INTO v_from_userid FROM knta_users WHERE upper(username) LIKE upper(from_user);
SELECT user_id INTO v_to_userid FROM knta_users WHERE upper(username) LIKE upper(to_user);
SELECT saved_search_id INTO v_current_search_id FROM knta_saved_searches WHERE upper(saved_search_name) = upper(from_search_name);
SELECT MAX(saved_search_id)+1 INTO v_new_search_id FROM knta_saved_searches;
SELECT MAX(seq)+1 into v_max_seq FROM knta_saved_searches where created_by = v_from_userid;
INSERT INTO knta_saved_searches
SELECT v_new_search_id saved_search_id, SYSDATE creation_date, v_to_userid created_by, SYSDATE last_update_date, v_to_userid last_udated_by,
to_search_name saved_search_name, search_category_id, v_max_seq seq, entity_id, search_type_id
FROM knta_saved_searches WHERE created_by = v_from_userid AND saved_search_id=v_current_search_id
GROUP BY search_category_id, seq, entity_id, search_type_id;
SELECT MAX(saved_search_filter_id)+1 INTO v_new_search_filter_id FROM knta_saved_search_filters;
FOR f IN (SELECT * FROM knta_saved_search_filters WHERE saved_search_id = v_current_search_id)
LOOP
f.saved_search_filter_id := v_new_search_filter_id;
f.creation_date := SYSDATE;
f.created_by := v_to_userid;
f.last_update_date := SYSDATE;
f.last_updated_by := v_to_userid;
f.saved_search_id := v_new_search_id;
INSERT INTO knta_saved_search_filters VALUES f;
v_new_search_filter_id := v_new_search_filter_id + 1;
END LOOP;
Commit;
END CopySearch;
-- Remember to give Kudos to answers! (click the KUDOS star)
