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

How to extract data from LONG datatype field using only SQL (without using PL/SQL block)?

How to extract data from LONG datatype field using only SQL (without using PL/SQL)? Getting error while concatenating with other columns- ORA00932: inconsistent datatypes DB: Oracle 8i enterprise edition
Soumya
  • 19
  • 4
1
vote
1 answer

inserting varchar2 field with python

I have varchar2 fields in my databases (Oracle and Impala) and I want to insert string fields into this fields. I could not insert the values as string as normally. I got following error AnalysisException: Possible loss of precision for target…
Murat Suluhan
  • 33
  • 6
1
vote
2 answers

Varchar2 datatype allows systimestamp

I have one table with varchar2 datatypes like below create table t11 (aa varchar2(100),bb varchar2(100)); Now, if I trying to insert systimestamp into above, values are getting inserted: insert into t11 values…
Ashish Patil
  • 4,428
  • 1
  • 15
  • 36
1
vote
1 answer

oracle : limit the varchar2 to 12 for phone number

I am trying to impose a constraint to fix the length of phone numbers to 12, but the code fails: create table customer ( rollcall number CONSTRAINT rc_pk PRIMARY KEY, fname varchar(2) not NULL, lname varchar(2) not NULL, phone varchar2(13) check…
Nigel
  • 47
  • 1
  • 2
  • 8
1
vote
2 answers

Is it possible to use CLOB with Materialized Views

In my project we have used a table with CLOB column in Materialized Views by converting it using TO_CHAR so the resulting column is a VARCHAR2 with size of 4000 characters. MAX(CASE WHEN BONG_NARR_TY = 'ADTX' AND TO_CHAR(BONG_NARR_TX) IS NOT NULL…
Ujjwal Pathak
  • 646
  • 12
  • 21
1
vote
1 answer

Varchar2 column versus variable memory allocation in pl/sql

In pl/sql, if you create a variable as varchar2(256) and then assign a 10 character string to the variable then the total memory used is 256 characters but if you declare it (4000 or more it will use only the 10 characters. Is that also true for…
user3876965
  • 11
  • 1
1
vote
3 answers

SQL Query - Greater Than with Text Data Type

I've searched around and couldn't find an answer anywhere. I'm querying a database that has stored numbers as a VARCHAR2 data type. I'm trying to find numbers that are greater than 1450000 (where BI_SO_NBR > '1450000'), but this doesn't bring back…
AdamH
  • 25
  • 6
1
vote
1 answer

Convert from Oracle VARCHAR2 to MySQL TEXT

How to do correct conversion of data from Oracle VARCHAR2 type to MySQL TEXT type? I want insert it into mysql table in field with TEXT type through DBLink.
lvccgd
  • 21
  • 3
1
vote
1 answer

Oracle - The lowest VARCHAR2 value

I need to query a table in the database on a column which is a VARCHAR2. I need to retrieve the records in chunks, not all at one go. ROWNUM is used for this purpose. The query is like this: select * from SOMETABLE where SOMECOLUMN > ? and rownum <=…
Manikandan Kannan
  • 8,684
  • 15
  • 44
  • 65
1
vote
1 answer

How to use varchar2 variable in XMLTable function?

I am trying to use XMLTable function as follow, DECLARE IDs VARCHAR2(2000); IDs := '4013105,4013106,4013107,4013108,4013109'; SELECT TO_NUMBER(COLUMN_VALUE) FROM XMLTABLE(:TRACT_IDS); but getting this error PL/SQL: ORA-19102: XQuery string…
user3119427
  • 11
  • 2
1
vote
2 answers

ORA-12899: value too large for column, even if value in Exception is smaller

I'm getting an ORA-12899 Error when updating Entities in the DB via EclipseLink. The Exception reads as follows: RuntimeException caught: org.springframework.transaction.TransactionSystemException: Could not commit JPA transaction; nested exception…
czo02
  • 33
  • 2
  • 8
1
vote
1 answer

Convert Varchar2 to Char array in Oracle

I have a varchar2 field and want to split it to array of chars Like 'ABCDEF' --> 'A' 'B' 'C' 'D' 'E' How can i convert my Field Values to chars array?
MH2538
  • 169
  • 2
  • 4
  • 11
1
vote
3 answers

How to mask characters with X in a varchar2 field in Oracle SQL

I have a list of varchar records (column) varchar2(30) with names. How to mask characters inside the name with the listed criteria. e.g. Tristram Vladimir Chan <---Original name 1234567890123456789012 <---ruler (character…
Din
  • 223
  • 3
  • 4
  • 8
1
vote
1 answer

Replace a part of a varchar2 column in Oracle

I've a varchar2 column in a table which contains a few entries like the following TEMPORARY-2 TIME ECS BOUND -04-Insuficient Balance I want to update these entries and make it TEMPORARY-2 X. What's the way out?
MontyPython
  • 2,906
  • 11
  • 37
  • 58
1
vote
1 answer

Oracle. How to format a table containing description coulmns into spool output?

The first table does not have any long strings and the output is well formatted: SQL> select * from Visit; SLOTNUM DATEVISIT ACTUALARR ----------…
Buras
  • 3,069
  • 28
  • 79
  • 126
1 2 3
…
12 13