Hello,
In preparation for an anticipated upgrade from version 11.2.0.4 to 12.2, I'm trying to refactor my code to ensure it will keep on working post-upgrade.
When reading @"Chris Saxon-Oracle"'s article on Long Names at https://blogs.oracle.com/sql/12-things-developers-will-love-about-oracle-database-12c-release-2#long-names, it outlines a few options on how to refactor PL/SQL declared variables using built-in constant like ORA_MAX_NAME_LEN, so that the code change will be more dynamic and maintenance-friendly for future upgrades, in case if the 128 BYTE limit is again increased to another value.
In a similar vein, I am looking for a way to dynamically or in a more maintenance-friendly manner, to also change various Table's column data-types from a hard-coded literal numeric value in the DDL from say, VARCHAR2(30 BYTE) to VARCHAR2(ORA_MAX_NAME_LEN BYTE) but it doesn't seem to like it when used in a DDL:-
ALTER TABLE tb_test MODIFY (schema_name VARCHAR2(ORA_MAX_NAME_LEN BYTE)); --This throws an error!
ALTER TABLE tb_test MODIFY (table_name VARCHAR2(user_tables.table_name%TYPE BYTE)); --This throws an error!
While there's flexibility using the ORA_MAX_NAME_LEN constant value in PL/SQL variables, however, there doesn't seem to have a similar option for refactoring the columns' data-types in the underlying tables using DDL statements.
Is there an option to NOT hard-code a literal numeric value of 128, as shown:-
ALTER TABLE tb_test MODIFY (schema_name VARCHAR2(128 BYTE)); --This works but not flexible for future upgrades!
Thanks!