Apps 9 mod to lengthen a field

It’s the employee ID. It’s so thoroughly littered everywhere, I figured it might be easier and safer to change the length of every 11-character column to 12 characters, doing something like this…

(I haven’t tested any of this yet. It’s late on a Sunday night here.)

BEGIN
   FOR rec_ IN (
      SELECT 'ALTER TABLE "' || table_name || '" '
          || 'MODIFY "' || column_name || '" '
          || 'VARCHAR2(12)' AS sq
        FROM user_tab_cols
       WHERE table_name IN (SELECT table_name
                              FROM user_tables
                             WHERE tablespace_name IS NOT NULL)
         AND table_name NOT IN (SELECT mview_name FROM user_mviews)
         AND table_name NOT IN (SELECT queue_table FROM user_queues)
         AND table_name NOT LIKE 'AQ$%'
         AND data_type = 'VARCHAR2'
         AND char_used = 'C'
         AND virtual_column = 'NO'
         AND char_length = 11
      ORDER BY 1
   ) LOOP
      BEGIN
         EXECUTE IMMEDIATE rec_.sq;
      EXCEPTION WHEN OTHERS THEN
         dbms_output.put_line(rec_.sq);
         dbms_output.put_line(SQLERRM);
      END;
   END LOOP;
END;
/


BEGIN
   FOR rec_ IN (
      SELECT 'COMMENT ON COLUMN '
          || '"' || table_name || '"."' || column_name || '" '
          || 'IS q''['
          || REPLACE(comments, 'STRING(11)', 'STRING(12)')
          || ']''' AS sq
        FROM user_col_comments
       WHERE comments LIKE '%STRING(11)%'
      ORDER BY 1
   ) LOOP
      BEGIN
         EXECUTE IMMEDIATE rec_.sq;
      EXCEPTION WHEN OTHERS THEN
         dbms_output.put_line(rec_.sq);
         dbms_output.put_line(SQLERRM);
      END;
   END LOOP;
END;
/

UPDATE dictionary_sys_view_column_tab
   SET column_comment = REPLACE(column_comment, 'STRING(11)', 'STRING(12)')
 WHERE column_comment LIKE '%STRING(11)%';

UPDATE dictionary_sys_view_column_tab
   SET column_datatype = REPLACE(column_datatype, 'STRING(11)', 'STRING(12)')
 WHERE column_datatype LIKE '%STRING(11)%';

UPDATE report_sys_column_tab
   SET column_dataformat = REPLACE(column_dataformat, 'STRING(11)', 'STRING(12)')
 WHERE column_dataformat LIKE '%STRING(11)%';



-- This step is the most dubious. I doubt it'll work, honestly....
DECLARE
   body_ CLOB;
BEGIN
   FOR rec_ IN (
      SELECT object_name
        FROM user_objects
       WHERE object_name IN ('ABROAD_P_G_FORMAT_API',
                             /* ... all the affected packages I found ... */
                             'XLR_WRITE_BACK_TYPE_API')
         AND object_type = 'PACKAGE'
       ORDER BY 1
   ) LOOP
      spec_ := NULL;
      spec_ := dbms_metadata.get_ddl('PACKAGE_SPEC', rec_.object_name);
      IF SUBSTR(UPPER(spec_), 1, 100) NOT LIKE '%WRAPPED%' THEN
         spec_ := REPLACE(spec_, 'VARCHAR2(11)', 'VARCHAR2(12)');
         spec_ := REPLACE(spec_, 'STRING(11)',   'STRING(12)');
         EXECUTE IMMEDIATE spec_;
      END IF;

      body_ := NULL;
      body_ := dbms_metadata.get_ddl('PACKAGE_BODY', rec_.object_name);
      IF SUBSTR(UPPER(body_), 1, 100) NOT LIKE '%WRAPPED%' THEN
         body_ := REPLACE(body_, 'VARCHAR2(11)', 'VARCHAR2(12)');
         body_ := REPLACE(body_, 'STRING(11)',   'STRING(12)');
         EXECUTE IMMEDIATE body_;
      END IF;
   END LOOP;
END;
/

For years, our standard practice was to create a user ID with 8 characters, but this broke when two people had the same name. We switched to a 12-character system to resolve name conflicts, and that worked during testing. We don’t use the HR module, and only users that need to book time against projects are created as employees, so now this edge case has left us stuck. If a user ID can be 30 characters and a person ID can be 20, it makes no sense to me why an employee ID is limited to 11. What was the point of limiting that so tightly?

There’s no technical reason to limit a VARCHAR2 like this. The length is really just a constraint. It only uses the room it needs for the data.

-- DROP TABLE c_short_tab PURGE;
-- DROP TABLE c_long_tab PURGE;

CREATE TABLE c_short_tab (x VARCHAR2(11)) TABLESPACE ifsapp_data;
CREATE TABLE c_long_tab (x VARCHAR2(100)) TABLESPACE ifsapp_data;

DECLARE
   hash_ RAW(32);
   value_ VARCHAR2(11);
BEGIN
   hash_ := HEXTORAW('0');
   FOR i IN 0..1048575 LOOP
      hash_ := dbms_crypto.hash(hash_, dbms_crypto.hash_sh256);
      value_ := SUBSTR(RAWTOHEX(hash_), 1, 11);
      INSERT INTO c_short_tab (x) VALUES (value_);
      INSERT INTO c_long_tab  (x) VALUES (value_);
   END LOOP;
END;
/

COMMIT;

SELECT segment_name,
       bytes,
       blocks,
       extents
  FROM user_segments
 WHERE segment_name IN ('C_LONG_TAB', 'C_SHORT_TAB');