Forum Stats

  • 3,824,849 Users
  • 2,260,430 Discussions
  • 7,896,330 Comments

Discussions

Ability to use VARCHAR2(ORA_MAX_NAME_LEN BYTE) in a DDL instead of hard-coding a numeric literal!

Sam_P
Sam_P Member Posts: 155 Blue Ribbon
edited Jul 30, 2019 10:07AM in Database Ideas - Ideas

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

Sam_PctriebSven W.
6 votes

Active · Last Updated

«1

Comments

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    What would you expect to happen if the ORA_MAX_NAME_LEN value changed in the future?

  • Sam_P
    Sam_P Member Posts: 155 Blue Ribbon
    edited Aug 16, 2019 6:09PM

    What would you expect to happen if the ORA_MAX_NAME_LEN value changed in the future?

    Perhaps, it would dynamically expand/reflect the column data length based on the max length, as at that time, without having to touch and alter each and every table and their columns which have hard-coded numeric values like 30 or 128 entered.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,816 Red Diamond

    I would rather have the ability to define an integer constant, and then use that constant as the size in a string type (varchar2) definition.

  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    I would rather have the ability to define an integer constant, and then use that constant as the size in a string type (varchar2) definition.

    Why?   Just use LONG all the time.    It works in every situation.

    (Or its equivalent VARCHAR2(32767)).

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,816 Red Diamond

    Why?   Just use LONG all the time.    It works in every situation.

    (Or its equivalent VARCHAR2(32767)).

    Yep. LONGs are not given enough recognition as the most useful type when it comes to inflexible complex code, that spells job protection. ;-)

    gaverill
  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    Yep. LONGs are not given enough recognition as the most useful type when it comes to inflexible complex code, that spells job protection. ;-)

    How does that result in inflexible code?

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,816 Red Diamond

    How does that result in inflexible code?

    If so useful, and flexible, why is LONG deprecated? Not used at all in a PL/SQL product like APEX?

    You are grossly mistaken if you think LONG is a better datatype than VARCHAR2 or LOB.

  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    If so useful, and flexible, why is LONG deprecated? Not used at all in a PL/SQL product like APEX?

    You are grossly mistaken if you think LONG is a better datatype than VARCHAR2 or LOB.

    LONG is VARCHAR2(32767) in PL/SQL.     I'm not talking about the database datatype of LONG.

    Same name, different thing.

  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    LONG is VARCHAR2(32767) in PL/SQL.     I'm not talking about the database datatype of LONG.

    Same name, different thing.

    Look in package spec. SYS,STANDARD.       All the subtypes are defined there.

    My apologies, not 32767, but 32760.       SUBTYPE LONG IS VARCHAR2(32760);

  • Sam_P
    Sam_P Member Posts: 155 Blue Ribbon

    Look in package spec. SYS,STANDARD.       All the subtypes are defined there.

    My apologies, not 32767, but 32760.       SUBTYPE LONG IS VARCHAR2(32760);

    While you're here anyways and so passionate about this, why not click to VOTE UP on this idea?