---------------------------------------------------------------------
-- A set of utilities dealing with various aspects of the repository
---------------------------------------------------------------------

create or replace package acs_util
is
  
-- Determine if an item in some sort of hierarchy has children
function has_children (
    item_id_in       IN varchar2,
    table_name_in    IN varchar2,
    parent_column_in IN varchar2
) return char;

-- Perform a SQL query which will return some values
-- or NULL. Return 't' if a value was returned, 'f' otherwise
function boolean_query (
  query_in IN varchar2,
  not_flag_in IN char := 'f'
) return char;

-- Convert a SQL string to a TCL string: surround the string with
-- {} if it contains spaces
function sql_to_tcl_string (
  string_in IN varchar2  
) return varchar2;

end acs_util;
/
show errors

create or replace package body acs_util
is

function has_children (
    item_id_in       IN varchar2,
    table_name_in    IN varchar2,
    parent_column_in IN varchar2
) return char
is
  v_newline varchar2(10) := '
';
  v_return char(1);
begin
 
  execute immediate 'select ''t'' ' || 
    '   from dual where exists (select 1 from ' || table_name_in || 
    '     where ' || parent_column_in || ' = ''' || item_id_in || ''')' 
   into
      v_return;

   return 't';

exception 
  when no_data_found then
    return 'f';

end has_children;

function boolean_query (
  query_in IN varchar2,
  not_flag_in IN char := 'f'
) return char
is
  v_flag char(1);
  v_sql_query varchar2(4000);
begin
  v_sql_query := 'select ''t'' from dual where ';

  if not_flag_in <> 'f' then
    v_sql_query := v_sql_query || 'not ';
  end if; 

  execute immediate v_sql_query || 'exists ( ' || query_in || ' )' into v_flag;
  return 't';

exception 
  when no_data_found then
    return 'f';
end boolean_query;

function sql_to_tcl_string (
  string_in IN varchar2  
) return varchar2
is
begin
  if instr(string_in, ' ') <> 0 then
    return '{' || string_in || '}';
  else
    return string_in;
  end if;
end sql_to_tcl_string;

end acs_util;
/
show errors