11 thoughts on “predefined collections”

  1. 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.

  2. 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.

  3. 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

  4. 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
                                                                                    
    

  5. 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 ;-)

  6. 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;
    /
    

  7. 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

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>