Oracle Database Discussions

----------Posted this in PL/SQL forums, no one replied, so post here to see if some one has the similar cases.


I have a requirement for pass in a varchar2 which is up to 32K as a comma seperated list like "12,abc,33,...'

The return of the ref cursor will base on this list, for example:

select col_a, col_b from tabA where tabA.id in ( the_comma_list) order by col_c ...

If the comma list is less than 4K, I can simple do it like:
SELECT DISTINCT TRIM(SUBSTR(TXT, INSTR(TXT, ',', 1, LEVEL) + 1, INSTR(TXT, ',', 1, LEVEL + 1)
- INSTR(TXT, ',', 1, LEVEL) - 1))
FROM (SELECT ',' || :in_string || ',' TXT FROM DUAL) CONNECT BY LEVEL <=
LENGTH('12') - LENGTH(REPLACE(':in_string , ',', '')) + 1


Now if, the string is more than 4K and up to 32K, I have tried 2 approaches for this:
1.parsing the list into a nested table ( array), then:
select … from tabA, table(nested table) …


For this solution, 2 drawbacks:
--use lots of PGA
--Oracle don’t know the cadinalities of the nested table

2.parsing the list and insert into a global temp table, then:
select .. from tabA, tmp_table …


For this solution, 2 drawbacks:
--Use lots of redo and latches ( we need to insert it to this table first)
--It is slower than using nested table solution
Anyone has the similar requests before? what is your solution and why do you choose it?