Index: openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql,v
diff -u -r1.4.2.4 -r1.4.2.5
--- openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql	16 Feb 2022 19:55:09 -0000	1.4.2.4
+++ openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql	17 Feb 2022 10:27:22 -0000	1.4.2.5
@@ -1,13 +1,9 @@
 PROMPT starting utilities-create.sql....
 --
--- /packages/acs-kernel/sql/utilities-create.sql
+-- PL/SQL utility routines for accessing schema information
 --
--- Useful PL/SQL utility routines.
+-- @author Jon Salz (jsalz@mit.edu), Antonio Pisano, Gustaf Neumann
 --
--- @author Jon Salz (jsalz@mit.edu)
--- @creation-date 12 Aug 2000
--- @cvs-id $Id$
---
 
 create or replace TYPE t_util_primary_keys IS TABLE OF varchar2(100);
 /
@@ -17,58 +13,58 @@
 create or replace package util
 as
     function multiple_nextval(
-	v_sequence_name in varchar2,
-	v_count in integer)
+        v_sequence_name in varchar2,
+        v_count in integer)
         return varchar2;
 
     function logical_negation (
         true_or_false IN varchar2)
-	return varchar2;
-    
+        return varchar2;
+
     function table_exists (
-	name in varchar2)
-	return char;
-	
+        name in varchar2)
+        return char;
+
     function table_column_exists (
-	t_name  in varchar2,
-	c_name in varchar2)
-	return char;
-    
+        t_name  in varchar2,
+        c_name in varchar2)
+        return char;
+
     function view_exists (
-	name in varchar2)
-	return boolean;
-	
+        name in varchar2)
+        return char;
+
     function index_exists (
-	name in varchar2)
-	return boolean;
+        name in varchar2)
+        return char;
 
     function foreign_key_exists (
         table_name IN varchar2,
-	column    IN varchar2,
-	reftable  IN varchar2,
-	refcolumn IN varchar2)
-    return boolean;
+        column    IN varchar2,
+        reftable  IN varchar2,
+        refcolumn IN varchar2)
+    return char;
 
     function unique_exists (
         table_name IN varchar2,
-	column    IN varchar2,
-	single_p  IN boolean default true)
-    return boolean;
+        column    IN varchar2,
+        single_p  IN boolean default true)
+    return char;
 
     function primary_key_exists (
         table_name IN varchar2,
-	column    IN varchar2,
-	single_p  IN boolean default true)
-    return boolean;
+        column    IN varchar2,
+        single_p  IN boolean default true)
+    return char;
 
     function not_null_exists (
         table_name IN varchar2,
-	column    IN varchar2)
-    return boolean;
+        column    IN varchar2)
+    return char;
 
     function get_default (
         table_name IN varchar2,
-	column    IN varchar2)
+        column    IN varchar2)
     return LONG;
 
     function get_primary_keys (
@@ -84,24 +80,24 @@
     -- Retrieves v_count (not necessarily consecutive) nextval values from the
     -- sequence named v_sequence_name.
     function multiple_nextval(
-	v_sequence_name in varchar2,
-	v_count in integer
+        v_sequence_name in varchar2,
+        v_count in integer
     )
     return varchar2
     is
-	a_sequence_values varchar2(4000);
+        a_sequence_values varchar2(4000);
     begin
-	execute immediate '
-	    declare
-		a_nextval integer;
-	    begin
-		for counter in 1..:v_count loop
-		    select ' || v_sequence_name || '.nextval into a_nextval from dual;
-		    :a_sequence_values := :a_sequence_values || '','' || a_nextval;
-		end loop;
-	    end;
-	' using in v_count, in out a_sequence_values;
-	return substr(a_sequence_values, 2);
+        execute immediate '
+            declare
+                a_nextval integer;
+            begin
+                for counter in 1..:v_count loop
+                    select ' || v_sequence_name || '.nextval into a_nextval from dual;
+                    :a_sequence_values := :a_sequence_values || '','' || a_nextval;
+                end loop;
+            end;
+        ' using in v_count, in out a_sequence_values;
+        return substr(a_sequence_values, 2);
     end;
 
     function logical_negation (
@@ -112,239 +108,183 @@
       IF true_or_false is null THEN
         return null;
       ELSIF true_or_false = 'f' THEN
-        return 't';   
-      ELSE 
-        return 'f';   
+        return 't';
+      ELSE
+        return 'f';
       END IF;
     END logical_negation;
-    
+
     function table_exists (
         name IN varchar2)
     return char
     as
-
-      v_count integer;
       v_exists char;
 
     begin
 
-      select decode(count(*),0,0,1) into v_count 
+      select decode(count(*),0,'f','t') into v_exists
       from user_tables where table_name = upper(table_exists.name);
 
-      if v_count = 1 then
-        v_exists := 't';
-      else
-        v_exists := 'f';
-      end if;
-
       return v_exists;
 
     END table_exists;
-    
+
     function table_column_exists (
         t_name  IN varchar2,
         c_name IN varchar2)
     return char
     as
-      v_count integer;
       v_exists char;
 
     begin
 
-      select decode(count(*),0,0,1) into v_count from user_tab_columns
+      select decode(count(*),0,'f','t') into v_exists
+      from user_tab_columns
       where table_name = upper(table_column_exists.t_name)
       and column_name = upper(table_column_exists.c_name);
 
-      if v_count = 1 then
-        v_exists := 't';
-      else
-        v_exists := 'f';
-      end if;
-
       return v_exists;
-      
+
     END table_column_exists;
-    
+
     function view_exists (
         name IN varchar2)
-    return boolean
+    return char
     as
-      v_count integer;
-      v_exists boolean;
+      v_exists char;
 
     begin
 
-      select decode(count(*),0,0,1) into v_count 
+      select decode(count(*),0,'f','t') into v_exists
       from user_views where view_name = upper(view_exists.name);
 
-      if v_count = 1 then
-        v_exists := true;
-      else
-        v_exists := false;
-      end if;
-
       return v_exists;
 
     END view_exists;
-    
+
     function index_exists (
         name IN varchar2)
-    return boolean
+    return char
     as
-      v_count integer;
-      v_exists boolean;
+      v_exists char;
 
     begin
-      select decode(count(*),0,0,1) into v_count 
+      select decode(count(*),0,'f','t') into v_exists
       from user_indexes where index_name = upper(index_exists.name);
 
-      if v_count = 1 then
-        v_exists := true;
-      else
-        v_exists := false;
-      end if;
-
       return v_exists;
     END index_exists;
 
     function foreign_key_exists (
         table_name IN varchar2,
-	column    IN varchar2,
-	reftable  IN varchar2,
-	refcolumn IN varchar2)
-    return boolean
+        column    IN varchar2,
+        reftable  IN varchar2,
+        refcolumn IN varchar2)
+    return char
     as
-      v_count integer;
-      v_exists boolean;
+      v_exists char;
 
     begin
-      select decode(count(*),0,0,1) into v_count
+      select decode(count(*),0,'f','t') into v_exists
       from user_constraints cons
       left join user_cons_columns cols on cols.constraint_name = cons.constraint_name
       left join user_constraints cons_r on cons_r.constraint_name = cons.r_constraint_name
       left join user_cons_columns cols_r on cols_r.constraint_name = cons.r_constraint_name
       where cons.constraint_type = 'R'
-        and cons.table_name = foreign_key_exists.table_name
-	and cols.column_name = foreign_key_exists.column
-  	and cons_r.table_name = foreign_key_exists.reftable
-  	and cols_r.column_name = foreign_key_exists.refcolumn;
+        and cons.table_name = upper(foreign_key_exists.table_name)
+        and cols.column_name = upper(foreign_key_exists.column)
+        and cons_r.table_name = upper(foreign_key_exists.reftable)
+        and cols_r.column_name = upper(foreign_key_exists.refcolumn);
 
-      if v_count = 1 then
-        v_exists := true;
-      else
-        v_exists := false;
-      end if;
-
       return v_exists;
     end foreign_key_exists;
 
     function unique_exists (
         table_name IN varchar2,
-	column    IN varchar2,
-	single_p  IN boolean default true)
-    return boolean
+        column    IN varchar2,
+        single_p  IN boolean default true)
+    return char
     as
