Home » SQL & PL/SQL » SQL & PL/SQL » Maximum size for columns with Varchar (Oracle 9.2.0.3)
Maximum size for columns with Varchar [message #410774] Tue, 30 June 2009 03:41 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
What would happen if we define VARCHAR2(4000) for each column of type Varchar.


Thanks
Prachi


Re: Maximum size for columns with Varchar [message #410776 is a reply to message #410774] Tue, 30 June 2009 03:43 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'd have a horrible time trying to get select output in a readable format in sqlplus and any other tool that sizes column widths according to the data dictionary?
Re: Maximum size for columns with Varchar [message #410781 is a reply to message #410774] Tue, 30 June 2009 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to do that?

Regards
Michel
Re: Maximum size for columns with Varchar [message #410787 is a reply to message #410781] Tue, 30 June 2009 04:41 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I have dimensioned a column to VARCHAR2(4000) in my table. I chose 4000 only because its the maximum allowed for this type, and I dont want to be bothered later by people telling me the column is not large enough. On the average today this column will receive strings of 60 chars, but this could double or triple in the future.

This might be a BAD idea..but I want to know the reasons.

What would if we maximize the width as we know that Varchar is a variable length string and it wont padded with blank
Thanks
Re: Maximum size for columns with Varchar [message #410794 is a reply to message #410787] Tue, 30 June 2009 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't use a CLOB then you will have no problem if your users want to insert more than 4000 bytes?

Regards
Michel
Re: Maximum size for columns with Varchar [message #410812 is a reply to message #410774] Tue, 30 June 2009 06:59 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Sounds like a style thing to me, but remember that it is very easy to increase the size via an alter command. Sounds like you are maybe looking at a max, down the road, of only 200. Could make it 300 and still be quite safe.
Re: Maximum size for columns with Varchar [message #410819 is a reply to message #410812] Tue, 30 June 2009 07:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Basically, you don't do it because the column size is useful information, and there is no advantage to discarding it.

we had an example through here last week - someone was trying to find all the columns in his database that held a Social security number, in the format xxx-xxxx-xx.
If all the columns in the DB were sized according to their contents, then he could have resried his search to only those columns defined as CHAR(11) or VARCHAR2(11)

Additionaly, it provides an additional sanity check to DML operations - if someone tries to put too much data into a field, then the question 'Why are you trying to do that' needs to be asked.
Previous Topic: query tuning
Next Topic: CREATE TABLE with LONG datatype, shows error
Goto Forum:
  


Current Time: Fri Dec 09 06:17:57 CST 2016

Total time taken to generate the page: 0.12286 seconds