Hello,
As Oracle decided to expand identifier name lengths, from 30 BYTES to 128 BYTES, they provided a useful CONSTANT named ORA_MAX_NAME_LEN to be used in PL/SQL variables so that the length going forward is dynamic and will widen automatically.
However, there are several cases where Developers do have tables which track who does what when or for logging purposes and we have to create custom-columns to reflect which SCHEMA_NAME, OBJECT_NAME, PROXY_USER ran which PROGRAM UNIT and when.
When Oracle decides to expand these max identifier lengths, we have to manually "widen" these columns as well in our custom tables. I was surprised that I couldn't use ORA_MAX_NAME_LEN in a DDL statement like ALTER, in order to expand these column widths for my custom tables.
Examples:-
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!
ALTER TABLE tb_test MODIFY (schema_name VARCHAR2(128 BYTE)); --This works but not flexible for future upgrades!
However, we should avoid hard-coding numeric literals like 128 above as what if Oracle decides to expand identifier lengths again in the future? We have to manually expand ALL the relevant columns in ALL the tables again?
For more details, see thread
@"Chris Saxon-Oracle"