-      v_count integer;
-      v_exists boolean;
+      v_exists char;
       v_single integer;
 
     begin
       v_single := case when unique_exists.single_p then 1 else 0 end;
-      
-      select decode(count(*),0,0,1) into v_count
+
+      select decode(count(*),0,'f','t') into v_exists
        from all_constraints c
        join all_cons_columns cc on (c.owner = cc.owner
-	                                 and c.constraint_name = cc.constraint_name)
+                                         and c.constraint_name = cc.constraint_name)
        where c.constraint_type = 'U'
-	 and c.table_name = unique_exists.table_name
-	 and cc.column_name = unique_exists.column
-	 and ((v_single = 0) or (
-	    select count(*) from all_cons_columns
-	     where constraint_name = c.constraint_name) = 1);
-             
-      if v_count = 1 then
-        v_exists := true;
-      else
-        v_exists := false;
-      end if;
+         and c.table_name = unique_exists.table_name
+         and cc.column_name = unique_exists.column
+         and ((v_single = 0) or (
+            select count(*) from all_cons_columns
+             where constraint_name = c.constraint_name) = 1);
 
       return v_exists;
     END unique_exists;
 
     function primary_key_exists (
         table_name IN varchar2,
-	column    IN varchar2,
-	single_p  IN boolean default true)
-    return boolean
+        column    IN varchar2,
+        single_p  IN boolean default true)
+    return char
     as
