SQL & PL/SQL

Hi,

I have a procedure which are contains 3 input parameters with comma separated value.

For example:

i_para_1 ---> 12,13,14
i_para_1 ---> A,B,C
i_para_1 ---> X,Y,Z

I have a table type array to store all the above input values.

For example:

TYPE user_input_type IS RECORD
(size_feature_flg NUMBER := NULL,
match_criteria_flg VARCHAR2(20) := NULL,
alternate_torque_flg VARCHAR2(20) := NULL
);

TYPE user_input_tbl_type IS TABLE OF user_input_type INDEX BY BINARY_INTEGER;

v_user_input_tbl_type user_input_tbl_type;
v_sizing_index BINARY_INTEGER;

Now i have to add the data into the table type array like below.

12,A,X is the one row.
13,B,Y is the second row.
14,C,Z is the third row.

I have a string tokenizer procedure to separate all the comma separted values.
By use of that procedure we should separate the string and populate into the array table type. Below is the function.


PROCEDURE tokenize_string (
i_start IN NUMBER,
i_pattern IN VARCHAR2,
i_buffer IN VARCHAR2,
o_result OUT VARCHAR2,
o_nextpos OUT NUMBER
)
AS
v_npos1 NUMBER;
v_npos2 NUMBER;
BEGIN
v_npos1 := INSTR (i_buffer, i_pattern, i_start);

IF v_npos1 = 0
THEN
o_result := NULL;
ELSE
v_npos2 := INSTR (i_buffer, i_pattern, i_start + 1);

IF v_npos2 = 0
THEN
o_result := RTRIM (LTRIM (SUBSTR (i_buffer, v_npos1 + 1)));
o_nextpos := v_npos2;
ELSE
o_result := SUBSTR (i_buffer, v_npos1 + 1, v_npos2 - v_npos1 - 1);
o_nextpos := v_npos2;
END IF;
END IF;
END tokenize_string;