Thanks in Advance,
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
ranit B wrote: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).
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...Never use LONG. the LONG datatype was deprecated over a decade ago. If you're needing to store large amounts of text, use CLOB.
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...?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.
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.
SQL> create table mytable (x clob); Table created. SQL> insert into mytable (x) values ('This is my varchar string'); 1 row created.
ranit B wrote: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.
Thanks Hitgon & Blu.
@Hitgon - I'm not sure on the %TYPE usage and code being age-old can't alter that now.
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?