Solved

size of varchar2

Posted on 2013-12-10
5
513 Views
Last Modified: 2013-12-10
What is the max size of VARCHAR2? How many zeros it can hold? -
0
Comment
Question by:d27m11y
5 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39709116
VARCHAR2 in a table or SQL: 4000 bytes.

In PL/SQL code: 32K.

The docs have this:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/datatypes.htm#CHDDCGEE

>>How many zeros it can hold?

As many as can fit given the constraints above and your character set?
0
 
LVL 8

Expert Comment

by:5teveo
ID: 39709118
http://www.orafaq.com/faq/what_is_the_difference_between_varchar_varchar2_and_char_data_types

'Varchar2' appears to hold 4000 characters vs 2000 of 'varchar'
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39709123
>>'Varchar2' appears to hold 4000 characters vs 2000 of 'varchar'

Incorrect.  It's bytes not characters.  If the database is using a multi-byte characterset the maximum number of characters is reduced.  For example: a 2-byte characterset the maximum is 2000 characters.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 39709179
depends on the version.

up through version 7,  varchar2 had a limit of 2000 bytes
from version 8 through 11gR2  varchar2 has a limit of 4000 bytes

in 12c, default limit is 4000 bytes, but you can extend it to 32767 bytes but they are stored externally like LOB segments

All of those are for SQL limits.

In pl/sql, varchar2 can hold up to 32767 bytes
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 39709458
Hi,
As 'zero' is probably not a multibyte character, you can store 4000 of them. But I'm not sure it's a good idea to store zeros like that.
Regards,
Franck.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
history tablespace temp usage 2 42
Determine Who is Runnig my Bash Shell Script 4 82
sort a spool into file output in oracle 1 30
Require data to appear on a single line 2 46
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question