Databases Reference
In-Depth Information
-- Convert the colon separated string of values
-- into a PL/SQL array
l_selected := HTMLDB_UTIL.STRING_TO_TABLE(p_list);
-- Clean up the intersection table first
-- Delete necessary records only
l_sql_statement :=
'DELETE FROM ' || p_intersection_table || ' ' ||
'WHERE ' || p_master_fk_name || '=' ||
p_master_fk_value || ' ' ||
'AND instr('':'|| p_list ||':'','':''||TO_CHAR('||
p_lookup_fk_name||')||'':'' )=0'
EXECUTE IMMEDIATE l_sql_statement;
-- Loop over the array to insert lookup_ids and
-- master_id into the intersection table
FOR i IN 1..l_selected.count LOOP
-- Check if the record already exists
l_sql_statement :=
'SELECT ' || l_selected(i) ||' ' ||
'FROM ' || p_intersection_table || ' ' ||
'WHERE ' || p_master_fk_name || '=' ||
p_master_fk_value || ' ' ||
'AND ' || p_lookup_fk_name ||'='|| l_selected(i);
BEGIN
-- when the record exists do nothing
EXECUTE IMMEDIATE l_sql_statement INTO l_id;
EXCEPTION WHEN OTHERS THEN
-- In case there is no record, insert it
l_sql_statement :=
'INSERT INTO ' || p_intersection_table || ' ' ||
'(' || p_master_fk_name || ',' ||
p_lookup_fk_name || ') ' ||
'VALUES (' || p_master_fk_value || ',' ||
l_selected(i) || ')';
-- no parent key exception
BEGIN
EXECUTE IMMEDIATE l_sql_statement;
EXCEPTION WHEN OTHERS THEN
NULL; -- logging can be done here!
END;
END;
END LOOP;
END IF;
