difference between varchar2 in oracle sql and pl/sql

Question

Is there any difference between varchar2 in oracle sql and pl/sql or both are same or there are some implementation difference.

ErikL · Accepted Answer · 2013-09-30 10:53:39Z

4

There is at least one difference: the length.

in Pl/sql, it can have a maximum length of 32,767 bytes, while in sql it can only be 4,000 bytes long.

answered Sep 30, 2013 at 10:53

ErikL

2,0517 gold badges35 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Frank Schmitt Over a year ago

This is true for Oracle versions up to 11gR2. Starting with 12c, maximum length of Varchar2 in SQL is also 32767 bytes, see docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm

ErikL Over a year ago

Thanks Frank, didn't know that yet. Guess I should brush up my Oracle skills a bit.

Jon Heller Over a year ago

Another difference is memory allocation: "For a CHAR variable, or for a VARCHAR2 variable whose maximum size is less than 2,000 bytes, PL/SQL allocates enough memory for the maximum size at compile time. For a VARCHAR2 whose maximum size is 2,000 bytes or more, PL/SQL allocates enough memory to store the actual value at run time. In this way, PL/SQL optimizes smaller VARCHAR2 variables for performance and larger ones for efficient memory use." Although this probably only matters if you're misusing PL/SQL.

Collectives™ on Stack Overflow

difference between varchar2 in oracle sql and pl/sql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related