Forum Stats

  • 3,872,013 Users
  • 2,266,363 Discussions
  • 7,911,025 Comments

Discussions

Auto Extend feature for VARCHAR2 datatype

Elamaran Police
Elamaran Police Member Posts: 117
edited Jul 13, 2016 4:04PM in Database Ideas - Ideas

It is good to have custom Auto Extend feature when data for that column grows beyound the limit. It should be managed when declaring the datatype for the column

Elamaran Police
6 votes

Active · Last Updated

Comments

  • Gerald Venzl-Oracle
    Gerald Venzl-Oracle Member, Moderator Posts: 85 Employee

    Thanks for your contribution!

    The VARCHAR2 datatype has a fixed upper limit of 32767 bytes in Oracle Database 12c.

    However, the LOB data type does have a much higher data size limit of a maximum size of ((4 GB - 1) * DB_BLOCK_SIZE initialization parameter).

    I'm not clear on what is meant by a "custom Auto Extend" feature. What would custom mean?

  • Thanks for your contribution!

    The VARCHAR2 datatype has a fixed upper limit of 32767 bytes in Oracle Database 12c.

    However, the LOB data type does have a much higher data size limit of a maximum size of ((4 GB - 1) * DB_BLOCK_SIZE initialization parameter).

    I'm not clear on what is meant by a "custom Auto Extend" feature. What would custom mean?

    We are not declaring all columns with its max. limit when defining tables. If I foresee that the length may grow in future, then I can declare the column with AUTO EXTEND so that the length of that datatype would be extended. This can be also used when Oracle further increases the max. length.

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    We are not declaring all columns with its max. limit when defining tables. If I foresee that the length may grow in future, then I can declare the column with AUTO EXTEND so that the length of that datatype would be extended. This can be also used when Oracle further increases the max. length.

    If you expect to grow it in the future, why not simply declare the column with max size.

    Or what problem do you see when you declare a column as VARCHAR2(4000)?

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

    If you expect to grow it in the future, why not simply declare the column with max size.

    Or what problem do you see when you declare a column as VARCHAR2(4000)?

    I think the question "Why don't we just make all character columns VARCHAR2(4000)?" is an old one.

    https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3014576500346676264

    https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1145132537055

  • If you expect to grow it in the future, why not simply declare the column with max size.

    Or what problem do you see when you declare a column as VARCHAR2(4000)?

    Today if I declare with max value say 4000. But now in 12c we can have till 32000. SO in this case I need to manually increase by altering the columns right ?

  • Gerald Venzl-Oracle
    Gerald Venzl-Oracle Member, Moderator Posts: 85 Employee

    We are not declaring all columns with its max. limit when defining tables. If I foresee that the length may grow in future, then I can declare the column with AUTO EXTEND so that the length of that datatype would be extended. This can be also used when Oracle further increases the max. length.

    Or you could just increase the column length once needed via a simple ALTER TABLE statement.

    There is a very important downside to this: What if the column length shouldn't increase? What if one should only put 25 bytes in the column and then somebody comes along and just throws 1000 bytes in there. Maybe an error, maybe a hack, maybe a bug in an application. It all works fine if there is just one system or one user on the database. But a database is built for hundreds of systems, thousands of users. What if application A wants an increase to 40 bytes but application B isn't ready for it yet and would cause an error? That is exactly why one would use a strong typed data model.

    Sven W.Elamaran Police
  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown
    edited Jul 14, 2016 1:25PM

    Today if I declare with max value say 4000. But now in 12c we can have till 32000. SO in this case I need to manually increase by altering the columns right ?

    Elamaran Police wrote:
    
    Today if I declare with max value say 4000. But now in 12c we can have till 32000. SO in this case I need to manually increase by altering the columns right ?
    
    

    Yes. Again: Where is the issue with that?

    If you have a column that you want to grow almost indefinitly, then make it a clob column.

    For me this idea smells like you had a very specifc problem in the past. However you want to solve it using a very generic approach, without considering the drawbacks of the general solution. Maybe for your very specific problem, the real culprit was something else.

    @William Robertson I'm aware of some of the drawbacks. I do not educate to make *every* column a varchar2(4000) column.