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
3
votes
1 answer

How does varchar2 sorting in oracle works?

I'm sorting a Oracle SQL query by a Varchar2 column. But when I get the results i can not correctly understand the ordering. Which is the logical order. first lower letters? numbers? symbols?. Here I attach the resulting ordering of one of the…
Fgblanch
  • 5,195
  • 8
  • 37
  • 51
3
votes
3 answers

VARCHAR2 datatype in ORACLE

I am very new to oracle and today I found about the data type VARCHAR2, and I wanted to learn more about it and google the datatype where I met the problem. I have gone through few articles about the datatype, and I found out some direct opposite…
Ramesh
  • 2,297
  • 2
  • 20
  • 42
3
votes
1 answer

PL/SQL Compilation error - PLS-00382: expression is of wrong type

I'm running the below PL/SQL... DECLARE BEGIN FOR i IN (select VALUE from REWARDS) LOOP insert into BT_CMS.T_REWARDS_TYPES (ID, REWARD_LABEL, REWARD_VALUE, REWARD_METHOD, UPDATE_USER, UPDATE_DATE, PAYMENT_PROVIDER_ID, CREATE_DATE,…
Stephen Walsh
  • 815
  • 4
  • 18
  • 34
3
votes
1 answer

Replace CHAR with VARCHAR2

How can I replace CHAR with VARCHAR2 in all tables in a schema? Note: I'm content with a query that returns the ALTER TABLE statements so I can save the script and run it again.
Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
3
votes
1 answer

Why doesn't translate work on some characters?

I am trying to remove certain characters from a VARCHAR2 using translate. Characters 160 (some kind of space) and 243 (paragraph control character?), however, appear to be "phantom" characters that are undetectable by both INSTR and TRANSLATE. …
oscilatingcretin
  • 10,457
  • 39
  • 119
  • 206
3
votes
1 answer

Remove interpunctional characters

Im looking for some way to remove interpunction in varchar2. Thats mean i want to get "Marian" from "Marián" and "Cernicky" from "Černický" Thanks for any help or suggestion Ondrej
ondro tadanai
  • 69
  • 3
  • 10
3
votes
2 answers

Inserting into CLOB very very slow

I am experiencing a significant performance drop when inserting string of 'TinyString' (just an example string) into stored-in-row CLOB, as compared to VARCHAR2. My understanding is that when storing data of < 4000 bytes into a CLOB with STORAGE IN…
PeetP
  • 137
  • 4
  • 13
3
votes
2 answers

pl/sql procedure returns 3 question marks ('???') for varchar2 columns (wls 10.3.4 - weblogic 11g)

I got the following code that works fine on OAS10, for fetching array of custom object from sql procedure: Connection conn = null; OracleCallableStatement stmt = null; RequestsWrapper requestsWrapper = null; conn = getConnection(DATASOURCE); if…
Kenny
  • 153
  • 1
  • 9
3
votes
1 answer

Oracle varchar2 to nvarchar2 conversion

If I change an existing column type from varchar2 to nvarchar2 in Oracle will Oracle automatically convert existing column data between character set or should I do it myself? I'm using Oracle 11g, the varchar2 character set is WE8MSWIN1252 and the…
user1399160
  • 33
  • 1
  • 1
  • 4
2
votes
1 answer

PL/SQL - Delete a character in a VARCHAR2

(I'm sorry for my english...) I'd like to know if I can delete a character in a varchar2 : FOR FF IN REQ LOOP IF FF.COLUMN_NAME = ANCIEN THEN ORDRE_DYN := ORDRE_DYN || ANCIEN; ELSE ORDRE_DYN := ORDRE_DYN || FF.COLUMN_NAME; END IF; ORDRE_DYN :=…
Carvallegro
  • 1,241
  • 4
  • 16
  • 24
2
votes
2 answers

using long string(over 4000) in oracle query

I know that in sql varchar2 can only be around 4000. I know that in oracle PL varchcar2 can be around 32000. I have a varchar2 variable defined that is over 4000 characters long and I want to use it in a query. I don't want to insert the value into…
kralco626
  • 8,456
  • 38
  • 112
  • 169
2
votes
1 answer

LISTAGG 4000 Character Limit - Result of string concatenation is too long

select t.name, listagg(t.text) from user_source t group by t.name; I am trying to execute the code above but since varchar2 is limited by 4000 chars it throws error. I tried to convert listagg to xml but I could not solve the ORA-64451:…
mertcan
  • 45
  • 5
2
votes
2 answers

Casting numerical value to VARCHAR2 throws ORA-03113

When we run statements against an Oracle 12c Enterprise Edition Release 12.2.0.1.0, which contains casts of numerical values to VARCHAR2(4000 char), we receive an ORA-03113 end-of-file on communication channel. Various resources - such as…
ihkawiss
  • 986
  • 3
  • 9
  • 25
2
votes
3 answers

Oracle not able to insert data into varchar2(4000 char) column

I use Oracle 12c. I have below table in my DB. CREATE TABLE TEST_T (COL VARCHAR2(4000 CHAR)); I need insert multibyte characters in this table. The character is 3 byte character. I am able to insert only 1333 (upto 3999 bytes) characters in…
hemalp108
  • 1,209
  • 1
  • 15
  • 23
2
votes
2 answers

Set Oracle VARCHAR2 length i Byte using SQLALCHEMY

I'm currently moving some data around using python and pandas. Here I am getting a bit stuck on the data types i can read in the existing database being in Byte and SQL Alchemy definition being in Char For example from sqlalchemy.dialects.oracle…
Henrik Poulsen
  • 935
  • 2
  • 13
  • 32
1 2
3
…
12 13