Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Filling an array with a list of values

    I can get the following to work just fine

    Code:
    create or replace procedure show_list
    is
      type l_list_of_divisions is table of varchar2(10);
      l_div_list l_list_of_divisions;
    begin
      l_div_list := l_list_of_divisions('Chuck','James','Larry');
      for i in l_div_list.first..l_div_list.last
      loop
        dbms_output.put_line(l_div_list(i));
      end loop;
    end;

    Is it possible to set this up so that the comma-delimited list can be passed in as a parm?

    Code:
    create or replace procedure show_list (p_arg1 varchar2)
    is
      type l_list_of_divisions is table of varchar2(10);
      l_div_list l_list_of_divisions;
    begin
      l_div_list := l_list_of_divisions(p_arg1);
      for i in l_div_list.first..l_div_list.last
      loop
        dbms_output.put_line(l_div_list(i));
      end loop;
    end;
    
    
    Begin
      show_list('Chuck','James','Larry');
    end;
    (This second code sample does not work, but I wanted to provide the pseudo-code)

    --=cf

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Firstly, calling of the procedure is not valid, as you are passing it three strings.
    So the first step would be making it one string either
    Code:
    '''Chuck'',''James'',''Larry'''
    or
    Code:
    'Chuck,James,Larry'
    (simpler, but only valid when given strings do not contain delimiter character).

    Second step is using one of the techniques described e.g. in this Tom Kyte's blog post: http://tkyte.blogspot.com/2006/06/varying-in-lists.html (depends on your Oracle version and/or preference).

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    I tried something similar, but PL/SQL doesn't seem to recognize them as separate elements

    Code:
    create or replace procedure show_list (arg_list varchar2)
    is
      type l_list_of_divisions is table of varchar2(100);
      l_div_list l_list_of_divisions;
      l_arg_list varchar2(100);
    begin
      l_arg_list := ''''||replace(arg_list,',',''',''')||'''';
      l_div_list := l_list_of_divisions(l_arg_list);
      for i in 1 .. l_div_list.count
      loop
        dbms_output.put_line(l_div_list(i));
      end loop;
      
    end show_list;
    then

    Code:
    begin
      show_list('CHUCK,SAM');
    end;
    /
    
    'CHUCK','SAM'

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by chuck_forbes
    I tried something similar, but PL/SQL doesn't seem to recognize them as separate elements
    Yes, it was just the first step for Oracle not to complain of wrong number of passed parameters.

    Second step is described in the link I posted below. It deals with putting string containing list of values into IN clause. Here, you want just to process it as a list. The technique (string to list) will be the same.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Turns out that one of our developers already created a function which pipelines the data back, so that you can do something like the following, via SQL:

    Code:
    SELECT string_split AS parsed_string
    FROM TABLE( PUB.STR_SPLIT( 'Chuck,is,trying,to,reinvent,the,wheel',','));
    
    
    PARSED_STRING                                                                   
    ----------------
    Chuck                                                                           
    is                                                                              
    trying                                                                          
    to                                                                              
    reinvent                                                                        
    the                                                                             
    wheel                                                                           
    
    7 rows selected.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Also, since Application Express now comes built in to Oracle, you can use apex_util.string_to_table:
    Code:
    SQL> declare
      2    v_array apex_application_global.vc_arr2;
      3    v_string varchar2(2000);
      4  begin
      5  
      6    -- Convert delimited string to array
      7    v_array := apex_util.string_to_table('alpha,beta,gamma,delta', ',');
      8    for i in 1..v_array.count
      9    loop
     10      dbms_output.put_line(v_array(i));
     11    end loop;
     12  
     13    -- Convert array to delimited string
     14    v_string := apex_util.table_to_string(v_array,'|');
     15    dbms_output.put_line(v_string);
     16  end;
     17  /
    alpha
    beta
    gamma
    delta
    alpha|beta|gamma|delta
    
    PL/SQL procedure successfully completed.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    Very cool! Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •