--
-- /packages/plsql-util/sql/plsql-list-ph.sql
--
-- Definitions for PL/SQL nested tables of different types, which may be
-- used as the parameter type for PL/SQL procedures and functions that
-- take in a variable number of ID arguments.  Since we tend to use these
-- tables as arrays or flat (non-nested) "lists", we call the package
-- pl_list.
--
-- @creation-date 1999-09-05
-- @cvs-id $Id: plsql-list-ph.sql,v 1.1.3.1 2001/08/29 05:31:19 andy Exp $
--
-- @author Andrew Piskorski (atp@arsdigita.com)
-- @author Michael Yoon (michael@arsdigita.com)
--
-- Note that the function plsql_to_tcl_list REQUIRES the Oracle
-- owa_pattern regular expression package which comes with Oracle WebDB.
-- (If you have it, you will probably find the owa_pattern package in the
-- file: "$ORACLE_HOME/webdb30/admin/plsql/owa/owaload.sql".)
-- 
-- If you don't have the owa_pattern package installed, simply comment
-- out the function plsql_to_tcl_list in both the package header and
-- body, before loading this pl/sql package.
--
-- For examples of using this package, see the sample code snippets at
-- the end of this file.
--


create or replace package pl_list
is

  subtype string     is varchar(700);
  subtype string_csv is varchar(1000);

  -- These declare nested tables:
  type varchar_list  is table of string_csv;
  type integer_list  is table of integer   ;
  type number_list   is table of number    ;

  -- We should probaly have used index-by (aka pl/sql) tables to define
  -- the *_list types, NOT nested tables.  The only advantage of nested
  -- tables is that they come with their own constructors, which we use
  -- all over the place in code like this:
  --
  --   v_ripcord_id_list := pl_list.integer_list(v_ripcord_id);
  --
  -- So if we were to change the implementation to use index-by tables,
  -- we would minimally have to provide these constructor functions.
  -- However, we would ALSO have to go through and make sure ALL the
  -- code that manipulates our list types will still work.  And, I don't
  -- know of any reason why implementing them as nested tables would
  -- really HURT anything, so we might as well just leave them that way.
  --
  -- --atp@arsdigita.com, 2000/12/04


  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.
  (
    var        pl_list.string
    ,list_csv  pl_list.string_csv
    ,in_p      char  default 't'
  ) return char;

  
  -- Converts a pl/sql table to a comma-delimited string. (Should have
  -- named it var_list_to_csv.):

  function var_list_csv (
    v_list  pl_list.varchar_list
  )
  return pl_list.string_csv ;

  function var_list_csv (
    v_list  pl_list.integer_list
  )
  return pl_list.string_csv ;

  function var_list_csv (
    v_list  pl_list.number_list
  )
  return pl_list.string_csv ;

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


  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 ;


end pl_list;
/
show errors


--create or replace function integer_list_csv
--  -- Legacy wrapper which calls var_list_csv, as function was moved into
--  -- package 2000/08/15.
--(
-- v_integer_list pl_list.integer_list
--)
--return varchar
--is
--begin
--  return pl_list.var_list_csv(v_integer_list);
--end;
--/
--show errors


-- ****************************************************************
--   Usage Examples - code snippets
-- ****************************************************************


-- From a Tcl page:
--   ns_db dml $db "begin dlfi_ripcords.update_values(
--     pl_list.integer_list([join $ripcord_id_list ", "])
--     ,pl_list.number_list([join $ripcord_value_list ", "])
--     ,$user_id
--   ); end;"
-- 
-- Where the dlfi_ripcords.update_values function is defined as:
--   procedure update_values (
--     ripcord_id_list     in pl_list.integer_list
--     ,ripcord_value_list in pl_list.number_list
--     ,user_id            in users.user_id%TYPE
--   );


-- is
--   v_list    pl_list.integer_list;
--   v_string  pl_list.string_csv ;
--
--   select
--     to_number(to_char(holiday_date, 'j')) as holiday_julian
--   bulk collect into v_list 
--   from market_holidays
--   where market_name = 'dlfi'
--     and holiday_type = 'h'
--     and holiday_date >= min_d
--     and holiday_date <= max_d
--   order by holiday_date ;
--   v_string := pl_list.var_list_csv(v_list);


-- procedure suspend_orders
--   -- Suspend a whole list of orders at once.  --atp@arsdigita.com, 2001/02/23
-- (
--   order_id_list  in  pl_list.integer_list
--   -- User whom we will log as having done the suspend:
--   ,v_user_id     in  users.user_id%TYPE  default 1
--   ,state_change_reason    in  orders.state_change_reason%TYPE
--                               default 'Suspended manually.'
--   -- NOT IMPLEMENTED:
--   ,refresh_bond_quotes_p  in  char  default 't'
-- )
-- is
--   --v_bond_id bond_descriptions.bond_id%TYPE;
-- begin
--   -- Here's a kludgier way to do the below, using dynamic
--   -- pl/sql instead of forall:
--   --
--   --execute immediate
--   --  'update orders  set ' ||
--   --  '  status = ''s'' ' ||
--   --  '  ,state_change_reason = ' || state_change_reason ||
--   --  '  ,last_modifying_user = ' || v_user_id ||
--   --  '  ,last_modified = ' || sysdate ||
--   --  'where order_id in (' || pl_list.integer_list_csv(order_id_list) || ')' ;
--
--   -- Use FORALL instead of a regular FOR loop, because it is
--   -- faster (see PL/SQL documentation on bulk binds for more
--   -- info).
--   --
--   FORALL i in order_id_list.first .. order_id_list.last
--   update orders  set
--     status = 's'
--     ,state_change_reason = state_change_reason
--     ,last_modifying_user = v_user_id
--     ,last_modified = sysdate
--   where order_id = order_id_list(i) ;
--
--   -- TODO:  Add optional refresh of bond_quotes.
-- end suspend_orders;


-- procedure process_yield_curve_update (
--  curve_id               in  yield_curves.curve_id%TYPE,
--  updated_point_id_list  in  pl_list.integer_list
-- )
-- is
--   -- [snip]
-- begin
--   -- Don't do anything unless at least one point on the curve
--   -- has been updated.
--   --
--   if updated_point_id_list is not null and updated_point_id_list.COUNT > 0 then
--     -- Lock *all* open orders for bonds that have municash
--     -- orders affected by this yield curve update.
--     --
--     OPEN affected_orders_lock FOR
--       'select * ' ||
--       'from orders_live ' ||
--       'where bond_id in ( ' ||
--       ' select distinct bond_id ' ||
--       ' from orders_live_municash ' ||
--       ' where curve_point_id in (' ||
--       pl_list.integer_list_csv(updated_point_id_list) || ')) ' ||
--       'for update'
--     ;
--     -- [snip]


-- function n_pulled_ripcords
--   -- Returns the number of ripcords in the specified list
--   -- that are currently invalid.
-- (
--  ripcord_id_list pl_list.integer_list
-- )
-- return integer
-- is
--  n_pulled_ripcords integer := 0;
-- begin
--  if ripcord_id_list.COUNT > 0 then
--   execute immediate
--    'select sum(decode(valid_value_p, ''f'', 1, 0)) ' ||
--    'from ripcords_active ' ||
--    'where ripcord_id in (' || pl_list.integer_list_csv(ripcord_id_list) || ')'
--   into n_pulled_ripcords;
--  end if;
--
--  return n_pulled_ripcords;
-- end n_pulled_ripcords;


-- ****************************************************************
--   END of Usage Examples
-- ****************************************************************