Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to build cursor based on values / parameters in given string

LukSkyWalkerJun 23 2020 — edited Aug 20 2020

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

This post has been answered by Mike Kutz on Jun 23 2020
Jump to Answer

Comments

cormaco

How about creating a view like this and then selecting from it:

create or replace view test_view as

select

    id,

    substr(regexp_substr(test_string,'[^,]+',1,level),1,instr(regexp_substr(test_string,'[^,]+',1,level),'=') - 1) as key,

    substr(regexp_substr(test_string,'[^,]+',1,level),instr(regexp_substr(test_string,'[^,]+',1,level),'=') + 1) as val

from test_table

connect by

    level <= regexp_count(test_string,',') + 1 and

    prior id = id and

    prior sys_guid() is not null;

select * from test_view

where id = 1;

        ID KEY        VAL                

---------- ---------- --------------------

         1 CO1        TESTCONFRNA1       

         1 CP1        TESTCONFRNA2       

         1 CP2        TESTCONFRNA3       

         1 CP3        TESTCONFRNA4       

         1 BUC_1      P_BUC_02           

         1 BUC_2      H_BUC_01           

Mike Kutz
Answer

My Car Is Broken

You provide no information about what is broken or even the error that is thrown.

UPGRADE

11gXE (11.2.0.2) is old. you need to upgrade that to 18cXE or use the Oracle Cloud Free Tier Autonomous Transaction Processing (ATP) which is running 19c.

APEX 5.1 ugh!!!

APEX Listener - its now ORDS.  You really should upgrade.

CODE NOTES

JSON strings can be easily parsed with JSON_TABLE (12c)

I have no idea why you wrote your STRING_TO_TABLE.  APEX_UTILS already had one.  Newer versions of APEX now uses APEX_STRING.SPLIT.

Extract the data as is will be as simple as:

with test_table( id, test_string) as (

  select 1, 'CO1=TESTCONFRNA1,CP1=TESTCONFRNA2,CP2=TESTCONFRNA3,CP3=TESTCONFRNA4,BUC_1=P_BUC_02,BUC_2=H_BUC_01' from dual union all

  select 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' from dual union all

  select 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' from dual union all

  select 4, 'CO1=TESTCONFRNA1,CP1=TESTCONFRNA2,CP2=TESTCONFRNA3,CP3=TESTCONFRNA4,BUC_1=P_BUC_02,BUC_2=H_BUC_01' from dual

)

select a.id, b.column_value

  ,substr( b.column_value, 1, instr(b.column_value, '=') - 1) param_name

  ,substr( b.column_value, instr(b.column_value, '=') + 1) param_value

from test_table a

  ,table( apex_string.split( a.test_string, ',' )) b;

MK

PS - please stop with the "fancy colorful format".  It is actually annoying.

Marked as Answer by LukSkyWalker · Sep 27 2020
LukSkyWalker

Thank you guys for answers. Mike's is the best in my situation. I don't need to create any table or view. This solution works perfect.

Upgrading DB and APEX is not up to me unfortunately. Thank you for suggestion.

I will not use "colorful format" any more. Actually it was added by mistake. I use "Night Eye" on my browsers and I haven't seen that background is black.

1 - 3

Post Details

Added on Jun 23 2020
3 comments
430 views