What are the repercussions of declaring all strings varchar2(255) even
if many will have lengths <=20 or <=30 (but none >255)?

Is there any value declaring some to be varchar2(20) or varchar2(30)
rather than just making them all varchar2(255)?

I'm converting a MySQL database to Oracle. By default MySQL accepts
strings too long and overwrites memory, which is okay as long as the
memory is unusued, but becomes a problem when it overwrites used
memory.

Thanks,
Ted Bogart

tedb@nc.rr.com wrote:
> What are the repercussions of declaring all strings varchar2(255) even
> if many will have lengths <=20 or <=30 (but none >255)?
>
> Is there any value declaring some to be varchar2(20) or varchar2(30)
> rather than just making them all varchar2(255)?
>
> I'm converting a MySQL database to Oracle. By default MySQL accepts
> strings too long and overwrites memory, which is okay as long as the
> memory is unusued, but becomes a problem when it overwrites used
> memory.
>
> Thanks,
> Ted Bogart

In terms of storage none. In terms of data integrity: Huge.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

On Thu, 02 Jun 2005 16:35:36 -0700, tedb interested us by writing:

> What are the repercussions of declaring all strings varchar2(255) even
> if many will have lengths <=20 or <=30 (but none >255)?
>
> Is there any value declaring some to be varchar2(20) or varchar2(30)
> rather than just making them all varchar2(255)?
>
> I'm converting a MySQL database to Oracle. By default MySQL accepts
> strings too long and overwrites memory, which is okay as long as the
> memory is unusued, but becomes a problem when it overwrites used
> memory.
>
> Thanks,
> Ted Bogart

Depends on your application. Generally no impact on the database.

Can your app handle freestyle ranges from 0-255 bytes? Does it really
make sense? DO you want to rely on your app developers to make the
correct decision ALL the time or do you want to put an added integrity
check at the one-and-only place that you are guaranteed to hit. (Meaning,
users have a habit of bypassing apps using Access, Excel, etc. and royally
messing data that isn't protected by constraints.)

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***

> What are the repercussions of declaring all strings varchar2(255) even
> if many will have lengths <=20 or <=30 (but none >255)?
>
> Is there any value declaring some to be varchar2(20) or varchar2(30)
> rather than just making them all varchar2(255)?
>
> I'm converting a MySQL database to Oracle. By default MySQL accepts
> strings too long and overwrites memory, which is okay as long as the
> memory is unusued, but becomes a problem when it overwrites used
> memory.
>
> Thanks,
> Ted Bogart

Thanks for the help. I was mainly worried about storage and
performance. The data is mainly "generic" and used in unbounded Java
Strings. Data integrity is already comprised since we're currently
using MySQL which is silently ignoring size errors and either
truncating or overwriting the data, with future consequences.

Per the advice given, I'll review the application for data integrity
issues.

Thanks for your timely help. It's very much appreciated and will make
my day so much better tomorrow!!!!

Thanks,
Ted Bogart