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
2
votes
2 answers

Data type Varchar2 to date format

I am writing SQL code to extract data for effective start date greater than or equal to 24/09/2018 00:00:00. My effective_start_date data type is in varchar2. Below is the code I am using: TO_DATE(TRUNC(effective_start_date),'DD/MM/YY')…
Raj
  • 23
  • 5
2
votes
1 answer

Space character that persists in varchar2 in Oracle 11g for display in SSRS 2008

Is there a space or other non-printing character I can insert to a varchar2 column that will not be trimmed away and result in a NULL? I just want to insert a blank into the column so it displays nothing on an SSRS 2008 report. The column is part of…
ErikE
  • 48,881
  • 23
  • 151
  • 196
2
votes
2 answers

Binary sort on alphanumeric text not behaving as natural sort

For last couple of days I've been trying to sort a list of alphanumeric text in a natural order.I found that using the NLS_SORT option can order the list correctly (see this answer). But when trying out that solution I found that it made no…
Robin Hermans
  • 1,579
  • 1
  • 24
  • 52
2
votes
2 answers

Convert datatype Clob to Varchar2 Oracle

I have an Oracle table with a column of type clob. I want to preserve the column order and change the datatype to a varchar2. The column just contains text. update IN_MSG_BOARD set MSG_TEXT = null; alter table IN_MSG_BOARD modify MSG_TEXT…
brandonbanks
  • 1,125
  • 1
  • 14
  • 21
2
votes
3 answers

Varchar2 and Oracle quick question

Hi guys I'm using varchar2 for a product name field, but when I query the database from the run SQL command line it shows too many empty spaces, how can I fix this without changing the datatype here is the link to the…
anon
2
votes
1 answer

Ibator didn't generate Oracle varchar2 field

I have table APP_REQ_APPROVE_COMPARE with following fields: "ID" NUMBER NOT NULL ENABLE, "TRACK_NO" VARCHAR2(20 BYTE) NOT NULL ENABLE, "REQ_DATE" DATE NOT NULL ENABLE, "OFFCODE" CHAR(6 BYTE) NOT NULL ENABLE, …
wearetherock
  • 3,721
  • 8
  • 36
  • 47
2
votes
1 answer

OracleType.LongVarChar equivalent data type in ODP.NET

I am searching for System.Data.OracleClient OracleType.LongVarChar equivalent data type in ODP.NET Oracle.DataAccess.Client OracleDbType Example :- OracleType.Timestamp is equivalent to OracleDbType.TimeStamp. How do I do this?
Yogesh
  • 23
  • 4
2
votes
1 answer

Replacing varchar2 with bigger data type in oracle SP

I am using oracle verion 10. There is stored procedure in PL/SQL using varchar2 variable. The code is constantly appending the varchar2 variable. When the varchar2 variable size exceeds 32767, it cannot append any more value. Now I want to change…
user215005
  • 175
  • 1
  • 3
  • 10
1
vote
1 answer

Oracle JDB Thin Client - Unique index with varchar2 not used

First som basics. Java 6 OJDBC6 Oracle 10.2.0.4 (also the same result in 11g version) I am experiencing that a sql statement is behaving differently when executed from Java with the OJDBC6 client and using the tool SQL Gate that probably uses a…
jdsthlm
  • 51
  • 5
1
vote
1 answer

Oracle Character Types

Is using a VARCHAR2 (1 BYTE) any less efficient than using CHAR(1 BYTE)? Is using a VARCHAR2 (2000 BYTE) any less efficient than using CHAR(1 BYTE), if I never put any value longer than one character in the field? ** By efficient, I meant efficient…
Steven
  • 13,501
  • 27
  • 102
  • 146
1
vote
0 answers

How to insert unicode data into varchar2 oracle db column from code or from command line sqlplus command?

I am able to insert unicode data (mulitybye etc) into varchar2 oracle db column from Oracle SQL developer and its shown correctly. But when the same query is put in sql file and run from command prompt using sqlplus command ,then the inserted…
parshu dhole
  • 11
  • 1
1
vote
1 answer

VARCHAR2 column formatting in spool file

I'm working on an assignment and the spool output is getting some weird formatting for VARCHAR2 function returns. The class uses Oracle SQL 11g. I have this PL/SQL script: SET echo on SET wrap off SET trimspool on SET linesize 80 SET colsep '|' SET…
Gethe
  • 23
  • 1
  • 5
1
vote
1 answer

How to convert CLOB to VARCHAR2 in Oracle

I am trying to transform a data that has been saved in a table as CLOB to varchar2. When the query is executed the value that is returned is still a CLOB. What can I be doing wrong when it comes to building the query? I tried select l.user_id,…
Bufank85
  • 65
  • 1
  • 2
  • 10
1
vote
1 answer

SQL Oracle - problem with converting varchar2 to number

I have problem with converting varchar2 to number, I have a column with such a data, which is a varchar2 (below, few records from this column) POINT(-122.387539744377 37.7604575554348) POINT(-122.400868982077 37.7985721084626) POINT(-122.3904285…
Leafer
  • 15
  • 5
1
vote
2 answers

ORA-01722: invalid number rows start with comma transfer VARCHAR2 TO_NUMBER

I have following source data in VARCHAR2 format ,00100000004749745 ,100000001490116 ,125 ,200000002980232 ,25 ,439999997615814 ,5 0 1 1,10000002384186 1,5 100 2,1800000667572 3 3,29999995231628 96 999 What is the…
Daniel Horvath
  • 350
  • 1
  • 4
  • 13
1 2 3
…
12 13