Monetra 8.7 introduced a new SQL subsystem that enforced the use of ANSI quoting of identifiers to aid in portability, and also enforced the use of integer types for boolean values. Unfortunately this broke compatibility with some naming conventions or data types used on Oracle and PostgreSQL systems. Other database systems were unaffected by this change.

When upgrading from a release of Monetra v8.0-8.6 to a release of Monetra v8.7 or later, it is officially recommended to perform an export and import during your upgrade procedure. However, this can lead to a large amount of downtime, so we have documented a procedure to perform an in-place modification of your schema. When prompted during the upgrade if you need to install a custom-build SQL module, this is the time you will run the conversion script.

On Oracle, you would run a PL/SQL script such as:

-- Allow output to console for debugging
SET serveroutput ON;
-- Make sure string comparisons are case-sensitive
ALTER SESSION SET NLS_SORT=BINARY;
DECLARE
  TYPE table_list IS TABLE OF VARCHAR2(32);
  my_tables table_list := table_list('TRANSDATA', 'IMAGEREPO', 'L3ITEMS', 'STATS', 'USERS', 'AUTHASPERMS', 'MISC', 'MERCHINFO', 'SETTLEQUEUE', 'COUNTERS', 'IDTRACK', 'CARDSHIELD_KEYS', 'CARDSHIELD_DEVICES', 'RESTRICTIONS', 'RECURRING', 'RECURRING_HIST', 'CRON', 'BBTRANSDATA', 'BBL3ITEMS',
'BBMERCHINFO', 'BBACCTINFO', 'BBACCTIDS', 'BBMERCHIDS', 'BBCOUNTERS', 'EMV_CAPKS', 'EMV_CARDPARAMS', 'TERMLOAD_CUSTOM', 'PRODUCTLICENSE');
  mon87_table_name VARCHAR2(64);
  mon87_col_name VARCHAR2(64);
  my_query VARCHAR2(1024);
BEGIN
  -- Iterate across all known tables
  FOR i IN my_tables.FIRST .. my_tables.LAST LOOP

    -- Iterate across all columns in each table and make them lowercase
    FOR col IN (SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = my_tables(i)) LOOP
      mon87_col_name := LOWER(col.COLUMN_NAME);
      IF mon87_col_name = col.COLUMN_NAME THEN
        DBMS_OUTPUT.PUT_LINE(my_tables(i) || ': col ' || col.COLUMN_NAME || ' already good');
      ELSE
        DBMS_OUTPUT.PUT_LINE(my_tables(i) || ': rename col ' || col.COLUMN_NAME || ' to ' || mon87_col_name);
        my_query := 'ALTER TABLE "' || my_tables(i) || '" RENAME COLUMN "' || col.COLUMN_NAME || '" TO "' || mon87_col_name || '"';
        EXECUTE IMMEDIATE my_query;
      END IF;
    END LOOP;

    -- Only attempt to rename table to lowercase if it exists
    FOR x IN (SELECT TABLE_NAME FROM user_tables WHERE TABLE_NAME = my_tables(i)) LOOP
      mon87_table_name := LOWER(my_tables(i));
      IF mon87_table_name = my_tables(i) THEN
        DBMS_OUTPUT.PUT_LINE('table: ' || my_tables(i) || ' already good');
      ELSE
        DBMS_OUTPUT.PUT_LINE('renaming table: ' || my_tables(i) || ' to ' || mon87_table_name);
        my_query := 'ALTER TABLE "' || my_tables(i) || '" RENAME TO "' || mon87_table_name || '"';
        EXECUTE IMMEDIATE my_query;
      END IF;
    END LOOP;

  END LOOP;
END;

* NOTE: If you receive an error similar to the following:

ORA-54032: column to be renamed is used in a virtual column expression

You should reference: https://blogs.oracle.com/sql/ora-54033-and-the-hidden-virtual-column-mystery

If a different error occurs, the back-out procedure is:

-- Allow output to console for debugging
SET serveroutput ON;
-- Make sure string comparisons are case-sensitive
ALTER SESSION SET NLS_SORT=BINARY;
DECLARE
  TYPE table_list IS TABLE OF VARCHAR2(32);
  my_tables table_list := table_list('transdata', 'imagerepo', 'l3items', 'stats', 'users', 'authasperms', 'misc', 'merchinfo', 'settlequeue', 'counters', 'idtrack', 'cardshield_keys', 'cardshield_devices', 'restrictions', 'recurring', 'recurring_hist', 'cron', 'bbtransdata', 'bbl3items',
'bbmerchinfo', 'bbacctinfo', 'bbacctids', 'bbmerchids', 'bbcounters', 'emv_capks', 'emv_cardparams', 'termload_custom', 'productlicense');
  mon86_table_name VARCHAR2(64);
  mon86_col_name VARCHAR2(64);
  my_query VARCHAR2(1024);
BEGIN
  -- Iterate across all known tables
  FOR i IN my_tables.FIRST .. my_tables.LAST LOOP

    -- Iterate across all columns in each table and make them lowercase
    FOR col IN (SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = my_tables(i)) LOOP
      mon86_col_name := UPPER(col.COLUMN_NAME);
      DBMS_OUTPUT.PUT_LINE(my_tables(i) || ': rename col ' || col.COLUMN_NAME || ' to ' || mon86_col_name);
      my_query := 'ALTER TABLE "' || my_tables(i) || '" RENAME COLUMN "' || col.COLUMN_NAME || '" TO "' || mon86_col_name || '"';
      EXECUTE IMMEDIATE my_query;
    END LOOP;

    -- Only attempt to rename table to lowercase if it exists
    FOR x IN (SELECT TABLE_NAME FROM user_tables WHERE TABLE_NAME = my_tables(i)) LOOP
      mon86_table_name := UPPER(my_tables(i));
      DBMS_OUTPUT.PUT_LINE('renaming table: ' || my_tables(i) || ' to ' || mon86_table_name);
      my_query := 'ALTER TABLE "' || my_tables(i) || '" RENAME TO "' || mon86_table_name || '"';
      EXECUTE IMMEDIATE my_query;
    END LOOP;

  END LOOP;
END;

On PostgreSQL, the procedure is a little less involved:

ALTER TABLE cardshield_keys ALTER COLUMN provision_allowed TYPE SMALLINT USING CAST(provision_allowed AS SMALLINT);
ALTER TABLE cardshield_devices ALTER COLUMN active TYPE SMALLINT USING CAST(active AS SMALLINT);
ALTER TABLE emv_cardparams ALTER COLUMN is_contactless TYPE SMALLINT USING CAST(is_contactless AS SMALLINT);
ALTER TABLE productlicense ALTER COLUMN active TYPE SMALLINT USING CAST(active AS SMALLINT);

* NOTE: If an error occurs, the back-out procedure is:

ALTER TABLE cardshield_keys ALTER COLUMN provision_allowed TYPE CHAR USING CAST(provision_allowed AS CHAR);
ALTER TABLE cardshield_devices ALTER COLUMN active TYPE CHAR USING CAST(active AS CHAR);
ALTER TABLE emv_cardparams ALTER COLUMN is_contactless TYPE CHAR USING CAST(is_contactless AS CHAR);
ALTER TABLE productlicense ALTER COLUMN active TYPE CHAR USING CAST(active AS CHAR);