Skip to Main Content
  • Questions

  • ERROR:- [Oracle]ORA-01704: string literal too long

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, bhagyashree.

Asked: September 29, 2015 - 10:56 am UTC

Last updated: September 29, 2015 - 1:05 pm UTC

Version: oracle SQL developer Version 3.2.20.09

Viewed 10K+ times! This question is

You Asked

Hi team,
I am trying to inserting data in a CLOB type variable which has more than 4000 char,Following are the columns in the table (REGRESSION_TEST_MASTER_TABLE):

TEST_CASE_NO VARCHAR2(50 BYTE)
TSO_LABEL VARCHAR2(255 BYTE)
XML_IN CLOB
OUTPUT_STATUS VARCHAR2(50 BYTE)

I am inserting the data in this table using the query as:

Insert into REGRESSION_TEST_MASTER_TABLE (TEST_CASE_NO, TSO_LABEL, XML_IN, OUTPUT_STATUS) values ('CASE6-CR27979','CHANGETARIFF', 'DATA MORE THAN 4000 CHAR', 'D')

I am getting the error as:
">java.sql.SQLException: [tibcosoftwareinc][Oracle JDBC Driver][Oracle]ORA-01704: string literal too long<"


I also tried the query as:

Insert into REGRESSION_TEST_MASTER_TABLE (TEST_CASE_NO,TSO_LABEL,XML_IN,OUTPUT_STATUS) values ('CASE6-CR27979','CHANGETARIFF', 'DATA 4000 char'||'DATA 4000 char'||'DATA 4000 char', 'D')

Still it giving me the error message as:
Error report:
"SQL Error: ORA-01489: result of string concatenation is too long"


Please help me on this. I want to use the same SQL developer and same version to perform this action.
Kindly help.

Thank you.

and Connor said...

To insert a clob, you want a *locator* to the clob, for example:

insert into T (clob_col)
values ( empty_clob() )
returing clob_col into :c

dbms_lob.writeappend(:c, amt, buffer);

Take a look at this previous question for some demos in SQL, PLSQL and links to some JDBC demos

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:388196216305


We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here