1

While working with some large scripts I encountered errors related with the 4000 character limit that VARCHAR2 has in SQL but not in PL/SQL.

As explained here the VARCHAR2 datatype has a 4000 character limited, while the PL/SQL has a much larger limit of 32,767.

Why does that difference exist? Wouldn't have been better to have both VARCHAR2 with the same limit? It seems a rather confusing and prone to error decision.

Update

I updated my question to add ansible's comment. Oracle VARCHAR2 and PL/SQL VARCHAR2 limit are on bytes, not in characters. This can have significant impact in multibyte characters system.

3
  • 2
    Actually in Oracle 12c you can extend VARCHAR2 limit to 32K. See Oracle Parameter MAX_STRING_SIZE Jan 8 '15 at 15:12
  • 2
    To clarify, the actual limit of the VARCHAR2 datatype is not 4000 characters, but 4000 bytes. The same is true inside of PL/SQL (32,767 bytes). This can make a significant impact if using a multi-byte character set that allocates more than 1 byte to 1 character. Oracle Doc. reference
    – ansible
    Jan 13 '15 at 23:54
  • @ansible I added an update regarding your comment.
    – Dzyann
    Jan 14 '15 at 12:15
2

It is just a fact of life. Different history, different beliefs, different goals.

Your might find this post on PL/SQL and its goals helpful in seeing some of the differences: http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/03_types.htm

It amusingly starts with:

Like--but oh how different! --William Wordsworth

There are ANSI Standard data types, but I believe that VARCHAR2 is an Oracle standard, not a part of ANSI. It appears from history that Oracle's VARCHAR2 was designed to overcome the limitations of their earlier VARCHAR implementation.

Having said that, practically every SQL dialect has some unique datatype that is not accepted by all other SQL dialects. (I primarily work with MS SQL Server, but do at times need to absorb data from other systems.)

Fortunately there are a number of online resources to help you in mapping one datatype to another as you move between the products of multiple vendors. Usually it is not too difficult. Note: it is not only strings (such as CHAR,VARCHAR) that can have conflicting definitions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.