pl/sql nested table loop

Question

I have a pl/sql nested table collection that contains the below elements:

AG~AG~1~14
US~BRANCH~1~24
NO~NO~2~10
KI~296~2~13
AI~AI~2~21

I have to look at the value in the 3rd subelement with each of these elements(not sure how to put it !) and pick the one that have the highest value. In the above data, the 3rd subelements are 1,1,2,2,2 (immediately following the second ~). Obviously here, the elements containing 2 are higher. So, 3 of them meet the criteria. Further, from the 3 of them, I have to look at the 4th subelement and ultimately pick the one that has the highest value. So, the 4th subelement for these 3 are: 10,13,21 Since 21 is the highest, the final output is to pick the element AI~AI~2~21.

I am struggling how to do this in the best possible way. I tried various combinations of instr and substr and looped them to compare. But, its not modular enough. I could also tokenize the string from each element and push into a global temp table that is valid for the session and then use oracle sql to get the final data. but, I want to avoid having to use a table and maintain it etc and if possible, like to keep it within pl/sql.

declare
TYPE final_score_typ IS TABLE OF varchar2(1000);

l_final_score final_score_typ;

l_final_output varchar2(20);

begin

<code logic that populates the nested table containing the above data>

for j in 1..l_final_score.count loop

    dbms_output.put_line(l_final_score(j));

end loop;

dbms_output.put_line('final output string is:' || l_final_output);


end;

The final output should be printed as AI~AI~2~21 Any pointers would be much appreciated. I could atleast try based on them...Right now, cannot think of good alternatives.

Egor Skriptunoff · Accepted Answer · 2014-08-29 22:15:53Z

1

select min(column_value) keep (dense_rank first order by 
  to_number(regexp_substr(column_value, '[^~]+', 1, 3)) desc, 
  to_number(regexp_substr(column_value, '[^~]+', 1, 4)) desc
) 
into l_final_output 
from table(l_final_score)

Note: type must be global:

create TYPE final_score_typ AS TABLE OF varchar2(1000);

edited Aug 29, 2014 at 22:15

answered Aug 29, 2014 at 22:08

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Casey Over a year ago

Thanks. But, how do I access this global collection type in my pl/sql block to populate those values ? In my original post, I had declared a local pl/sql nested table collection type and populated the initial values. I created a global type as you had suggested by the name final_score_typ. However, accessing it inside my pl/sql block does not work. I declared this way: l_final_score final_score_typ := final_score_typ(); It gives me a compilation error: "Identifier final_score_typ" must be declared.

Casey Over a year ago

If the collection has data such that it could produce a tie, then the above query does not work and I am not sure how to get all those tie data back as a result. values: final_score_typ('KI~296~1~9','US~CA~2~11','CA~CA~2~11','KP~KOREA~2~14','KR~KOREA~2~14','US~CA~3~19','CA~CA~3~19') Here, both 'US~CA~3~19' and 'CA~CA~3~19' are valid as their 3rd subelement is highest and their 4th is the same value of 19 and hence produce a tie. Here, my output should be to return them both. Currently, the above query picks 'CA~CA~3~19' based on the min logic.

Tony Andrews · Accepted Answer · 2014-08-30 10:57:28Z

There is an existing database type you can use for a table of VARCHAR2(1000): `

SYS.DBMS_DEBUG_VC2COLL

You could use it like this:

declare
   l_data SYS.DBMS_DEBUG_VC2COLL := SYS.DBMS_DEBUG_VC2COLL
                                       ('AG~AG~1~14'
                                       ,'US~BRANCH~1~24'
                                       ,'NO~NO~2~10'
                                       ,'KI~296~2~13'
                                       ,'AI~AI~2~21'
                                       );
   l_result varchar2(100);
begin
   select column_value 
   into l_result
   from
   ( select column_value 
          , row_number() over
               (order by substr(column_value
                               ,instr(column_value,'~',1,2)+1
                               ,instr(column_value,'~',1,3)
                                    -instr(column_value,'~',1,2)-1
                               ) desc
               ,         substr(column_value,instr(column_value,'~',1,3)+1) desc
               ) as rn
     from table (l_data)
   ) where rn = 1;
   dbms_output.put_line(l_result);
end;

(Or you could use it with Egor's much more elegant regexp_substr code of course.)

Collectives™ on Stack Overflow

pl/sql nested table loop

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related