-- /packages/plsql-util/sql/plsql-list-pb.sql
--
-- $Id: plsql-list-pb.sql,v 1.1.3.1 2001/08/29 05:31:19 andy Exp $






create or replace package body pl_list as


  function in_list_p
    -- Returns 't' if var is in the list, 'f' if not.  Or, Set in_p to 'f'
    -- in order to get a "not in" test.
    --
    -- Warning: We are not overriding Oracle's default behavior with empty
    -- string and null, so this does NOT work for values of '' and null!!
  (
    var        pl_list.string
    ,list_csv  pl_list.string_csv
    ,in_p      char  default 't'
  )
  return char
  is
    result    char;
    not_text  varchar(20);
  begin
    if in_p = 'f' then
      not_text := ''' not''';
    else 
      not_text := null;
    end if;

    execute immediate
      'select ''t'' from dual ' ||
      'where ' || '''' || var || '''' || not_text ||
         ' in ('  || list_csv || ')'
      into result
    ;
  
    --dbms_output.put_line('result:  ' || result);
    return result;
  
    exception
      when NO_DATA_FOUND then
        --dbms_output.put_line('result:  ' || 'f');
        return 'f';
  end in_list_p;


  -- Note that using a construction like this:
  --
  --   FOR i IN v_list.FIRST + 1 .. v_list.LAST LOOP
  --     var_list_csv := var_list_csv || ', ' || v_list(i);
  --   END LOOP;
  --
  -- is NOT a good idea, as it ASSUMES that none of the collection
  -- items between the first and last have been deleted.  E.g., just
  -- because v_list.first = 1 and v_list.last = 10 does NOT mean that
  -- v_list(5) still exists!  --atp@arsdigita.com, 2000/12/04
  --
  -- Better to use a while loop as done below, like this:
  --
  --   while i is not null loop
  --     var_list_csv := var_list_csv || ', ' || v_list(i);
  --     i := v_list.NEXT(i);
  --   end loop;


  -- Converts a pl/sql table to a comma-delimited string. (Should have
  -- named it var_list_to_csv.):
  --
  -- The integer version of var_list_csv came first.  The number version
  -- should be identical except for the incoming variable type.  The
  -- char version is similar, but must also do proper quoting with '.

  
  function var_list_csv (
    v_list  pl_list.varchar_list
  )
  return pl_list.string_csv
  as
    var_list_csv  pl_list.string_csv := '';
    i             binary_integer := 0;
  begin
    if v_list.COUNT > 0 then
      i  := v_list.FIRST;
      var_list_csv := var_list_csv || '''' || v_list(i) || '''';
      i := v_list.NEXT(i);

      while i is not null loop
        var_list_csv := var_list_csv || ', ' || '''' || v_list(i) || '''';
        i := v_list.NEXT(i);
      end loop;
    end if;
   
    return var_list_csv;
  end;


  function var_list_csv (
    v_list  pl_list.integer_list
  )
  return pl_list.string_csv
  as
    var_list_csv  pl_list.string_csv := '';
    i             binary_integer := 0;
  begin
    -- Since the code for the integer_list and number_list versions of
    -- var_list_csv is identical, it sure would be nice to simply cast the
    -- integer_list to a number_list and then call var_list_csv
    -- (pl_list.number_list).  Unfortunately, I don't know how to do
    -- that.  --atp@arsdigita.com, 2000/12/05

    if v_list.COUNT > 0 then
      i  := v_list.FIRST;
      var_list_csv := var_list_csv || v_list(i);
      i := v_list.NEXT(i);
    
      while i is not null loop
        var_list_csv := var_list_csv || ', ' || v_list(i);
        i := v_list.NEXT(i);
      end loop;
    end if;

    return var_list_csv;
  end;


  function var_list_csv (
    v_list  pl_list.number_list
  )
  return pl_list.string_csv
  as
    var_list_csv  pl_list.string_csv := '';
    i             binary_integer := 0;
  begin
    if v_list.COUNT > 0 then
      i  := v_list.FIRST;
      var_list_csv := var_list_csv || v_list(i);
      i := v_list.NEXT(i);

      while i is not null loop
        var_list_csv := var_list_csv || ', ' || v_list(i);
        i := v_list.NEXT(i);
      end loop;
    end if;
   
    return var_list_csv;
  end;


  -- This is not used anywhere.  Does it work?  --atp@arsdigita.com, 2000/12/04
  --function csv_to_list (
  --  csv_string  pl_list.string_csv
  --)
  --return pl_list.varchar_list
  --as 
  --  return_list         pl_list.varchar_list;
  --  comma_index         integer := 1;
  --  old_comma_index     integer := 1;
  --  table_index         integer := 1;
  --begin
  --  loop
  --    comma_index := instr(csv_string, ',', comma_index, 1);
  --    exit when comma_index = 0;
  -- 
  --    return_list(table_index) := substr(csv_string, old_comma_index, (comma_index - old_comma_index));
  --
  --    old_comma_index := comma_index;
  --    table_index := table_index + 1;                   
  --  end loop;
  --  return return_list;
  --end;  


  --function integer_list_csv
  --  -- Legacy wrapper which calls var_list_csv.
  --(
  --  v_integer_list pl_list.integer_list
  --)
  --return varchar
  --is
  --begin
  --  return pl_list.var_list_csv(v_integer_list);
  --end;


  function plsql_to_tcl_list
    -- Convert a varchar_list (which is a nested table) to a Tcl list.
    --
    -- TODO:  Finish the allow_sublists_p = 'f' option - right now it does nothing.
  (
     v_list            pl_list.varchar_list
    ,allow_sublists_p  char default 't'
  )
  return pl_list.string_csv
  as
    tcl_list  pl_list.string_csv;
    space     varchar(10) := ' ';  -- Delimiter between Tcl list items, generally a space.
    i         binary_integer := 0;
    -- Regexp character classes:
    -- The Oracle docs SAY that \s matches whitespace but it is NOT TRUE.
    wrap_pattern    varchar(100) := '[ \t\n\[\]]';
      -- Dev and Staging do **NOT** handle the character class '[\[]' the
      -- same way!  muniversaldev works correctly - owa_pattern.match
      -- returns TRUE for the match - but on muniversalstaging, it returns
      -- FALSE!  This is totally screwy, but I've no idea what's causing it.
      -- Therefore, split the pattern up into two separate pieces.
      -- --atp@arsdigita.com, 2000/12/19
    wrap_pattern_1  varchar(100) := '[ \t\n\[]';
    wrap_pattern_2  varchar(100) := ']';
    escape_pattern  varchar(100) := '[{}]';
  begin
    -- Note that this may not give EXACTLY the same string representation
    -- of a list as Tcl does, but it should be good enough.
    --
    -- If the varchar_list is atomically null, we return the empty string.
    -- Else if the varchar_list exists but is empty, we return an empty list.
    -- Else, there is stuff on the list, and we convert it to a Tcl list.
    --
    -- If the wrap_pattern characters are present in a list item, then we
    -- surround the list item with squiggly brackets - {}.  If
    -- allow_sublists_p is false, then we are NOT allowing sublists, so if
    -- the list item contains '{' or '}' then we escape them with '\'.  If
    -- allow_sublists_p = 't', then we assume that any squiggly brackets
    -- indicate list items and leave them alone.
  
    if v_list is null then
      tcl_list := '';
    elsif not (v_list.COUNT > 0) then
      tcl_list := '{}';
    else
      i := v_list.FIRST;
  
      if allow_sublists_p = 'f' and
         owa_pattern.match(v_list(i), escape_pattern) = TRUE
      then
        -- TODO:  Finish this part.
        null;
      end if;
  
      --if owa_pattern.match(v_list(i), wrap_pattern) = TRUE
      if owa_pattern.match(v_list(i), wrap_pattern_1) = TRUE or
         owa_pattern.match(v_list(i), wrap_pattern_2) = TRUE
      then
        tcl_list := tcl_list || '{' || v_list(i) || '}';
      else 
        tcl_list := tcl_list || v_list(i);
      end if;
  
      i := v_list.NEXT(i);
      while i is not null loop
        if allow_sublists_p = 'f' and
           owa_pattern.match(v_list(i), escape_pattern) = TRUE
        then
          -- TODO:  Finish this part.
          null;
        end if;
  
        --if owa_pattern.match(v_list(i), wrap_pattern) = TRUE then
        if owa_pattern.match(v_list(i), wrap_pattern_1) = TRUE or
           owa_pattern.match(v_list(i), wrap_pattern_2) = TRUE
        then
          --ad_logging.log(ad_logging.NOTICE, i || ':  true');
          tcl_list := tcl_list || space || '{' || v_list(i) || '}';
        else
          --ad_logging.log(ad_logging.NOTICE, i || ':  false');
          tcl_list := tcl_list || space || v_list(i);
        end if;
  
        i := v_list.NEXT(i);
      end loop;
    end if;
  
    return tcl_list;
  end plsql_to_tcl_list;


end pl_list;
/
show errors