Hello,
I have a field (Checkbox) called P31_TYPE_MAINT_CODE from which I need it to accept one or more values. Currently I created another field (Text Field called P31_TYPE_MAINT_CODE_ID_1) in which I can see the output of my Checkbox selection. As an example I selected 3 values being them B, S and Y and the out put was B:S:Y but that format it is not acceptable by the Oracle database. Then I created another field (Text Field called P31_TYPE_MAINT_CODE_ID_2) with the shown below PL/SQL included to change the format to what I believe is the correct one which is 'B', 'S', 'Y' and the output worked.
DECLARE
l_input varchar2(50);
l_remainings varchar2(500);
l_pos number := 0;
l_input_full varchar2(500);
BEGIN
l_remainings := :P31_TYPE_MAINT_CODE_ID_1 || ':';
WHILE INSTR(l_remainings, ':') > 0
LOOP
l_pos := INSTR(l_remainings, ':');
l_input := SUBSTR(l_remainings, 0, l_pos - 1);
l_remainings := SUBSTR(l_remainings, l_pos + 1, LENGTH(l_remainings));
l_input_full := l_input_full || '''' || l_input || ''',';
END LOOP;
l_input_full := SUBSTR(l_input_full, 0, LENGTH(l_input_full) - 1);
DBMS_OUTPUT.PUT_LINE(l_input_full);
RETURN l_input_full;
END;
That output should be the filter of an SQL code so I wrote:
SELECT FIELD_1, FIELD_2, FIELD_3
FROM RESPECTIVE_TABLE
WHERE TYPE_MAINT_CODE IN :P31_TYPE_MAINT_CODE_ID_2
Everything says its okay but then the output from my SQL code comes out blank. And if I hard code the filter as shown below which I think is the same thing then I get the data retrieved.
SELECT FIELD_1, FIELD_2, FIELD_3
FROM RESPECTIVE_TABLE
WHERE TYPE_MAINT_CODE IN ('B', 'S', 'Y')
Does somebody could help me on this or know what is happening or why I am not getting the wanted output?