-      v_count integer;
-      v_exists boolean;
+      v_exists char;
       v_single integer;
 
     begin
       v_single := case when primary_key_exists.single_p then 1 else 0 end;
 
-      select decode(count(*),0,0,1) into v_count
+      select decode(count(*),0,'f','t') into v_exists
        from all_constraints c
        join all_cons_columns cc on (c.owner = cc.owner
-	                                 and c.constraint_name = cc.constraint_name)
+                                    and c.constraint_name = cc.constraint_name)
        where c.constraint_type = 'P'
-	 and c.table_name = primary_key_exists.table_name
-	 and cc.column_name = primary_key_exists.column
-	 and ((v_single = 0) or (
-	    select count(*) from all_cons_columns
-	     where constraint_name = c.constraint_name
-	       and owner = c.owner) = 1);
+         and c.table_name = primary_key_exists.table_name
+         and cc.column_name = primary_key_exists.column
+         and ((v_single = 0) or (
+            select count(*) from all_cons_columns
+             where constraint_name = c.constraint_name
+               and owner = c.owner) = 1);
 
-      if v_count = 1 then
-        v_exists := true;
-      else
-        v_exists := false;
-      end if;
-
       return v_exists;
     END primary_key_exists;
 
     function not_null_exists (
         table_name IN varchar2,
-	column    IN varchar2)
-    return boolean
+        column    IN varchar2)
+    return char
     as
-      v_count integer;
-      v_exists boolean;
+      v_exists char;
 
     begin
-      select decode(count(*),0,0,1) into v_count
+      select decode(count(*),0,'f','t') into v_exists
         from all_tab_columns
-	 where table_name = not_null_exists.table_name
-	   and column_name = not_null_exists.column
+         where table_name = not_null_exists.table_name
+           and column_name = not_null_exists.column
            and nullable = 'N';
 
-      if v_count = 1 then
-        v_exists := true;
-      else
-        v_exists := false;
-      end if;
-
       return v_exists;
     END not_null_exists;
 
     function get_default (
         table_name in varchar2,
-	column    in varchar2)
+        column    in varchar2)
     return long
     as
       v_value long;
 
     begin
       select data_default into v_value
         from all_tab_columns
-	 where table_name = get_default.table_name
-	   and column_name = get_default.column;
+         where table_name = get_default.table_name
+           and column_name = get_default.column;
 
       return v_value;
-      
-      exception when no_data_found then 
+
+      exception when no_data_found then
          return null;
     end get_default;
 
@@ -357,12 +297,12 @@
     begin
           select cols.column_name
           bulk collect into v_ret
-	  from all_constraints cons, all_cons_columns cols
-	  where cols.table_name = get_primary_keys.table_name
-	  and cons.constraint_type = 'P'
-	  and cons.constraint_name = cols.constraint_name
-	  and cons.owner = cols.owner
-	  order by cols.table_name, cols.position;
+          from all_constraints cons, all_cons_columns cols
+          where cols.table_name = upper(get_primary_keys.table_name)
+          and cons.constraint_type = 'P'
+          and cons.constraint_name = cols.constraint_name
+          and cons.owner = cols.owner
+          order by cols.table_name, cols.position;
 
         return v_ret;
     end get_primary_keys;