StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODISTINCT count on a ref_cursor in Oracle PL/SQL
text
Body
copied!<p>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</p> <p>(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</p> <p>array position 1 call returns 'Mike' and 'Dave' array position 2 call returns 'Male' and 'Male'</p> <p>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</p> <p>so array position 1 call would return 2 array position 2 call would return 1</p> <p>I'm quite new to PL/SQL so please excuse the dumb question! </p> <p>Splitting the source table into individual columns is not an option unfortunately :-(</p> <p>Many thanks</p> <p>Mike</p> <p>IN response to @zero323 (apologies for missing this out)</p> <p>Sample code so far is...</p> <pre><code>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; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload