Stack Overflow
Stack Overflow

Questions tagged [clob]

Character Large Object - SQL data type used to store character strings longer than the regular CHAR and VARCHAR types allow.

Character Large Object - SQL data type intended to store character strings longer than the regular CHAR and VARCHAR types handle.

See also NCLOB data type which is recommended for storing National Character strings/Unicode.

Oracle: CLOBs are stored externally and are referenced from table, so using them involves performance hit in comparison to using VARCHAR2. In exchange they have very high size limits, depending on exact version of RDBMS but not smaller than 2 GB.

Main difference between CLOB and BLOB data type is that CLOB has defined character encoding and to limited extent can be compared to strings, indexed and searched.

1067 questions
118
votes
3 answers

Difference between CLOB and BLOB from DB2 and Oracle Perspective?

I have been pretty much fascinated by these two data types. According to Oracle Docs, they are presented as follows : BLOB : Variable-length binary large object string that can be up to 2GB (2,147,483,647) long. Primarily intended to hold…
The Dark Knight
  • 5,455
  • 11
  • 54
  • 95
91
votes
18 answers

Disabling contextual LOB creation as createClob() method threw error

I am using Hibernate 3.5.6 with Oracle 10g. I am seeing the below exception during initialization but the application itself is working fine. What is the cause for this exception? and how it can be corrected? Exception Disabling contextual LOB…
GiriByaks
  • 941
  • 1
  • 7
  • 6
86
votes
8 answers

ORA-00932: inconsistent datatypes: expected - got CLOB

Considering that TEST_SCRIPT is a CLOB why when I run this simple query from SQL*PLUS on Oracle, I get the error: ORA-00932: inconsistent datatypes: expected - got CLOB I have been reading a lot of questions about the same error but none of those…
user1298925
  • 2,304
  • 7
  • 29
  • 43
60
votes
8 answers

How to query a CLOB column in Oracle

I'm trying to run a query that has a few columns that are a CLOB datatype. If i run the query like normal, all of those fields just have (CLOB) as the value. I tried using DBMS_LOB.substr(column) and i get the error ORA-06502: PL/SQL: numeric or…
Catfish
  • 18,876
  • 54
  • 209
  • 353
58
votes
12 answers

Most efficient solution for reading CLOB to String, and String to CLOB in Java?

I have a big CLOB (more than 32kB) that I want to read to a String, using StringBuilder. How do I do this in the most efficient way? I can not use the "int length" constructor for StringBuilder since the lenght of my CLOB is longer than a "int" and…
Jonas
  • 121,568
  • 97
  • 310
  • 388
48
votes
8 answers

Error : ORA-01704: string literal too long

While I try to set the value of over 4000 characters on a field that has data type CLOB, it gives me this error : ORA-01704: string literal too long. Any suggestion, which data type would be applicable for me if I have to set value of unlimited…
hsuk
  • 6,770
  • 13
  • 50
  • 80
37
votes
7 answers

How to get size in bytes of a CLOB column in Oracle?

How do I get the size in bytes of a CLOB column in Oracle? LENGTH() and DBMS_LOB.getLength() both return number of characters used in the CLOB but I need to know how many bytes are used (I'm dealing with multibyte charactersets).
rag
  • 971
  • 1
  • 10
  • 15
32
votes
9 answers

Overcomplicated oracle jdbc BLOB handling

When I search the web for inserting BLOBs into Oracle database with jdbc thin driver, most of the webpages suggest a 3-step approach: insert empty_blob() value. select the row with for update. insert the real value. This works fine for me, here is…
asalamon74
  • 6,120
  • 9
  • 46
  • 60
32
votes
6 answers

Search for a particular string in Oracle clob column

How to get a particular string from a clob column? I have data as below which is stored in clob column called product_details CALCULATION=[N]NEW.PRODUCT_NO=[T9856] OLD.PRODUCT_NO=[T9852].... -- with other text I would like to search for string…
Jacob
  • 14,463
  • 65
  • 207
  • 320
29
votes
1 answer

PostgreSQL Clob datatype

PostgreSQL supports both clob and text data types for storing large texts. I've used clob data type as we are migrating the DB from Oracle. I understand that the clob data type in PostgreSQL can only store up to 1GB of text rather than the 4GB in…
Tino M Thomas
  • 1,676
  • 2
  • 16
  • 24
29
votes
2 answers

How do I display the full content of LOB column in Oracle SQL*Plus?

When I try to display the contents of a LOB (large object) column in SQL*Plus, it is truncated. How do I display the whole thing?
Anonymoose
  • 5,662
  • 4
  • 33
  • 41
26
votes
2 answers

How to create a Clob in JPA in an implementation agnostic way

I am using Ejb3 and JPA (based on Hibernate and Oracle 10g at the moment) I have an entity that contains a clob @Entity @Table(name = "My_TAB") public class ExampleEntity implements java.io.Serializable { private Clob someText; public void…
kazanaki
  • 7,988
  • 8
  • 52
  • 79
25
votes
4 answers

Performance of SUBSTR on CLOB

I have a PL/SQL procedure that does a lot of SUBSTRs on a VARCHAR2 parameter. I would like to remove the length limit, so I tried to change it to CLOB. Works fine, but performance suffers, so I did some tests (based on these tests from…
Peter Lang
  • 54,264
  • 27
  • 148
  • 161
24
votes
6 answers

How to export clob field datas in oracle sql developer

How to export clob field data's in oracle sql developer. Currently clob field data's can't export in oracle sql developer.
Prashobh Chandran
  • 301
  • 1
  • 3
  • 9
21
votes
4 answers

Issues calling stored procedure from C# with large CLOB

I'm not the first to have these issues, and will list some reference posts below, but am still looking for a proper solution. I need to call a stored procedure (Oracle 10g database) from a C# web service. The web server has an Oracle 9i client…
chiccodoro
  • 14,407
  • 19
  • 87
  • 130
1
2 3
…
71 72