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.

Improve this question
3
  • 2
    Actually in Oracle 12c you can extend VARCHAR2 limit to 32K. See Oracle Parameter MAX_STRING_SIZE
    – Wernfried Domscheit
    Jan 8, 2015 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, 2015 at 23:54
  • @ansible I added an update regarding your comment.
    – Dzyann
    Jan 14, 2015 at 12:15

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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