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
1
vote
1 answer

VarChar2 to Char comparison : Oracle settings can allow this?

I've just a quick question to see how it comes that I get 2 different results for the same thing. We have two databases which are built up exactly the same in terms of structure. In both, there is a view which do a comparison between a varchar2(10)…
Maarten Kesselaers
  • 1,141
  • 2
  • 8
  • 19
1
vote
1 answer

oracle varchar max lower than specified

I have a table with a column varchar2(4000), where I got a problem inserting data, such that: ORA-12899: value too large for column "XXX"."YYY"."ZZZ" (actual: 2132, maximum: 2048) When I run select * from user_tab_columns where table_name =…
Niels Bech Nielsen
  • 4,777
  • 1
  • 21
  • 44
1
vote
1 answer

Inserting binary data into Varchar2 with OTL (OCCI, OCI)

How do I insert data that might be binary into a Varchar2 with OTL? (OCI/OCCI would be OK of course) Background: We have a lot of Varchar2 columns which are generally not binary, but it might happen somewhere someday (I am especially concerned…
Olav
  • 1,758
  • 4
  • 27
  • 49
0
votes
3 answers

Handling large String values in database VARCHAR2 columns

I have defined an VARCHAR2(2000 CHAR) field in the database. Often, we run into issues where the client input is more than 2000 characters and we end up missing out on the record. I am not sure if setting the field(s) to maximum characters would be…
name_masked
  • 9,544
  • 41
  • 118
  • 172
0
votes
0 answers

Use Create view to convert a varchar field and contents to number field

I have table with a Varchar field. See structure. IMAGE_KEY VARCHAR2(32 BYTE) DOC_TYPE VARCHAR2(8 BYTE) DOC_KEY VARCHAR2(256 BYTE) LAST_UPDT DATE UPDT_USER VARCHAR2(6 BYTE) BLK_HANDLE VARCHAR2(16 BYTE) DOC_KEY_ID NUMBER(15,0) FE_ID …
C Awaibe
  • 11
  • 4
0
votes
1 answer

Remove Duplicate nested phrases from a string with Oracle Regexp_replace

as part of a PL/SQL script, we are setting a Varchar variable called 'V_COMMENT' as such: V_COMMENT := INCOMING_COMMENT || '[' || V_COMMENT || ']'; because this statement is run potentially multiple times for each record, we will eventually end up…
Austin
  • 18
  • 4
0
votes
1 answer

Oracle VARCHAR2 - Inserted value too large for column

Working on a Java/Spring system with Oracle(19c Enterprise) database, I have recently bumped into this error: database.org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call…
user2526586
  • 972
  • 2
  • 12
  • 27
0
votes
2 answers

Split json field to extract value in oracle sql

I have a table with a varchar2 type field in which a simil "json" like this is saved: json data{ first:one second:two } the string "json" present in the example above is also saved in the field. I need a query that brings out the values…
Prodox21
  • 107
  • 1
  • 2
  • 9
0
votes
1 answer

Oracle: how to convert varchar2 to date

Oracle 19.3 on Win 2019 I have a VARCHAR2 column that used to store dates as "2013-01-27T10:47:09-08:00" by the front end application, but now it's being stored as "12/09/2021 04:58:24 PM" When I try to retrieve the data, I am getting an…
epipko
  • 473
  • 5
  • 18
0
votes
2 answers

how to store double colon values in oracle database table

I have excel which i am trying to import in oracle database table. Some of the values in excel consist of for example 14:39.5 with double colon. What dataype in oracle database table i should provide to store this value ? Currently have given…
Andrew
  • 3,632
  • 24
  • 64
  • 113
0
votes
0 answers

When to convert from varchar2 to clob in Oracle Database?

We are doing an Oracle Database Unicode conversion from WE8ISO8859P15 single Byte characterset to AL32UTF8 is Multibyte Characterset. It seems our software vendor mixed VARCHAR2(CHAR and Byte) in the data model. After the datamodel has been moved to…
r0tt
  • 379
  • 3
  • 20
0
votes
3 answers

In oracle i have this Dataype: "VARCHAR2(255 Byte)" what would be the converted/mapping Dataype in PostgreSQL?

In Oracle I have this Dataype: VARCHAR2(255 Byte) what would be the converted/mapping Dataype in PostgreSQL?
user9342493
  • 21
  • 5
0
votes
1 answer

Oracle | Varchar2 | Number Equality | Different output

I have a table in Oracle, let's say Table1: Column - ticketNo - NOT NULL VARCHAR2(10) Now when I run different queries, I get different outputs, though record is present. select * from Table1 where ticketNo = '0900000106'; -- Fetches the…
Ankur Singhal
  • 26,012
  • 16
  • 82
  • 116
0
votes
2 answers

SQL sum money with currency

If I want a query that displays the number of customers living in Penang and also the sum of their income, how should I do it? SELECT COUNT(Cust_state), SUM(Cust_income) FROM Customer WHERE Cust_state = ‘Penang’; This would not work as the values…
user12911503
0
votes
1 answer

Oracle varchar2 and unicode texts

According to spec VARCHAR2(max_size CHAR) should store max_size chars. I observe other/strange behavior for Unicode texts. Let's consider that example: create table test (id varchar2(3 char) not null, primary key (id)); insert into test(id)…
FoxyBOA
  • 5,788
  • 8
  • 48
  • 82
1 2 3
…
12 13