Loop using String Value

[HezMac]

Hey

I'm using a string to pass multiple values from VB6 to PL/SQL.

I want to delete certain rows from a table, based on IDs from the string.

I'm not sure how to set up the loop so it knows when to stop.

Here's what I have so far.

PROCEDURE P_REMOVE_RECORDS (in_ID IN VARCHAR2) AS

i_EndPos INT := 0;
i_StrLength INT := 0;
i_StartPos INT := 1;
i_FolderRSN FOLDER.FOLDERRSN%TYPE;
i_Num INT := 1;
v_FRSNNum VARCHAR2(3);

BEGIN

FOR i IN i_RemoveNum LOOP

-- Get the IDs to be deleted from the table
-- they will be in a string from vb app
-- "(1)ID1,(2)ID2,(3)ID3," OR
-- "(1)3855,(2)3856,(3)3857
--
v_FRSNNum := '(' || TO_CHAR(i_Num) || ')';
i_StartPos := INSTR(in_FolderRSN, v_FRSNNum) + 3;
i_EndPos := INSTR(in_FolderRSN, ',', i_StartPos);
i_StrLength := (i_EndPos - i_StartPos);
i_ID := SUBSTR(in_ID, i_StartPos, i_StrLength);

DELETE FROM table
WHERE ID = i_ID;

i_Num := i_Num + 1;

END LOOP;

END P_REMOVE_RECORDS;

Any suggestions would be appreciated. Thanks.

 

[lewisp]

Where is i_FolderRSN initiated or assigned?
Where is i_RemoveNum assigned?

Try a procedure like this:

PROCEDURE P_REMOVE_RECORDS (in_ID IN VARCHAR2) IS
  i_ID VARCHAR2(100);
BEGIN
  i_ID := Substr(in_ID,
                 Instr(in_ID,')') + 1,
                 Instr(in_ID,',') - Instr(in_ID,')') - 1);
--
  DELETE table
  WHERE  id = i_ID;
--
-- Recursive procedure call
--
  IF  Instr(in_ID,',') != 0
  THEN
    P_Remove_Records(Substr(in_ID,
                            Instr(in_ID,',') + 1));
  ELSE
    i_ID := Substr(in_ID,
                   Instr(in_ID,')') + 1);
--
    DELETE table
    WHERE  id = i_ID;
  END IF;
END;