This discussion is archived
4 Replies Latest reply: Dec 13, 2012 2:42 AM by BluShadow RSS

Reg : Column Size -

ranit B Expert
Currently Being Moderated
Hi All,

I've got a doubt regarding alter the column size.
There's a table column which is currently 255 and I want to increase it to max i.e. 4000 Char

It is used in many places by various objects - Procs/Packages/Triggers/Views...

Is it advisable to use Varchar2(4000Char) or using LONG or CLOBS??
If I use LONG/CLOB now, will it affect the existing objects since they believe the column to be VARCHAR2...?


Help highly appreciated.

My Database :
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE     10.2.0.3.0     Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Thanks in Advance,
Ranit B.
  • 1. Re: Reg : Column Size -
    hitgon Expert
    Currently Being Moderated
    It is used in many places by various objects - Procs/Packages/Triggers/Views...
    You could use the %type inside the above object so you not bother of any data type changes of related table

    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#BEIIGBBF


    If I use LONG/CLOB now, will it affect the existing objects since they believe the column to be VARCHAR2...?
    YES if you not use %type
  • 2. Re: Reg : Column Size -
    BluShadow Guru Moderator
    Currently Being Moderated
    ranit B wrote:
    Hi All,

    I've got a doubt regarding alter the column size.
    There's a table column which is currently 255 and I want to increase it to max i.e. 4000 Char
    Be aware, the Maximum is 4000 bytes not char. If your database is using a multi byte character set, the maximum could actually be as small as 1000 characters (if you have 4 bytes per character).
    It is used in many places by various objects - Procs/Packages/Triggers/Views...

    Is it advisable to use Varchar2(4000Char) or using LONG or CLOBS??
    Never use LONG. the LONG datatype was deprecated over a decade ago. If you're needing to store large amounts of text, use CLOB.
    If I use LONG/CLOB now, will it affect the existing objects since they believe the column to be VARCHAR2...?
    It's one of those "it depends" answers, because it depends what the code is doing with it. It's not so much a case of the code/objects believing it to be varchar2, but whether that code or objects can do implicit conversions without issue. If it's just a case of something inserting into it, believing it's a varchar2, then it would work ok e.g.
    SQL> create table mytable (x clob);
    
    Table created.
    
    SQL> insert into mytable (x) values ('This is my varchar string');
    
    1 row created.
    The problem would obviously be if there is code that is reading the data into a varchar2 variable or datatype and the data exceeds the 4000 byte limit, in which case you will get issues.

    The best way to look at is is... Yes, it will effect existing code/objects, and you need to carry out an impact analysis to see where and how it's used.
  • 3. Re: Reg : Column Size -
    ranit B Expert
    Currently Being Moderated
    Thanks Hitgon & Blu.

    @Hitgon - I'm not sure on the %TYPE usage and code being age-old can't alter that now.

    @Blu -
    How can we exactly check for all places where this column is being used??
    Currenlty, I'm using DBA_SOURCE to check for all places where this is used. Is this ok?

    Please advise.
    Ranit B.
  • 4. Re: Reg : Column Size -
    BluShadow Guru Moderator
    Currently Being Moderated
    ranit B wrote:
    Thanks Hitgon & Blu.

    @Hitgon - I'm not sure on the %TYPE usage and code being age-old can't alter that now.

    @Blu -
    How can we exactly check for all places where this column is being used??
    Currenlty, I'm using DBA_SOURCE to check for all places where this is used. Is this ok?

    Please advise.
    Ranit B.
    Along with the (USER|DBA)_DEPENDENCIES view, that's probably a fairly good estimation. However it can never account for people who've written shoddy dynamic code where the columns are text values or have been stored as data on the database etc. to be used in dynamically generated queries.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points