Stack Overflow
Stack Overflow

Questions tagged [varchar2]

The VARCHAR2 datatype stores variable-length character strings.

The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. For each row, Oracle Database stores each value in the column as a variable-length field unless a value exceeds the column's maximum length, in which case Oracle Database returns an error. Using VARCHAR2 and VARCHAR saves on space used by the table.

The VARCHAR datatype is synonymous with the VARCHAR2 datatype. To avoid possible changes in behavior, always use the VARCHAR2 datatype to store variable-length character strings.

http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1822

192 questions
42
votes
5 answers

What is the max size of VARCHAR2 in PL/SQL and SQL?

I am on Oracle 10g. In a requirement I need to increase the size of a pl/sql VARCHAR2 variable. It is already at 4000 size. I have read that in PL/SQL, VARCHAR2 can be up to 32767 bytes. For SQL the limit is 4000 bytes Can I increase the size…
Ajay Gupta
  • 464
  • 1
  • 5
  • 10
28
votes
11 answers

Finding rows that don't contain numeric data in Oracle

I am trying to locate some problematic records in a very large Oracle table. The column should contain all numeric data even though it is a varchar2 column. I need to find the records which don't contain numeric data (The to_number(col_name)…
Ben
  • 2,058
  • 9
  • 29
  • 39
28
votes
7 answers

What is the major difference between Varchar2 and char

Creating Table: CREATE TABLE test ( charcol CHAR(10), varcharcol VARCHAR2(10)); SELECT LENGTH(charcol), LENGTH(varcharcol) FROM test; Result: LENGTH(CHARCOL) LENGTH(VARCHARCOL) --------------- ------------------ 10 …
ram12393
  • 1,284
  • 3
  • 14
  • 29
28
votes
3 answers

Impact of defining VARCHAR2 column with greater length

What are the effects of defining a column with VARCHAR2(1000) instead of VARCHAR2(10) in Oracle, when the values are not longer than 10 Byte? Does the column only take the space really necessary to store the values, or would that have any negative…
Peter Lang
  • 54,264
  • 27
  • 148
  • 161
23
votes
1 answer

Difference between NVARCHAR in Oracle and SQL Server?

We are migrating some data from sql server to oracle. For columns defined as NVARCHAR in SQL server we started creating NVARCHAR columns in Oracle thinking them to be similar..But it looks like they are not. I have read couple of posts on…
Zenil
  • 1,491
  • 3
  • 12
  • 21
20
votes
1 answer

What is the default size of a varchar2 input to Oracle stored procedure, and can it be changed?

I have a stored procedure in Oracle Database 10g where my input is a varchar2 but I'm having issues getting it to run when the input string is long (not sure of exact length maybe > 8000). My thought is the 'intext varchar2' (as below) is by…
Gern Blanston
  • 42,482
  • 19
  • 50
  • 64
17
votes
6 answers

Why does Oracle varchar2 have a mandatory size as a definition parameter?

I want to know why Oracle needs the size parameter in the definition of the VARCHAR2. I think that is for constraint. Would it be a better option that oracle takes this parameter as an optional like NUMBER dataType? I often have problems resizing…
user2427
  • 7,842
  • 19
  • 61
  • 71
14
votes
1 answer

difference between NLS_NCHAR_CHARACTERSET and NLS_CHARACTERSET for Oracle

I would like to know the difference between NLS_NCHAR_CHARACTERSET and NLS_CHARACTERSET settings in Oracle? From my understanding, NLS_NCHAR_CHARACTERSET is for NVARCHAR data types and for NLS_CHARACTERSET would be for VARCHAR2 data types. I tried…
Steven Tang Ti Khoon
  • 479
  • 2
  • 6
  • 17
12
votes
1 answer

How to change a dataype CLOB TO VARCHAR2(sql)

Table: customers ID NAME DATATYPE NUMBER VARCHAR2(100) CLOB I want to change the DATA column from CLOB to `VARCHAR2(1000) I have try ALTER TABLE customers MODIFY DATA VARCHAR2 (1000) also ALTER TABLE customers MODIFY (DATA…
PHPnoob
  • 293
  • 1
  • 3
  • 12
11
votes
1 answer

Limit listagg function to first 4000 characters

I have a query that uses the listagg function to get all rows as a comma delimited string to ultimately be shipped to a big text box. I'm getting the following exception: ORA-01489: result of string concatenation is too long I know the problem is…
spots
  • 2,483
  • 5
  • 23
  • 38
9
votes
4 answers

ORA-12899 value too large for column despite of same length

I am running the following query. But getting ORA-12899. Altough the length of string am trying to insert is 30. INSERT INTO TABLE1 SELECT * FROM temp_Table1 where LENGTH(column1)=30; SQL Error: ORA-12899:value too large for column…
Shitu
  • 821
  • 5
  • 12
  • 20
9
votes
3 answers

What is the correct way of checking if varchar2 is empty?

According to official 11g docs Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same…
Roland
  • 7,525
  • 13
  • 61
  • 124
8
votes
2 answers

Why does an oracle plsql varchar2 variable need a size but a parameter does not?

Suppose you have this procedure: PROCEDURE f (param VARCHAR2) IS var VARCHAR2(10); BEGIN var := 'hi'; END f; I would like to understand why var needs a length specified, but param does not. I'm having a hard time finding information about…
grinch
  • 804
  • 1
  • 9
  • 17
7
votes
3 answers

Oracle empty strings

How do you guys treat empty strings with Oracle? Statement #1: Oracle treats empty string (e.g. '') as NULL in "varchar2" fields. Statement #2: We have a model that defines abstract 'table structure', where for we have fields, that can't be NULL,…
Vugluskr
  • 1,426
  • 1
  • 12
  • 12
7
votes
3 answers

oracle convert DD-MON-YY to DD/MM/YYYY

I am trying to convert the format of a varchar2 column from 'DD-MON-YY' to 'DD/MM/YYYY'. In example: from '01-JAN-16' to '01/01/2016' In case you can ask or it may help: 'MON' part is in English however my current NLS settings are in Turkish. All…
kzmlbyrk
  • 583
  • 1
  • 4
  • 17
1
2 3
…
12 13