If all values have the same number of levels (as in, all list values will have something like A|B|C), then you might be able to do it with a single query. It would be a royal PITA, but I think it could be done. Note that the values are stored hierarchically, and that the leaf values are collected under the parent list value.  Using the “A|B|C” example, C would have the ID for B as it’s parent, and B would have the ID for A as it’s parent. But it can get a bit stranger than that, sometimes.

I show a script below (an attachment would be SO much easier) that uses a PL/SQL procedure that you call with the base ID of the list (in the script, it is 2474530). Update that value, save the script, and execute it in your database. If your values can be longer than 120 characters, update “set line 120” to be something larger such that the values do not wrap. Also note that PL/SQL has a 1MB limit on what it will output, so if your values are long or if you have a LOT of values, the script may error out when it hits a MB of output. If that happens, create a table and save the data into it, and then you can query it any way you want. Note that this will give you entire values, and not the “exploded BOM” view that is what you will see in the Java client. But doing that should be fairly easy to with this script.

set serveroutput on size 1000000
set line 120
set echo off

create or replace procedure GetCVal(pid IN number, prfx IN varchar2, peID IN number) IS

leID number;
leVAL varchar2(500);
prntID number;
pentID number;
lcnt integer;

cursor csrGCL is select entryid,entryvalue,parent_entry from listentry
where parentid = prntID and parent_entry = peID and langid=0 order by entryvalue;

begin
— dbms_output.put_line(‘proc=’ || pid || ‘, pent=’ || peID || ‘ with <‘ || prfx || ‘>’);
begin
select id into prntID from listname where parent_list = pid;
— dbms_output.put_line(‘prnt=’ || prntID || ‘ for ‘ || pid);

select count(*) into lcnt from listentry
where parentid = prntID and parent_entry = peID and langid=0;
if lcnt = 0 then
dbms_output.put_line(‘cval=<‘ || prfx || ‘>, ID=’ || peID);
else
open csrGCL;
loop
fetch csrGCL into leID, leVAL, pentID;
exit when csrGCL%NOTFOUND;

— dbms_output.put_line(‘list id=’ || leID || ‘ –> ‘ || leVAL);
if prfx is null then
GetCVal(prntID, leVAL, leID);
else
GetCVal(prntID, prfx || ‘|’ || leVAL, leID);
end if;
end loop;
close csrGCL;
end if;

exception
when others then
dbms_output.put_line(‘cval=<‘ || prfx || ‘>, ID=’ || peID);
end;
end;
/
show err

declare

begin
GetCVal(2474530, null, 0);

end;
/
show err

drop procedure GetCVal;

set echo off