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

Extracting substring from BLOB converted in VARCHAR2

I have this flexible search in Oracle: select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(p_targetCarts, 4000,1)) from {OrderProposal} and the result is this: �srjava.util.ArrayListx��a�Isizexpwt 0000031072x �srjava.util.ArrayListx��a�Isizexpwt…
sharkbait
  • 2,980
  • 16
  • 51
  • 89
0
votes
1 answer

mysql varchar and text doubts, reloaded ;)

I had a doubt with mysql. I have fields that I can easily restrict to some chars, like surname, and others like a mini log with an event particular that I think is no longer 2 than pages (or fewer characters too). I know it's a fairly common…
Rulolp
  • 21
  • 5
0
votes
1 answer

Getting ORA-12899: Value too large error for nvarchar2 and varchar2 in Oracle

I have created 2 tables and inserting data as below--- SQL> create table t (aa nvarchar2(10)); Table created. SQL> create table t1 (aa varchar2(10)); Table created. Now, inserting some french characters like below-- SQL> insert into t values…
Ashish Patil
  • 4,428
  • 1
  • 15
  • 36
0
votes
2 answers

ms Oracle: Avoid blank-padding in Embedded SQL

I am currently migrating a C-application from RdB (OpenVMS) to Oracle 11 (Linux). I now stumbled over a difference between the two database systems. Example: create table MYTAB(id number(13), name varchar2(10)) ; Contents Id Name 1 Smith 2 …
Jörg Mohren
  • 87
  • 5
0
votes
1 answer

Select too long blob data gives weird result

I am trying to run the below query in SQL developer which gives weird result in the output: select utl_raw.cast_to_varchar2(dbms_lob.substr(message,100)) from XML_MESSAGE; My problem is that the result shows the correct XML message for some rows…
fiddle
  • 1,095
  • 5
  • 18
  • 33
0
votes
1 answer

Oracle add new line character to varchar variable for utlmail message

I am creating the body of an email for utlmail and I want to create a string and replace tokens in the string. The first problem I am having is displaying new lines in the body of the email. Mesg VARCHAR2(4000) := q'[Item1:token1 /n/n…
Jason Smith
  • 373
  • 2
  • 6
  • 20
0
votes
1 answer

How to select a constraint_name from user_constraints with its SEARCH_CONDITION?

query SELECT * FROM DBA_constraints WHERE constraint_type= 'C' AND search_condition = 'SEARCH_CONDITION' and table_name = 'TABLE-NAME'; error message ORA-00997: illegal use of LONG datatype 00997. 00000 - "illegal use of LONG datatype" …
Deepesh kumar Gupta
  • 884
  • 2
  • 11
  • 29
0
votes
1 answer

Extended length for varchar2 in Oracle 12c

I thought I should give the new extended varchar2 limits in my Java application. Here's what I've done so far: Changed MAX_STRING_SIZE to EXTENDED according to the recommended procedure. Extended the column in question to 32767 characters. Ran the…
Roald Andresen
  • 23
  • 4
0
votes
3 answers

mysql aggregate function for varchar column

I need to calculate the sum of one column(col2) , but the column has both numbers and text. How do I exclude the text alone before I use sum()? The table has around 1 million rows, so is there any way other than replacing the text first? My query…
New user
  • 3
  • 2
0
votes
3 answers

Characters spilled over multiple columns in Oracle 11g?

This is related to question: How to store unlimited characters in Oracle 11g? If maximum I need is 8000 characters, can I just add 3 more varchar2 columns so that I will have 4 columns with 2000 char each to get 8000 chars. So when the first column…
user32262
  • 8,660
  • 21
  • 64
  • 77
0
votes
2 answers

Column segregation using datatype as parameter

I want to know if there is a method in which we can select the columns of a table of a specific datatype in a table that has columns of various datatypes. Say a table has three columns - SSN, Name and phone number where SSN and Name are of Varchar2…
Vishnu
  • 3
  • 1
0
votes
2 answers

Oracle - generate unique random varchar values for each row of a table

I have to update a 'varchar2' column in a table with random values, but the catch is that the column is defined with 'unique' constraint, due to which I am getting the error saying that "Unique constraint violated". Can somebody help please. Thanks
Shaan44
  • 5
  • 1
  • 2
0
votes
2 answers

Check if number in varchar2 is greater than n

I've got a Varchar2 field which usually holds two alphabetic characters (such as ZH, SZ, AI,...). Let's call it FOO. Certain datasets save A or A1 - A9 into the same field. I need to select all rows except exactly those. I used the function substr…
Alex
  • 781
  • 10
  • 23
0
votes
2 answers

PL/SQL select multiple varchar2 into one varchar2

i'm a beginner at pl-sql and i am trying to write a code of a function to read in a coursename and display the lecturerName, coursename, and the title for which matches with the coursename. However i am unable to get a decent output disregarding the…
darren lim
  • 29
  • 4
0
votes
1 answer

Hibernate query.setString don't work

Now I using hql in query. The code is: return session.createQuery( "from Company com " + " where com.comId = " + ":comId and com.phoneNumber = :phoneNumber") …
mengya
  • 1
  • 1
1 2 3
…
12 13