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

Hibernate on Oracle: mapping String property to CLOB column

WARNING: see my own answer below. The problem is caused by old Oracle drivers that were present on the classpath in addition to 10.2.0.4. Problem solved. Leaving the rest of this question for posterity. I've been banging my head against the…
Max A.
  • 4,842
  • 6
  • 29
  • 27
13
votes
5 answers

Exporting a CLOB to a text file using Oracle SQL Developer

I am using Oracle SQL Developer and trying to export a table to a CSV file. Some of the fields are CLOB fields, and in many cases the entries are truncated when the export happens. I'm looking for a way to get the whole thing out, as my end goal…
geoffjentry
  • 4,674
  • 3
  • 31
  • 37
12
votes
3 answers

ORA-40478 when returning large json into clob in Oracle 19c

In Oracle 19c I created the table: create table SAMPLE_TABLE ( id NUMBER not null, display_name NVARCHAR2(4000), ) When I run the script: declare i integer; p_tmp_clob clob; begin select JSON_ARRAYAGG(JSON_OBJECT(t.* )) into…
Goxy
  • 151
  • 1
  • 1
  • 7
12
votes
6 answers

How to store unlimited characters in Oracle 11g?

We have a table in Oracle 11g with a varchar2 column. We use a proprietary programming language where this column is defined as string. Maximum we can store 2000 characters (4000 bytes) in this column. Now the requirement is such that the column…
user32262
  • 8,660
  • 21
  • 64
  • 77
12
votes
1 answer

How to change a dataype CLOB TO VARCHAR2(sql)

Table: customers ID NAME DATATYPE NUMBER VARCHAR2(100) CLOB I want to change the DATA column from CLOB to `VARCHAR2(1000) I have try ALTER TABLE customers MODIFY DATA VARCHAR2 (1000) also ALTER TABLE customers MODIFY (DATA…
PHPnoob
  • 293
  • 1
  • 3
  • 12
11
votes
4 answers

Explanation of a BLOB and a CLOB

I am looking for a real good explanation of a BLOB and CLOB data. I am looking for the great of that explains in plain English.
Randy
  • 773
  • 2
  • 7
  • 12
11
votes
5 answers

Oracle 10g small Blob or Clob not being stored inline?

According to the documents I've read, the default storage for a CLOB or BLOB is inline, which means that if it is less than approx 4k in size then it will be held in the table. But when I test this on a dummy table in Oracle (10.2.0.1.0) the…
David Carle
  • 131
  • 2
  • 6
11
votes
2 answers

Oracle: LONG or CLOB?

From these two threads, Why is LONG an issue with Oracle? Is it possible to read a CLOB from a remote Oracle database? LONG is archaic and deprecated. Oracle says, Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB) instead.…
Moeb
  • 10,527
  • 31
  • 84
  • 110
11
votes
3 answers

How to import a .dmp file (Oracle) into MySql DB?

The .dmp is a dump of a table built in Oracle 10g (Express Edition) and one of the fields is of CLOB type. I was trying to simply export the table to xml/csv files then import it to the MySql, but the export simply ignored the CLOB field... (I was…
ScienceFriction
  • 1,538
  • 2
  • 18
  • 29
11
votes
1 answer

java.sql.SQLException: operation not allowed: streams type cannot be used in batching while inserting data into Oracle clob data type

I'm using Hibernate Tools 3.2.1.GA with Spring version 3.0.2. I'm tying to insert data into Oracle (10g) database field of type clob as follows. Clob…
Tiny
  • 27,221
  • 105
  • 339
  • 599
11
votes
2 answers

extract a substring from clob in oracle

I have a clob with data function change_case() { alert("here..."); …
micheal marquiz
  • 321
  • 2
  • 5
  • 18
10
votes
4 answers

How to convert BLOB to CLOB?

I'm using Oracle 11g and I'm trying to find out the length of a text. I normally use select length(myvar) from table, but I can't do that. The table which I want to query has a BLOB column that saves characters or photos. I want to know the number…
user1739469
  • 101
  • 1
  • 1
  • 4
10
votes
1 answer

String to CLOB with postgreSQL

I'm trying to read a clob from postgreSQL DB, change it, and write it back. I was able to read the clob successfully using the following code: PreparedStatement statement = connection.prepareStatement("SELECT clob_column from data where id =…
Ben Bracha
  • 1,377
  • 2
  • 15
  • 28
9
votes
5 answers

"ORA-01036: illegal variable name/number\n" for oracle clob in C#

When I try to create an oracle stored procedure call with clob input and output in C#, I get the following error: ORA-01036: illegal variable name/number\n Here's the code itself: OracleTransaction transaction =…
user421719
  • 237
  • 2
  • 7
  • 20
9
votes
2 answers

replacing characters in a CLOB column (db2)

I have a CLOB(2000000) field in a db2 (v10) database, and I would like to run a simple UPDATE query on it to replace each occurances of "foo" to "baaz". Since the contents of the field is more then 32k, I get the following error: "{some char data…
krisy
  • 1,508
  • 1
  • 14
  • 32
1 2
3
…
71 72