OraFAQ Forum: JDeveloper, Java & XML » insertion of xml data to clob/xmltpe

Home » Developer & Programmer » JDeveloper, Java & XML » insertion of xml data to clob/xmltpe (oracle11R2,Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

insertion of xml data to clob/xmltpe [message #654955]

Wed, 17 August 2016 07:28

prejib
Messages: 126
Registered: March 2009
Location: India

Senior Member

Hi,

We have a table with the below structure .

SQL> desc example1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 KEY_COLUMN                                         VARCHAR2(10)
 XML_COLUMN                                         PUBLIC.XMLTYPE

while trying to insert data to the table having length(XML_COLUMN) <=4000 is allowed through the insert script and is successfull. But when I tried the insert data with length(XML_COLUMN) > 4000 , the script is failing with the below error . The same thing is happening for 'clob' column also . scripts are given in the attached file . I tried the same by running the .sql file also .

SQL> @TEST
'<?xml version="1.0"?>
*
ERROR at line 5:
ORA-01704: string literal too long

But when I tried to load data from the .xml file , I am able to do that by putting in a directory . But I want to do this activity in insert script only . Can you any one please help me .
Thanks
Prejib

Attachment: test.sql
(Size: 315.88KB, Downloaded 2993 times)

Report message to a moderator

Re: insertion of xml data to clob/xmltpe [message #654957 is a reply to message #654955]

Wed, 17 August 2016 07:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL strings are limited to 4000 bytes.
From where exactly are you obtaining the XML data that you want to reside in EXAMPLE1 tables?

Report message to a moderator

Re: insertion of xml data to clob/xmltpe [message #654959 is a reply to message #654957]

Wed, 17 August 2016 08:13

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Delimited strings in an insert limited to 4000 characters. You will need to load the large XML from a flat file using sqlloader or setting up an external file. Good luck.

Report message to a moderator

Re: insertion of xml data to clob/xmltpe [message #654975 is a reply to message #654959]

Wed, 17 August 2016 22:11

prejib
Messages: 126
Registered: March 2009
Location: India

Senior Member

Thanks for the quick reply .

We are passing the value as a variable from tibco application as an insert script to the log table. 100s of .xml files are getting generated in tibco application server (not in DB server) and we needs to keep the data of each file in log table for future reference as a lengthy string or xml format . The loading is happening in daily basis. It is not mandatory to keep in xml format itself . we can keep as 2-3 lines of lengthy string also .

Thanks
Prejib

Report message to a moderator

Re: insertion of xml data to clob/xmltpe [message #654981 is a reply to message #654975]

Thu, 18 August 2016 00:17

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

We are passing the value as a variable from tibco application as an insert script to the log table.

If you have the value in a variable then just use something like:
INSERT INTO tab VALUES (:variable);

[Updated on: Thu, 18 August 2016 00:18]

Report message to a moderator

Re: insertion of xml data to clob/xmltpe [message #654994 is a reply to message #654981]

Thu, 18 August 2016 06:13

prejib
Messages: 126
Registered: March 2009
Location: India

Senior Member

Thanks a lot for the solutions . I will try

Thanks
Prejib

Report message to a moderator

Previous Topic:	Dynamic splitting of column
Next Topic:	JDeveloper ADF Clear Form Fields

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 18 07:31:46 CDT 2025