Hi All,
I have an application where by the user can copy records in a table. I have a procedure that creates the item with a '-copy' at the end of an item name.
I have a request whereby if the user copies the first row, the new record is named -copy, when they copy the initial item again, the new item name should be suffixed with '-copy(1), if they copy it again it should '-copy(2).
The item_name has a unique index so in the unlikely event of there already being a row with that name,, I would need the suffix to be incremented in a similar way that windows explorer does if you copy a file more than once.
I have a started writing a function like the following:
FUNCTION get_item_name (p_item_name IN VARCHAR2)
RETURN VARCHAR2
IS
l_item_name VARCHAR2(250);
l_item_name_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO l_item_name_count
FROM items
WHERE item_name = p_item_name;
IF l_item_name_count > 0 --item name already exists
THEN
--need to add 1 to the suffix and check that
ELSE l_item_name := p_item_name;
END IF;
RETURN l_item_name;
END get_item_name;
Any advice on how I can achieve this would be appreciated.
Thanks
Chris