Index: openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.9.1d19-5.9.1d20.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.9.1d19-5.9.1d20.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.9.1d19-5.9.1d20.sql 21 Dec 2016 00:14:56 -0000 1.1.2.1 @@ -0,0 +1,245 @@ + +create or replace package util +as + function multiple_nextval( + v_sequence_name in varchar2, + v_count in integer) + return varchar2; + + function logical_negation ( + true_or_false IN varchar2) + return varchar2; + + function table_exists ( + name in varchar2) + return boolean; + + function table_column_exists ( + t_name in varchar2, + c_name in varchar2) + return boolean; + + function view_exists ( + name in varchar2) + return boolean; + + function index_exists ( + name in varchar2) + return boolean; + + function foreign_key_exists ( + table IN varchar2, + column IN varchar2, + reftable IN varchar2, + refcolumn IN varchar2) + return boolean; + + function unique_exists ( + table IN varchar2, + column IN varchar2, + single_p IN boolean default true) + return boolean; + + function primary_key_exists ( + table IN varchar2, + column IN varchar2, + single_p IN boolean default true) + return boolean; + + function not_null_exists ( + table IN varchar2, + column IN varchar2) + return boolean; + + function get_default ( + table IN varchar2, + column IN varchar2) + return LONG; + + TYPE primary_keys IS TABLE OF varchar2; + + function get_primary_keys(table IN varchar2) + return primary_keys; + +end util; +/ +show errors + +create or replace package body util +as + -- 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 + ) + return varchar2 + is + 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); + end; + + function logical_negation ( + true_or_false IN varchar2) + return varchar2 + as + begin + IF true_or_false is null THEN + return null; + ELSIF true_or_false = 'f' THEN + return 't'; + ELSE + return 'f'; + END IF; + END logical_negation; + + function table_exists ( + name IN varchar2) + return boolean + as + begin + return exists (select 1 from user_tables where table_name = t_name); + END table_exists; + + function table_column_exists ( + t_name IN varchar2, + c_name IN varchar2) + return boolean + as + begin + return exists (select 1 from user_tab_columns where c.table_name = t_name and c.column_name = c_name); + END table_column_exists; + + function view_exists ( + name IN varchar2) + return boolean + as + begin + return exists (select 1 from user_views where view_name = name); + END view_exists; + + function index_exists ( + name IN varchar2) + return boolean + as + begin + return exists (select 1 from user_indexes where index_name = name); + END index_exists; + + function foreign_key_exists ( + table IN varchar2, + column IN varchar2, + reftable IN varchar2, + refcolumn IN varchar2) + return boolean + as + begin + return exists ( + SELECT 1 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 = table + AND COLS.COLUMN_NAME = column + AND CONS_R.TABLE_NAME = reftable + AND COLS_R.COLUMN_NAME = refcolumn); + END foreign_key_exists; + + function unique_exists ( + table IN varchar2, + column IN varchar2, + single_p IN boolean default true) + return boolean + as + begin + return exists ( + SELECT 1 + FROM all_constraints c + JOIN all_cons_columns cc ON (c.owner = cc.owner + AND c.constraint_name = cc.constraint_name) + WHERE c.constraint_type = 'U' + AND c.table_name = table + AND cc.column_name = column + and (not single_p or ( + select count(*) from all_cons_columns + where constraint_name = c.constraint_name) = 1)); + END unique_exists; + + function primary_key_exists ( + table IN varchar2, + column IN varchar2, + single_p IN boolean default true) + return boolean + as + begin + return exists ( + SELECT 1 + FROM all_constraints c + JOIN all_cons_columns cc ON (c.owner = cc.owner + AND c.constraint_name = cc.constraint_name) + WHERE c.constraint_type = 'P' + AND c.table_name = table + AND cc.column_name = column + and (not single_p or ( + select count(*) from all_cons_columns + where constraint_name = c.constraint_name + and owner = c.owner) = 1)); + END primary_key_exists; + + function not_null_exists ( + table IN varchar2, + column IN varchar2) + return boolean + as + begin + return ( + SELECT nullable = 'N' + FROM ALL_TAB_COLUMNS + WHERE table_name = table + AND column_name = column); + END not_null_exists; + + function get_default ( + table IN varchar2, + column IN varchar2) + return LONG + as + begin + return ( + SELECT DATA_DEFAULT + FROM ALL_TAB_COLUMNS + WHERE table_name = table + AND column_name = column); + END get_default; + + function get_primary_keys(table IN varchar2) + return primary_keys IS + rec primary_keys; + BEGIN + SELECT cols.column_name + BULK COLLECT INTO rec + FROM all_constraints cons, all_cons_columns cols + WHERE cols.table_name = table + 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 rec; + END get_primary_keys; + +end util; +/ +show errors