Array OF HOPE DAN STOBER - PowerPoint Presentation

Array OF HOPE DAN STOBER INTERMOUNTAIN HEALTHCARE MARCH 21, 2018

DAN STOBER Intermountain Healthcare Data Warehousing and Infrastructure (*EDW) Consultant Data ArchitectCalifornia State University FresnoServed on the Board of Utah Oracle Users Group (2010-2012)Board of Trustees, Health Care Credit Union (2011-2017)Part-time instructor – University of Utahhttp://sqlunderground.blogspot.com/https://selfietravelerblog.wordpress.com/

MY APOLOGIES The award for worst pun in the naming of a full-length presentation goes to … (… the envelope please…)

Agenda Overview What is an array? What is available in PLSQL?Declaring and populating an arrayFunctions and ProceduresUse cases

Utah gained statehood on January 4, 1896, as the 45th state. Which of these states was admitted to the union later than Utah? Oklahoma Nevada WyomingIdaho

What is an array structure? Single variable with multiple values Indexed into positions Most programming languages have them in one form or anotherUnderused in PLSQLString[] state; state[0]="Arizona"; state[1]="Iowa"; state[2]="Tennessee"; for ( int i=0;i< state.length;i ++ ) { Declare variable Populate array Access values Directly Iteration

COLLECTIONS IN PLSQL VARRAY Nested Table Associative ArrayAlso known previously as:PLSQL table“Index by” Table Associative Array Size defined at runtime No need to declare size or upper bound Add more elements as needed Index may be “dense” or “sparse” The datatype in the array can be a single field or a record Record declared previously or using %ROWTYPE Indexed by numbers or by varchar2 Identified by keywords “INDEX BY”

Declaring an Associative Array Declaration in PLSQL requires two steps: Declare the array structure Declare the variable itself TYPE my_array_t IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; v_state my_array_t ; Keyword “TYPE”, followed by a name for the new datatype being declared . Type of data being stored in the array. “ INDEX BY” then datatype for values being used to index the array “IS TABLE OF” Keywords required in this statement; Defines array structure Declaration of the variable; Assign to it the datatype created previously

ALLOWABLE INDEX TYPES BINARY_INTEGER and PLS_INTEGER are identical in implementation VARCHAR and VARCHAR2 are identical, too Limit is 32767 characters (in 11g)Not permitted: Indexing by NUMBER, INTEGER, CHAR, DATE . . . INDEX BY BINARY_INTEGER; . . . INDEX BY PLS_INTEGER; . . . INDEX BY VARCHAR2(25); . . . INDEX BY VARCHAR(25); PLS-00315: Implementation restriction: unsupported table index type Numeric index options String index options

Naming the datatype Remember: You can store any valid PLSQL datatype in an arrayAnd, you can index by any numeric or Varchar2 datatypeSo, I use a naming convention when I declare a typeI often have several different types of arrays in use in a single programRemember, the name you supply for your array datatype is completely up to you; these are just my suggestions TYPE varchar_by_number_t IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; TYPE number_by_varchar_t IS TABLE OF NUMBER(10) INDEX BY VARCHAR2(25);

Accessing an Associative Array Use the variable name, followed by the index position in parentheses To assign values and to read values The index position is created the first time it is referencedNo constructor is necessaryValue can be updated with a reassignmentIndex positions also can be accessed using variablesThere is no concept of a "pointer" or active value v_state (1) := 'Arizona'; v_state (2) := 'Iowa'; v_state (3) := 'Tennessee'; dbms_output.put_line ( v_state (2) ); v_state (2) := 'Hawaii'; a := 2; dbms_output.put_line ( v_state (a) ); 1 Arizona 2 Iowa 3 Tennessee Hawaii Iowa Iowa Hawaii

Indices can be sparse The index positions do not have to be filled consecutively DECLARE TYPE varchar_by_number_t IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER; x varchar_by_number_t ; BEGIN x(1) := 'Bleu'; x(2) := 'Blanc'; x(4) := 'Rouge'; x(5) := 'Noir'; END; This array is “sparse”. Position (3) does not exist - - - - - - - - - - - - - - - - - - This array contains FOUR elements

UNIQUENESS Keys MUST be unique Values in the array do not have to be unique x(1) := 'A'; x(2) := 'B'; x(4) := 'A'; x(5) := 'C'; z(1) := 'Dick York'; z(2) := 'Elizabeth Montgomery'; z(1) := 'Dick Sargent'; Reusing the same index is not an error The value is replaced with the new value

Errors Accessing an index value which does not exist raises no_data_found DECLARE TYPE varchar_by_number_t IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER; x varchar_by_number_t ; BEGIN x(1) := 'Bleu'; x(2) := 'Blanc'; x(4) := 'Rouge'; dbms_output.put_line (x(3)); END; DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 8

Errors NULL Index error DECLARE TYPE varchar_by_number_t IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER; x varchar_by_number_t ; a INTEGER := 1; b INTEGER := 2; c INTEGER; BEGIN x(1) := 'Bleu'; x(2) := 'Blanc'; x(4) := 'Rouge'; dbms_output.put_line (x(a)); dbms_output.put_line (x(b)); dbms_output.put_line (x(c)); END; Bleu Blanc DECLARE*ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: NULL index table key valueORA-06512: at line 13

Array Functions & Procedures (METHODS) Use dot notation array_variable_name.COUNT* DELETE is a procedureAll of the others are just functions. They return values but do not change the array COUNT FIRST LAST NEXT PRIOR DELETE * EXISTS

COUNT The number of elements in the array If the array is empty, COUNT returns “0” (zero) In sparsely populated arrays, count is of positions declared onlyThe value in the array position can be null 1 Hydrogen 2 Helium 3 Lithium 6 Carbon 36 Krypton 201 1 Washington 2 Adams 3 Jefferson 4 Madison 5 Monroe 6 Adams The COUNT of this array is 6 , because 6 index positions have been filled, even though one of them is null

COUNT The number of elements in the array DECLARE TYPE varchar_by_number_t IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER; v_element varchar_by_number_t ; BEGIN v_element (2) := 'Helium'; v_element (3) := 'Lithium'; v_element (1) := 'Hydrogen'; v_element (6) := 'Carbon'; dbms_output.put_line ( 'Element count ' || v_element.COUNT ); v_element (200) := NULL; dbms_output.put_line ( 'Element count ' || v_element.COUNT ); END; Element count 4 Element count 5

FIRST / LAST FIRST Returns the value of the first index position (in order) LAST Returns the value of the last index position (in order)Return type will match datatype of indexThey are functionsFor a empty array, FIRST and LAST return null -- No error is raised DECLARE TYPE varchar_by_number_t IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER; v_element varchar_by_number_t ; BEGIN v_element (2) := 'Helium'; v_element (3) := 'Lithium'; v_element (1) := 'Hydrogen'; dbms_output.put_line ( 'First = ' || v_element.FIRST ); dbms_output.put_line ( 'Last = ' || v_element.LAST);END; First = 1 Last = 3

NEXT / PRIOR NEXT : Given one index value, what is the next index value (in order)?PRIOR: Given one index value, what is the prior index value (in order)?Syntax is slightly different than others: array_variable_name .NEXT ( idx );These functions are how we can iterate through a sparse indexAt the margins, returns NULLIt does not raise an errorEven if supplied with an index value which is not in the array

NEXT / PRIOR DECLARE TYPE varchar_by_number_t IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER; v_element varchar_by_number_t;BEGIN v_element (2) := 'Helium'; v_element (3) := 'Lithium'; v_element (1) := 'Hydrogen'; v_element (6) := 'Carbon'; dbms_output.put_line ( 'Next(1): ' || v_element.NEXT (1) ); dbms_output.put_line ( 'Next(2): ' || v_element.NEXT (2) ); dbms_output.put_line ( 'Next(3): ' || v_element.NEXT(3) ); dbms_output.put_line ( 'Next(6): ' || v_element.NEXT (6) ); dbms_output.put_line ( 'Next(5): ' || v_element.NEXT (5) ); END; Next(1): 2 Next(2): 3 Next(3): 6 Next(6): Next(5): 6

DELETE Removes an element from the array Specify index position of the element to be removed Syntax: array_variable_name.DELETE ( idx ) ;Size (COUNT) of array is reduced by one If no element exists with the index supplied, no error is raised There is no return value This is not a function

DELETE DECLARE TYPE varchar_by_number_t IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER; v_golfers varchar_by_number_t;BEGIN v_golfers (1) := 'Ernie Els'; v_golfers (2) := ' Tiger Woods '; v_golfers (3) := 'Tom Watson'; v_golfers (4) := 'Arnold Palmer'; dbms_output .put_line ( ' Before delete, count = ' || v_golfers.COUNT ); dbms_output.put_line ( ' Before delete , second element = ' || v_golfers (2) ); v_golfers.DELETE (2); dbms_output . put_line ( ' After delete , count = ' || v_golfers.COUNT ); dbms_output.put_line ( ' After delete, second element = ' || v_golfers (2) ); END; Before delete, count = 4 Before delete, second element = Tiger Woods After delete, count = 3 DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 13

EXISTS Boolean return Is there an entry in the array with specified index value? Specify index position of the element to be testedSyntax: array_variable_name.EXISTS ( idx ) ;

EXISTS DECLARE TYPE varchar_by_number_t IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER; v_comics varchar_by_number_t;BEGIN v_comics (10) := 'Peanuts'; v_comics (25) := 'Calvin & Hobbes'; v_comics (300) := 'The Far Side'; IF v_comics.EXISTS ( 25 ) THEN dbms_output.put_line ( 'Position 25 is in the array.'); ELSE dbms_output.put_line ( 'Position 25 is NOT in the array.'); END IF; IF v_comics.EXISTS ( 100 ) THEN dbms_output.put_line ( 'Position 100 is in the array.'); ELSE dbms_output.put_line ( 'Position 100 is NOT in the array.'); END IF; END; Position 25 is in the array. Position 100 is NOT in the array.

When Salt Lake City hosted the Winter Olympics in 2002, which of these locations was not a competition venue? Deer Valley The “E” Center SnowbasinSeven Peaks

BASIC OPERATIONS

POPULATING VIA A CURSOR DECLARE TYPE varchar_by_number_t IS TABLE OF scott.emp.ename%TYPE INDEX BY BINARY_INTEGER; v_emps varchar_by_number_t ; BEGIN FOR rec IN ( SELECT ename FROM scott.emp WHERE sal >2000 ) LOOP v_emps ( v_emps.COUNT + 1 ) := rec.ename ; END LOOP; dbms_output.put_line ( v_emps.COUNT ); END; 6 This is NOT the most efficient way to do this But there ARE uses cases for it And it's common SELECT a set of records and stepping through them one at a time

Very common structure Usually have to wrap this in EXCEPTION handler Must return exactly one record BULK COLLECT Doesn't matter how many records are returned Even OK if zero records are returned SELECT field_name INTO var FROM table_name WHERE { returns exactly one rec } SELECT field_name BULK COLLECT INTO array_var FROM table_name ORA-01403: no data found ORA-01422: exact fetch returns more than requested number of rows

BULK COLLECT – BETTER WAY DECLARE TYPE varchar_by_number_t IS TABLE OF scott.emp.ename%TYPE INDEX BY BINARY_INTEGER; v_emps varchar_by_number_t ; BEGIN SELECT ename BULK COLLECT INTO v_emps FROM scott.emp WHERE sal >2000; dbms_output.put_line ( v_emps.COUNT ); END; 6 One query, No looping through records Records are inserted into the array with dense index, 1 though n

ITERATING THROUGH AN ARRAY With a dense array You can use a FOR loop FOR i IN 1..v_emps.COUNT LOOP dbms_output.put_line ( v_emps ( i )); END LOOP; With a sparse array Use a WHILE loop, testing for NULL i := v_emps.FIRST ; WHILE i IS NOT NULL LOOP dbms_output.put_line ( v_emps ( i )); i := v_emps.NEXT ( i ); END LOOP;

Lake Powell, the largest freshwater body of water in Utah, is a man-made lake created by which dam on the Colorado River? Hoover Dam John Wesley Powell Dam Glen Canyon DamFlaming Gorge Dam BONUS QUESTION: In what state is Glen Canyon Dam located?

Use cases

USE CASES Indexing with a meaningful value “Dictionaries”, “Key/value pairs”, “lookup”, “Associative array” CountingIn memory sortingA benefits interface

Sample data EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 12/17/1980 800 20 7499 ALLEN SALESMAN 7698 2/20/1981 1600 300 30 7521 WARD SALESMAN 7698 2/22/1981 1250 500 30 7566 JONES MANAGER 7839 4/2/1981 2975 20 7654 MARTIN SALESMAN 7698 9/28/1981 1250 1400 30 7698 BLAKE MANAGER 7839 5/1/1981 2850 30 7782 CLARK MANAGER 7839 6/9/1981 2450 10 7788 SCOTT ANALYST 7566 12/9/1982 3000 20 7839 KING PRESIDENT 11/17/1981 5000 10 7844 TURNER SALESMAN 7698 9/8/1981 1500 0 30 7876 ADAMS CLERK 7788 1/12/1983 1100 20 7900 JAMES CLERK 7698 12/3/1981 950 30 7902 FORD ANALYST 7566 12/3/1981 3000 20 7934 MILLER CLERK 7782 1/23/1982 1300 10

Indexing with a meaningful value DECLARE TYPE varchar_by_number_t IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER; v_emps varchar_by_number_t; i INTEGER; BEGIN FOR rec IN ( SELECT empno , ename FROM emp ) LOOP v_emps ( rec.empno ) := rec.ename ; END LOOP; dbms_output. put_line ( 'Array size is ' || v_emps.COUNT ); dbms_output . put_line ( ' Lowest index value = ' || v_emps.FIRST ); dbms_output . put_line ( ' Highest index value = ' || v_emps.LAST ); i := v_emps.FIRST ; WHILE i IS NOT NULL LOOP dbms_output. put_line ( i || ' = ' || v_emps(i) ); i := v_emps.NEXT ( i ); END LOOP; END;Using the empno as the index value Each pass through the loop, adds one more entry to the array The entries are created, indexed by the value of empno I have created an associative array -- LOOKUP FOR rec IN ( SELECT empno , ename FROM emp ) LOOP v_emps ( rec.empno ) := rec.ename ; END LOOP;

Meaningful Index The result of the initial FOR loop in the prior slide. An array with 14 elements, but it is indexed by the value from the empno fieldThis has created a lookup (or hash table)These values can be queried once and used over and over v_name := v_emps (7788); Instead of executing another query More efficient code -> Avoids context switchesI use them frequently for:Hospital nameBenefit plan namesLots of similar situations 7369 Smith 7499 Allen 7521 Ward 7566 Jones 7654 Martin 7698 Blake 7782 Clark 7788 Scott 7839 King 7844 Turner 7876 Adams 7900 James 7902 Ford 7934 Miller

Indexing with a meaningful value DECLARE TYPE varchar_by_number_t IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER; v_emps varchar_by_number_t; i INTEGER; BEGIN FOR rec IN ( SELECT empno , ename FROM emp ) LOOP v_emps ( rec.empno ) := rec.ename ; END LOOP; dbms_output. put_line ( 'Array size is ' || v_emps.COUNT ); dbms_output . put_line ( ' Lowest index value = ' || v_emps.FIRST ); dbms_output . put_line ( ' Highest index value = ' || v_emps.LAST ); i := v_emps.FIRST ; WHILE i IS NOT NULL LOOP dbms_output. put_line ( i || ' = ' || v_emps(i) ); i := v_emps.NEXT ( i ); END LOOP; END;Using the empno as the index value i := v_emps.FIRST ; WHILE i IS NOT NULL LOOP dbms_output . put_line ( i || ' = ' || v_emps (i) ); i := v_emps.NEXT ( i ); END LOOP; The loop is controlled by “ i ” As long as there is another index value in the array, i will not be null Once the end of the array is reached, i will be null Oracle will exit the loop i also will be null if the array is empty because v_emps ( i ).FIRST will return null

ITERATING THROUGH A LOOK-up (SPARSE INDEX) DECLARE TYPE varchar_by_number_t IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER; v_emps varchar_by_number_t; i INTEGER; BEGIN FOR rec IN ( SELECT empno , ename FROM emp ) LOOP v_emps ( rec.empno ) := rec.ename ; END LOOP; dbms_output .put_line ( 'Array size is ' || v_emps.COUNT ); dbms_output . put_line ( ' Lowest index value = ' || v_emps.FIRST ); dbms_output . put_line ( ' Highest index value = ' || v_emps.LAST ); i := v_emps.FIRST ; WHILE i IS NOT NULL LOOP dbms_output .put_line ( i || ' = ' || v_emps(i) ); i := v_emps.NEXT ( i ); END LOOP; END;Using the empno as the index value Array size is 14 Lowest index value = 7369 Highest index value = 7934 7369 = SMITH 7499 = ALLEN 7521 = WARD 7566 = JONES 7654 = MARTIN 7698 = BLAKE 7782 = CLARK 7788 = SCOTT 7839 = KING 7844 = TURNER 7876 = ADAMS 7900 = JAMES 7902 = FORD 7934 = MILLER Did you notice that the output records were printed in order? We can employ knowledge this to sort in memory within a PLSQL program

Using Index for in-memory sort DECLARE TYPE varchar_by_varchar_t IS TABLE OF VARCHAR2(25) INDEX BY VARCHAR2(25); v_emps varchar_by_varchar_t; i VARCHAR2(25); BEGIN FOR rec IN ( SELECT ename FROM emp ) LOOP v_emps ( rec.ename ) := NULL; END LOOP; dbms_output . put_line ( 'Array size is ' || v_emps.COUNT ); dbms_output.put_line ( ' Lowest index value = ' || v_emps.FIRST ); dbms_output . put_line ( ' Highest index value = ' || v_emps.LAST ); i := v_emps.FIRST ; WHILE i IS NOT NULL LOOP dbms_output . put_line ( i ); i := v_emps.NEXT ( i ); END LOOP; END;Simply index using the value to be sorted Array size is 14 Lowest index value = ADAMS Highest index value = WARD ADAMS ALLEN BLAKE CLARK FORD JAMES JONES KING MARTIN MILLER SCOTT SMITH TURNER WARD

Gotchas when using index to sort Be alert for duplicates Remember: index values must be unique If they are not unique, this technique still can be used, but it needs modificationInclude count – as we’ll see in next sectionEnsure that the column to be used does not contain any null values

Variables for Counting The requirement is to report counts of employees assigned in each job DECLARE v_analyst_count INTEGER := 0; v_clerk_count INTEGER := 0; v_mgr_count INTEGER := 0; v_president_count INTEGER := 0; v_salesman_count INTEGER := 0; BEGIN FOR rec IN (SELECT * FROM scott.emp ) LOOP IF rec.job = 'MANAGER' THEN v_mgr_count := v_mgr_count +1; ELSIF rec.job = 'CLERK' THEN v_clerk_count := v_clerk_count + 1; . . . END IF; Etc etc .. THERE MUST BE A BETTER WAY… ----------------------------------------- Inefficient: lots of variables to track More than a few jobs, and number of variables is untenable Not scalable – What happens to the code if there’s a new job code? Prone to programmer errors

Counting with arrays DECLARE TYPE number_by_varchar_t IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(25); v_emps number_by_varchar_t; i VARCHAR2(25); BEGIN FOR rec IN ( SELECT job FROM emp ) LOOP IF NOT v_emps.EXISTS ( rec.job ) THEN v_emps ( rec.job ) := 0; END IF; v_emps ( rec.job ) := v_emps ( rec.job ) + 1; END LOOP; i := v_emps.FIRST; WHILE i IS NOT NULL LOOP dbms_output .put_line ( i || ': ' || v_emps (i) ); i := v_emps.NEXT ( i ); END LOOP; END; Counting the number of emps by job with an array instead FOR rec IN ( SELECT job FROM emp ) LOOP IF NOT v_emps.EXISTS ( rec.job ) THEN v_emps ( rec.job ) := 0; END IF; v_emps ( rec.job ) := v_emps ( rec.job ) + 1; END LOOP; The array is indexed by the job name Values in the array: The number of records seen with the job name that is the index Incremented one at a time The IF statement, avoids no_data_found on the first record with each job name ANALYST: 2 CLERK: 4 MANAGER: 3 PRESIDENT: 1 SALESMAN: 4

Other Uses Once you get comfortable with the workings of PLSQL arrays, you’ll find lots of uses! Flexible Cursor Does this exist?Have we seen this value previously?Set MINUS logicCreating functions that can return arraysAnd procedures that can accept arrays as parametersMultidimensional arrays – nested arrays

Which of these people does NOT have a street named after him in downtown Salt Lake City? Brigham Young Karl Malone Martin Luther KingCésar Chávez

HEALTH PLAN ENROLLMENT CSV Receive positive enrollment info from employer group Are there terminations? We must compare current enrollment from database vs enrollment from CSV fileIdentify ADD and DROP coverage For ADD: Loop through rows in CSV, Test each to see if employee has coverage For DELETE: ? CSV Coverage DB Coverage check Add (API) Current Enrollment PLSQL Enrollment List From Vendor

HEALTH PLAN ENROLLMENT CSV – DROP COVERAGE Current Enrollment emp (1001) := ''; emp (1964) := ''; emp (2357) := ''; emp (32748):= ''; emp (93726):= ''; Coverage DB Create array CSV emp (2357).DELETE; emp (93726).DELETE; One by one, each employee in the CSV file with coverage is removed from the array emp (1001) emp (1964) emp (32748) Coverage DB After processing CSV, employees remaining in the array can have coverage terminated Remaining array of Employees becomes list to terminate coverage

IDENTIFYING ADDS WITHOUT CONTEXT SWITCH Current Enrollment emp (1001) := ''; emp (1964) := ''; emp (2357) := ''; emp (32748):= ''; emp (93726):= ''; Coverage DB Create array CSV Coverage DB When checking for existing coverage, just check PLSQL array, instead of going to DB for each record Coverage check emp (1001) := ''; emp (1964) := ''; emp (2357) := ''; emp (32748):= ''; emp (93726):= ''; Add Coverage (API call)

Use an array: Query once – use often Query each time Embed in PLSQL (hard-code) USE CASE: LOOK-UPS (TRANSLATIONS) Employer Group Plans HMO Plan – Single HMO Plan – Family High Deductible Plan – Single High Deductible Plan – Family Plan IDs (for calling API) 1234 HMO Plan – Single 1235 HMO Plan – Family 5611 High Deductible Plan – Single 5612 High Deductible Plan – Family IF plan_name = 'HMO plan Single' THEN v_plan_id := 1234; ELSIF plan_name = 'HMO plan Family' . . . etc CSV Coverage DB Coverage check SELECT plan_id INTO v_plan_in FROM health_plans WHERE plan_name = :1; x x v_plans ('High Deductible Plan - Single') := 1234; v_plans ('HMO Plan - Single') := 5611; v_plans ('High Deductible Plan - Family') := 1235; v_plans ('HMO Plan - Family') := 5612; Vendor sends plan info using their identifiers. - - - - - - - - - - - We need the IDs for API and coverage query

ARRAY LOOKUP v_plans ('High Deductible Plan - Single') := 1234; v_plans ('HMO Plan - Single') := 5611; v_plans ('High Deductible Plan - Family') := 1235; v_plans ('HMO Plan - Family') := 5612; Indexed by the plan name NOT by the plan_id . This is because the plan name is what is in the file, so that should be index. You can (and should) populate array with query CSV Coverage DB Coverage check Look up plan_id in array Call API

SPEED OF BULK COLLECT VS CURSOR LOOP DECLARE TYPE num_by_int_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; v_emp_list num_by_int_t ; v_emp_index num_by_int_t ; BEGIN SELECT empno BULK COLLECT INTO v_emp_list FROM scott.emp ; FOR i IN 1..v_emp_list.COUNT LOOP v_emp_index ( v_emp_list ( i )) := 0; END LOOP; END; DECLARE TYPE num_by_int_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; v_emp_index num_by_int_t ; BEGIN FOR rec IN ( SELECT empno FROM scott.emp ) LOOP v_emp_index ( rec.empno ):= 0; END LOOP; END;

SPEED OF BULK COLLECT VS CURSOR LOOP > 2018-02-28 17:02:01.813960 -- 2018-02-28 17:02:27.376853 == +000000000 00:00:25.562893000 > 2018-02-28 17:02:35.629705 -- 2018-02-28 17:03:07.992050 == +000000000 00:00:32.362345000 > 2018-02-28 17:08:09.056676 -- 2018-02-28 17:08:35.008987 == +000000000 00:00:25.952311000 > 2018-02-28 17:08:42.721240 -- 2018-02-28 17:09:16.943773 == +000000000 00:00:34.222533000 > 2018-02-28 17:02:27.376874 -- 2018-02-28 17:02:31.705211 == +000000000 00:00:04.328337000 > 2018-02-28 17:02:31.705238 -- 2018-02-28 17:02:35.626838 == +000000000 00:00:03.921600000 > 2018-02-28 17:08:35.009024 -- 2018-02-28 17:08:39.043161 == +000000000 00:00:04.034137000 > 2018-02-28 17:08:39.043188 -- 2018-02-28 17:08:42.721076 == +000000000 00:00:03.677888000 7,6 million records Cursor Loop BULK COLLECT then Loop

THANK-you DAN STOBER