Need distinct values from PL/SQL Table

Does anyone have a quick little routine to populate one PL/SQL table from another. The trick is that I only want the distinct values copied.

Thanks in advance,

- Chris

I don't know of any straightforward method, but this comes to my mind:

1.) The source PL/SQL table has one column only of type INTEGER (or INITIGER compatible type):
You could use the column itself as the index of the destination index-by table (meaning that column value will be the same as its corresponding index value). As a result each distinct value will have only one entry in the destination index-by table.

2.) The source PL/SQL table has one column of a type that is not INTEGER:
Use a good hash function to calculate a hashed integer number from the column value and use this hashed value as a index in the destination table.

3.) The source PL/SQL table is a table of records (meaning more than one field in the records, and uniquenes is defined accros all fields):
In this case (and if number of records is not trivially small) I would populate a normal database table from the source PL/SQL table and load distinct records into destination PL/SQL table from there.

HTH,

Option 1 would be the applicable one.

Here's the scenario: I have some before row triggers that populate an index-by table with IDs from an FK column for all the rows that were modified. Multiple rows can be affected for each FK value, so it can show up multiple times inthe index-by table.

Now, I use this index-by table in 1 of my after-statement triggers as-is. In another, I want to act only on the distinct set of keys as it would be much more efficient.

So, using the values from the one table as indexes in the other sound intriguing, but how do I then roll through the values in the second table? I don't want to roll through tens of thousands of entries to find the ones that are populated - that would be too slow and definitely not scalable.

So how would this work?

- Chris

Index-by tables are typicaly sparce, so you are right it isn't very efficient to scan each and every possible value in the index to find existing rows. But index-by tables now offer quite some useful "methods" (aka functions) like FIRST, LAST, NEXT etc, that you can use to efficiently scan the table:

DECLARE
--indx PLS_INTEGER := pl_table.FIRST;
BEGIN
--LOOP
----EXIT WHEN indx IS NULL;
----DBMS_OUTPOT.PUT_LINE(pl_table(idx).value);
----idx := pl_table.NEXT;
--END LOOP;
END;

So if you have three records in a table with index values 0, 100 and 200, the above block will "touch" only those three indexed rows in three iterations, instead of doing 201 iterations to check each possible value between 0 and 200.

HTH,

Hi Jmodic , 24th May 2001 15:09 hrs chennai

Good Solution.

Cheers

Padmam

Very cool!

Here is the final code:

DECLARE
___-- ---------------
___-- Local Variables
___-- ---------------
___l_Employee_ID1______EmployeeSalaryHistory_P.Employee_ID_TYPE___;
___l_Employee_ID2______EmployeeSalaryHistory_P.Employee_ID_TYPE___;
___l_idx1____________PLS_INTEGER;
___l_idx2____________PLS_INTEGER;

BEGIN

______-- First, get the unique entries
___FOR x IN 1..EmployeeSalaryHistory_P.g_Old_Employee_ID.COUNT
___LOOP
______l_Employee_ID1(EmployeeSalaryHistory_P.g_Old_Employee_ID(x)) := EmployeeSalaryHistory_P.g_Old_Employee_ID(x);
___END LOOP;

___l_idx1___:=___l_Employee_ID1.FIRST;
___l_idx2___:=___0;
___WHILE l_idx1 IS NOT NULL
___LOOP
______l_idx2__________________:=___l_idx2 + 1;
______l_Employee_ID2(l_idx2)___:=___l_Employee_ID1(l_idx1);
______l_idx1__________________:=___l_Employee_ID1.NEXT(l_idx1);
___END LOOP;

______-- Loop through each deleted entry and handle the changes
___FORALL x IN l_Employee_ID2.FIRST..l_Employee_ID2.LAST

After putting the distinct values into l_Employee_ID1, I needed to re-create a contiguous array with l_Employee_ID2 so the FORALL would work - doesn't work on sparse tables.

I think I'm going to make a generic proc for this - it is very cool (I'm such a geek :D)

Thanks a ton!

- Chris