Hi Tom!

I've only worked with MS SQL Server in the past and only recently started with Oracle and must admit your knowledge/advice on this site always gets me past a problem.

I have something I want to ask though, not a problem, more a misunderstanding of the Varchar2 type maybe. Here goes:

I recently implemented a split function to help me with strings being sent from ASP.net, in order to loop through the string (using a delimiter) and do whatever with the values. (there might be a better way in oracle 11g now, since what I implemented came from 2004, but it works great nonetheless)

Now my question is this:

Everywhere I read I'm told the varchar2 max storage value is 4000 bytes or characters, thus creating a table with a varchar2 column can be a max of 4000 bytes/characters, which i understand.

This doesn't seem to be true for varchar2 variables in stored procs / functions though? The max size you can create a variable with is in terms of the numeric range 1 - 32767. Being useful only up to a point where you want to store a 4000+ varchar2 variable in a varchar2(4000) column.

My question: How come a varchar2 column in a table has a limit of 4000 chars, while the variable equal can go up to 32767? Is it simply a matter of Oracle allowing you to do string manipulation to a higher level "in memory", but limits you to storing only 4000 for the sake of, what, performance or physical database size?

Sorry if it's a question that won't contribute to the real knowledge base, curiosity got the better of me.

Thanks!