Oracle Updating value in one collection using another collection

Question

I have a solution that works using for loops but this method is inefficient and performing poorly. I'm new to using collections and can't find my answer on the internet yet. Can anyone point me in the right direction? This is a simplified version of what I'm trying to achieve.

DECLARE
TYPE rec_numbers IS RECORD
(
  DIGIT NUMBER,
  ENGLISH VARCHAR2 (10),
  FRENCH VARCHAR2(10),
  ITALIAN VARCHAR2(10)
);
TYPE tab_numbers IS TABLE OF rec_numbers;
blk_num tab_numbers;
blk_num2 tab_numbers;

BEGIN

SELECT 1 DIGIT, 'ONE' ENGLISH, NULL FRENCH, NULL ITALIAN 
BULK COLLECT INTO blk_num
FROM DUAL;

SELECT 1 DIGIT, NULL ENGLISH, 'UN' FRENCH, 'UNO' ITALIAN 
BULK COLLECT INTO blk_num2
FROM DUAL;    

FOR i IN blk_num.FIRST .. blk_num.LAST LOOP
    FOR j IN blk_num2.FIRST .. blk_num2.LAST LOOP
        IF blk_num(i).digit = blk_num2(j).digit THEN
            blk_num(i).french := blk_num2(j).french;
            blk_num(i).italian := blk_num2(j).italian;
        END IF;
    END LOOP;      
END LOOP;

END;

Why exactly do you want to use collections here? Your version seems to indicate that your'e first loading data from a table into collection and doing some comparison. Why not do all those is pure SQL? — Kaushik Nayak, Mar 07 '19 at 17:42

score 3 · Accepted Answer · answered Mar 07 '19 at 18:38

You have not mentioned why you would need a collection here. If you define a TYPE and collection as schema objects, it is possible to do a simple join query using TABLE functions to do exactly what your for loop does.

CREATE OR REPLACE
         TYPE rec_numbers AS OBJECT 
         ( digit      NUMBER,
         english    VARCHAR2(10),
         french     VARCHAR2(10),
         italian    VARCHAR2(10) );
         /

CREATE OR REPLACE    TYPE tab_numbers AS
              TABLE OF rec_numbers;
              /

Code

SET SERVEROUTPUT ON
DECLARE
     blk_num    tab_numbers;
     blk_num2   tab_numbers;
     blk_num3   tab_numbers;
BEGIN
     SELECT rec_numbers(1,'ONE',NULL,NULL) BULK COLLECT
       INTO blk_num
      FROM dual;

     SELECT rec_numbers(1,NULL,'UN','UNO') BULK COLLECT
       INTO blk_num2
     FROM dual;

SELECT rec_numbers
 ( a.digit,
   a.english, 
   COALESCE(b.french,a.french ),
   COALESCE(b.italian,a.italian)  
  ) BULK COLLECT
  INTO blk_num3
     FROM TABLE   ( blk_num  ) a
  LEFT JOIN TABLE ( blk_num2 ) b 
  ON a.digit = b.digit;
  blk_num := blk_num3;

  for i in 1..blk_num.count
  loop
  dbms_output.put_line(blk_num(i).digit ||','||blk_num(i).english
                ||','||blk_num(i).french||','||blk_num(i).italian
                );
  END LOOP;
END;
/

Output

1,ONE,UN,UNO

PL/SQL procedure successfully completed.

Demo

add a second value to any collection, you'll see that it won't work. the working one demo https://dbfiddle.uk/?rdbms=oracle_18&fiddle=7c2ac2462dcae2735f27740225f74052 — Ravshan Abdulaev, Mar 07 '19 at 19:28
@RavshanAbdulaev : What do you mean by "it won't work" ? Check [this example](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=b5f5cfaddf6bca7f16c56dcb546c70d5) with second value .Also, your solution doesn't work in [Oracle 11g](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=7c2ac2462dcae2735f27740225f74052) or 12c . Moreover you are using outdated `(+)` syntax for `outer` joins rather than ANSI standard explicit joins. See [this](https://stackoverflow.com/questions/1193654/difference-between-oracles-plus-notation-and-ansi-join-notation) — Kaushik Nayak, Mar 07 '19 at 20:14
my mistake, it work's as you say. i can't reproduce the example where somehow i've got unexpected result. can't understand the error of my solution in Oracle 11g or 12c, can you explain it? — Ravshan Abdulaev, Mar 12 '19 at 14:52

Matthew McPeak · Answer 2 · 2019-03-07T18:35:02.230

Nested loops can be slow. If blk_num and blk_num2 each have 1000 records, you are doing 1000x1000 = 1 million iterations. That's O(n^2) performance.

You can get this down to O(n) by taking the time to convert blk_num2 into an associative array after you load it. Then, instead of looping through all the entries in blk_num2, you can find the one you are looking for just by asking for it by its index value.

Here is an example of what I mean:

DECLARE
TYPE rec_numbers IS RECORD
(
  DIGIT NUMBER,
  ENGLISH VARCHAR2 (10),
  FRENCH VARCHAR2(10),
  ITALIAN VARCHAR2(10)
);
TYPE tab_numbers IS TABLE OF rec_numbers;
blk_num tab_numbers;
blk_num2 tab_numbers;
-- Define an associative array to copy blk_num2 into.
-- Note: I did not INDEX BY PLS_INTEGER because I do not know how big DIGIT can be.
-- if DIGIT will always fit into a PLS_INTEGER, you can use that instead.
TYPE assoc_numbers IS TABLE OF rec_numbers INDEX BY VARCHAR2(30);
blk_num2_aa assoc_numbers;


BEGIN

SELECT 1 DIGIT, 'ONE' ENGLISH, NULL FRENCH, NULL ITALIAN 
BULK COLLECT INTO blk_num
FROM DUAL;

SELECT 1 DIGIT, NULL ENGLISH, 'UN' FRENCH, 'UNO' ITALIAN 
BULK COLLECT INTO blk_num2
FROM DUAL;    

-- Copy blk_num2 into blk_num2_aa
FOR i in blk_num2.FIRST..blk_num2.LAST LOOP
  blk_num2_aa(to_char(blk_num2(i).digit)) := blk_num2(i);
END LOOP;

FOR i IN blk_num.FIRST .. blk_num.LAST LOOP
  -- Find the match based on the associative array's index value
  IF blk_num2_aa.exists(to_char(blk_num(i).digit)) THEN
    blk_num(i).french := blk_num2_aa(to_char(blk_num(i).digit)).french;
    blk_num(i).italian := blk_num2_aa(to_char(blk_num(i).digit)).italian;
  END IF;
END LOOP;

END;

score 1 · Answer 3 · answered Mar 07 '19 at 18:50

1

How about this?

update (select * from table(blk_num )) a
set (FRENCH, ITALIAN) = 
    (select FRENCH, ITALIAN 
     from table(blk_num2) b 
     where a.DIGIT = b.DIGIT);

answered Mar 07 '19 at 18:50

Wernfried Domscheit

54,457
9
76
110

Oracle Updating value in one collection using another collection

3 Answers3