Hello,
Environment info:
- APEX version: 5.1.1.00.08
- DB version: 11.2.0.2.0
- Architecture: APEX listener
- Browser: Chrome 76.0.3809.132
I am not sure whether topic name is adequate. I will describe what I need to do and what is my approach.
I have the table with column which store long strings. This string, contains set of parameters and their keys and values. For example: 'CO1=TEST1, CO2=TEST2, CP1=VAL1, CP2=VAL2, CP3=VAL3, AP=POL, GP=TR1'.
I would like to display this information in APEX, so lets say that for particular record selected by user, I will display modal window with list of keys (parameters names) and values (values assigned to parameters).
My approach:
Please use following table
CREATE TABLE test_table (
id number(10),
test_string varchar2(4000)
);
INSERT INTO test_table
(id, test_string)
VALUES
(1, 'CO1=TESTCONFRNA1,CP1=TESTCONFRNA2,CP2=TESTCONFRNA3,CP3=TESTCONFRNA4,BUC_1=P_BUC_02,BUC_2=H_BUC_01');
INSERT INTO test_table
(id, test_string)
VALUES
(2, 'CO1=TESTCONFRNA1,CP1=TESTCONFRNA2,CP2=TESTCONFRNA3,CP3=TESTCONFRNA4,BUC_1=P_BUC_02,BUC_2=H_BUC_01,BUC_3=LA_BUC_05,BUC_4=P_BUC_10,BUC_5=P_BUC_06,SED_1=P2100,SED_2=P8000,SED_3=H001,SED_4=A010,SED_5=P15000,SED_6=P6000,SED_7=P8000,SED_8=P6000');
INSERT INTO test_table
(id, test_string)
VALUES
(3, 'CO1=TESTCONFRNA1,CP1=TESTCONFRNA2,BUC_1=P_BUC_02,BUC_2=H_BUC_01,BUC_3=LA_BUC_05,BUC_4=P_BUC_10,BUC_5=P_BUC_06,SED_1=P2100,SED_2=P8000,SED_3=H001,SED_4=A010');
INSERT INTO test_table
(id, test_string)
VALUES
(4, 'CO1=TESTCONFRNA1,CP1=TESTCONFRNA2,CP2=TESTCONFRNA3,CP3=TESTCONFRNA4,BUC_1=P_BUC_02,BUC_2=H_BUC_01');
Firstly I need to cut selected string. I did this using table function:
create or replace FUNCTION comma_to_table(
p_list IN VARCHAR2)
RETURN test_type
AS
l_string VARCHAR2(32767) := p_list || ',';
l_comma_index PLS_INTEGER;
l_index PLS_INTEGER := 1; l_tab test_type := test_type();
BEGIN
LOOP
l_comma_index := INSTR(l_string, ',', l_index);
EXIT
WHEN l_comma_index = 0;
l_tab.EXTEND;
l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,
l_index,
l_comma_index - l_index
)
);
l_index := l_comma_index + 1;
END LOOP;
RETURN l_tab;
END comma_to_table;
After that, I was going to use comma_to_table function to cut sting and take records from this table.
declare
v_parameter varchar2(1000);
v_parameter_number number := 0;
v_string_to_parse varchar2(32000);
v_size number;
v_helper number := 0;
cursor c_string_parameters is
select * from
table (comma_to_table(v_string_to_parse));
r_string_parameters c_string_parameters%ROWTYPE;
begin
select parameter_url into v_string_to_parse from test_table where id = 1;
dbms_output.put_line('String to parse: ' || v_string_to_parse);
select count(*) into v_size from (select * from table (comma_to_table(v_string_to_parse)));
dbms_output.put_line('Size: ' || v_size);
open c_string_parameters;
loop
fetch c_string_parameters into r_string_parameters;
exit when c_string_parameters%NOTFOUND;
dbms_output.put_line('Parameter: ' || r_string_parameters);
end loop;
end;
/
Of course it is not working. Do you know how to do that? What is the best approach to do what I need? Thank you in advance for help.
Best Regards,
Lukasz