DISTINCT count on a ref_cursor in Oracle PL/SQL

Question

I have some code where I create a ref cursor within Oracle PL/SQL to be able to loop through the resulting rows. The table has one large pipe delimited text string column which I then loop through to get the x position array value

(eg: if the lines are all like 'Mike|Male|100|Yes|UK' and 'Dave|Male|200|No|UK' then I have a formula that allows the array position to be passed to the function to return an array of the resulting values

array position 1 call returns 'Mike' and 'Dave' array position 2 call returns 'Male' and 'Male'

What is the best way to approach it if I want to be able to have a function that returns the distinct values of an array position

so array position 1 call would return 2 array position 2 call would return 1

I'm quite new to PL/SQL so please excuse the dumb question!

Splitting the source table into individual columns is not an option unfortunately :-(

Many thanks

Mike

IN response to @zero323 (apologies for missing this out)

Sample code so far is...

FUNCTION DistinctInterfacelineValues(
    inArrayPos     NUMBER,
    inSessionIdMIN NUMBER,
    inSessionIdMAX NUMBER)
  RETURN NUMBER
AS
  int_cursor interfaceline_refcursor_type;
  runningTotal NUMBER:=0;
  tempValue    NUMBER:=0;
  int_record inttestintrecs%ROWTYPE;
  lineLength INTEGER:=1;
  distinctCount NUMBER:=0;

  TYPE table_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; 
  tempTable  table_type;
  tempTablePos INTEGER:=1;

BEGIN

 OPEN int_cursor FOR SELECT * FROM inttestintrecs WHERE (sessionid>=inSessionIdMIN AND sessionid <=inSessionIdMAX);
  LOOP
    FETCH int_cursor INTO int_record;
    EXIT
  WHEN int_cursor%NOTFOUND;
    lineLength := LENGTH(int_record.interfaceline) - LENGTH(REPLACE(int_record.interfaceline, '|', '')) + 1;
    FOR i                                         IN 1 .. lineLength
    LOOP
      IF i            =inArrayPos THEN
***VALUE HERE IS 'Mike' or 'Dave' DEPENDING ON LOOP POSITION***
***I WANT TO KNOW COUNT THE DISTINCT VALUES HERE***
      END IF;
    END LOOP;
  END LOOP;
  CLOSE int_cursor;
  RETURN runningTotal;



END DistinctInterfacelineValues;

Answer 1

You don't really need PL/SQL for this; you can use regexp_substr:

select count(distinct value)
from (
  select regexp_substr(interfaceline, '[^|]+', 1, :arrayPos) as value
  from inttestintrecs
  where sessionid between :loSessId and :hiSessId
);

... where the bind variables are the values you were passing to the function. If you really want a function for that you can just wrap the query (updated with tbone's replace trick to deal with null values):

create or replace function DistinctInterfacelineValues(
    inArrayPos     NUMBER,
    inSessionIdMIN NUMBER,
    inSessionIdMAX NUMBER)
  RETURN NUMBER
AS
  distinctCount NUMBER;
BEGIN
  select count(distinct value)
  into distinctCount
  from (
    select trim(regexp_substr(replace(interfaceline, '|', ' |'),
      '[^|]+', 1, inArrayPos)) as value
    from inttestintrecs
    where sessionid between inSessionIdMIN and inSessionIdMAX
  );
  RETURN distinctCount;
END;
/

SQL Fiddle demo.

---

Within the nested loop version you posted, you could maintain a PL/SQL table indexed by the value rather than a number:

...
  TYPE table_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100); 
  tempTable  table_type;
  tempValue VARCHAR2(100);
...
      IF i = inArrayPos THEN
        tempValue := regexp_substr(int_record.interfaceline, '[^|]+', 1, i);
        tempTable(tempValue) := 1;
      END IF;
...
  RETURN tempTable.count;
...

Another Fiddle just for fun, but there's really no point having all the PL/SQL loop overhead, particularly with the regexp_substr in the middle anyway. You could increment the count held in tempTable for each value so you knew how many of each you'd seen, and you could get the actual values from tempTable too... but you can get those from the pure SQL version just as easily (selecting value, count(*) and distinct value).