RE: RE: Get max length of data for a column

Morning All-

I *thought* VARCHAR2 was varying size based on a Run Length Encoding for the column
where CHAR was always fixed length 

to acquire column length the path of least resistance is to take the length from USER_TAB_COLUMNS e.g.
SQL> select DATA_LENGTH from USER_TAB_COLUMNS where table_name='USER$' AND COLUMN_NAME='PASSWORD';

Norm or Bert does this conform to your understanding?

Cheers,
Martin Gainty 
______________________________________________ 
GMT+5 (this week)


To: [email protected]
From: [email protected]
Date: Wed, 18 Apr 2012 08:26:05 +0100
Subject: Re: [toad] RE: Get max length of data for a column
















 



  


    
      
      
      Morning Mike,



> Thanks for that clarification. To be honest, we don't really use CHAR here

> anymore, which is why I've either forgotten that important piece of

> information, or (worse) never knew it.

Years ago, I wrote a "data obfuscation" routine with simply replaced 

certain columns with random data generated by DBMS_RANDOM. All my CHAR 

columns ended up being exactly the defined length! That's when I noticed 

it. I ended up using VSIZE() to get the length of the TRIM()med CHAR 

columns. Sort of stuck in my "cache" since then.



> Out of interest, is there any reason

> why one would want to use CHAR rather than VARCHAR?

I'm not sure. I use them occasionally myself. Just for internal space 

saving. A VARCHAR2 has a hidden size setting. If the column is 100 long 

and you store 40 in it, there's a counter internally that says "this is 

how long the data is". Not a great reason, but if you have flag columns 

- gender, for example, that are only one character long, you don't need 

the counter internally.



There are probably other good reasons for using CHAR, but I can't think 

of any off hand. Maybe Bert knows - he knows lots of good stuff!



-- 

Cheers,

Norm. [TeamT]