Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle 10g XE and CLOB data type

547912Nov 23 2006 — edited Sep 3 2009
I am using Oracle 10.2.0.1.0 Express Edition and I wish to store large amounts of data in a CLOB column. I have created my table using a "standard" SQL statement similar to the following:

CREATE TABLE LotsofData(
id INT NOT NULL,
data CLOB);

The table is created successfully, so far so good.

And I used a "standard" insert SQL statement to insert large amount of data into the before mentioned table, similar to the follwing:

INSERT INTO LotsofData VALUES('4043', 'The quick brown fox jumps over the lazy dog.The quick brown fox jumps over the lazy dog <insert more iterations>');

The data being inserted takes up 4043 bytes. Now when attempting, using among other programs the handy web interface, to execute the insert statement I get this error:

Error starting at line 1 in command:
INSERT INTO LotsofData VALUES('4043', 'The quick brown fox jumps over the lazy dog.The quick brown fox jumps over the lazy dog <insert more iterations>');
Error at Command Line:1 Column:38
Error report:
SQL Error: ORA-01704: string literal too long

Now if I reduce the size of the data to be inserted to 4000 bytes or below there is no problem. I have performed searches on google groups, google search engine, the oracle site, this forum but not found a way to solve it... I have found documentation stating that there is support for 4gb of user data, but well that doesn't really help.
I have been on the trail of TABLESPACE and working with that, but with no luck. The data type BFILE will not work as I need to be able to change the data after it has been inserted into the table.
Is the 4000 bytes a limitation in the Express Edition and I should just forget it, or is there a way I configure the database to accept large amounts of data. Should I be creating the table using a different SQL statement?

kind regards
Chris Nilsson
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 1 2009
Added on Nov 23 2006
4 comments
2,715 views