predefined collections

13 thoughts on “predefined collections”

1. Paweł Barut December 5, 2007 at 19:06

   Nice trick.
2. Gary December 6, 2007 at 00:25

   I’ve used the following, which is also available in 9iR2:
   select * from table(sys.dbms_debug_vc2coll(‘A’,’B’,’C’));
   This one is a TABLE OF VARCHAR2(1000), where ODCIVarchar2List is a VARRAY(32767) OF VARCHAR2(4000), so yours does give more space.
3. DJ December 6, 2007 at 01:41

   Come on, it takes a single SQL to create your own defined types. Why rely on system types that may change in the next release? One less thing to check for upgrade.
4. Laurent Schneider Post authorDecember 6, 2007 at 11:03

   DJ,
   Why should a SQL statement depend on a type, when there is a documented datatype which is not supposed to change in next release (because it is documented). Oracle cannot remove features in a next release without notice. I would say it is OK to rely on them because *they are documented*

   Those extensibility types also exist for date, numbers and raw.

   Gary,
   How could you say my one use more space? 32767 is a limit. Also varchar2(1000) is a meaningless constraint on varchar2, 4000 is the maximum. Could you show the difference in memory usage?

   But true, those extensibility types are documented only in 10gR2 and 11gR1. So in 9iR2, dbms_debug_vc2coll sounds possible
5. Laurent Schneider Post authorDecember 6, 2007 at 12:55

   I made a test Garry and it seems you are right about memory usage.

   
select * from table(sys.ODCIVarchar2List('AAA','BBB','CCC'))

   COLUMN_VALUE
   ------------
   AAA
   BBB
   CCC

   select * from table(sys.dbms_debug_vc2coll('AAA','BBB','CCC'))

   COLUMN_VALUE
   ------------
   AAA
   BBB
   CCC

   select sql_text,sharable_mem,persistent_mem,runtime_mem
   from v$sqlarea
   where sql_text like '%AAA%'
   and sql_text not like '%sharable%'

   SQL_TEXT
   --------
   SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM
   ------------ -------------- -----------
   select * from table(sys.ODCIVarchar2List('AAA','BBB','CCC'))
   13582 13752 12864

   select * from table(sys.dbms_debug_vc2coll('AAA','BBB','CCC'))
   13584 4752 3864
6. Chris December 7, 2007 at 11:09

   What imho really sucks is that oracle is not capable of producing a set of reusable components for pl/sql development. E.g., there should be a goddamn standard table of varchar2(4000) that everybody uses! Well, they’d better get a better name 😉
7. Laurent Schneider Post authorDecember 7, 2007 at 18:36

   Some ODCI varrays existed in 9i, like ODCINUMBERLIST. However, I found them documented only in 10gR2
8. Gazete July 18, 2008 at 20:03

   Nice trick , thanks…
9. Bruno Fabietti February 7, 2014 at 09:39

   Hello Laurent,
   Seems like a very useful function. I tried it in a paramtrized setting, but it does not seem to work. Could you please point me in the right direction ?
   My example :
   
select * from bf_prod_exmpl;
parameter_name parameter_value
-------------- ---------------
Product AAA,BBB+

   select * from bf_products;
   row_id id_number prod_type
   ------ --------- ---------
   1 NUM001 AAA
   2 NUM002 AAA
   3 NUM002 BBB
   4 NUM003 AAA
   5 NUM004 AAA
   6 NUM004 BBB+

   select * from table(sys.ODCIVarchar2List('AAA','BBB','CCC'));
column_value
------------
AAA
BBB
CCC
-- = = = = = = = = = = = = = = = = = = = = = = = = = = = =
set serveroutput on
declare
lv_list varchar2(200);
begin
select regexp_replace( (regexp_replace(pe.parameter_value, ',', ''',''') ), '^|$', '''')
into lv_list
from bf_prod_exmpl pe
where pe.parameter_name = 'Product'
;
dbms_output.put_line (
'lv_list >'||
lv_list||
'''AAA'',''BBB+''<''AAA'',''BBB'','''||lv_list||'''AAA'',''BBB+''>'
);
end;
/

10. Laurent Schneider Post authorFebruary 7, 2014 at 14:48

    please use <code> and </code>

    Okay, you wrote something like

    declare
    lv_list varchar2(200);
    begin
    select regexp_replace( (regexp_replace(pe.parameter_value, ',', ''',''') ), '^|$', '''')
    into lv_list
    from bf_prod_exmpl pe
    where pe.parameter_name = 'Product';
    dbms_output.put_line ( 'lv_list = <'||lv_list||'>');
    end;
    /

    lv_list = <'AAA','BBB+'>


    But you are looking for something like
    
declare
lv_list varchar2(200);
lv_odcilist sys.ODCIVarchar2List;
c number;
begin
select regexp_replace( (regexp_replace(pe.parameter_value, ',', ''',''') ), '^|$', '''')
into lv_list
from bf_prod_exmpl pe
where pe.parameter_name = 'Product';
execute immediate 'begin :lv_odcilist := sys.ODCIVarchar2List('||lv_list||'); end;' using out lv_odcilist;
c := lv_odcilist.count;
dbms_output.put_line ( 'lv_odcilist.count = '||c);
for i in 1 .. c loop
dbms_output.put_line ( 'lv_odcilist('||i||') = '||lv_odcilist(i));
end loop;
end;
/
lv_odcilist.count = 2
lv_odcilist(1) = AAA
lv_odcilist(2) = BBB+


    You need to translate your “string” in an expression list, either with execute immediate or with a plsql loop and substrings
11. Bruno Fabietti February 7, 2014 at 16:14

    Thanks Laurent for the prompt reply.
    This is a very useful solution.
12. Durga May 22, 2018 at 11:02

    Hi Laurent Schneider,

    I am trying to do the following:
    In my code the IN statement will be dynamically created and it may have more than 1050 values in it.
    We want to handle these values without getting error -:
    ora-01795 maximum number of expressions in a list is 1000

    To handle this error, this is what i am trying to do:
    example:
    select * from employees
    where emp_id in (select * from table(sys.odcinumberlist(1,2,3,4 ….1020 values)));

    However, i get error
    ORA-00939: too many arguments for function

    Can you please suggest how should i proceed?

    Thanks,
    Durga
13. Laurent Schneider Post authorMay 22, 2018 at 21:19

    A common solution is multiple OR

    where id in (1,2,3,….1000)
OR id in (1001,1002…2000)


    But I prefer the temporary table approach…

    where Id in (select * from temp_table)

Comments are closed.