Home » Developer & Programmer » JDeveloper, Java & XML » insertion of xml data to clob/xmltpe (oracle11R2,Linux)
insertion of xml data to clob/xmltpe [message #654955] Wed, 17 August 2016 07:28 Go to next message
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 2372 times)
Re: insertion of xml data to clob/xmltpe [message #654957 is a reply to message #654955] Wed, 17 August 2016 07:44 Go to previous messageGo to next message
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?
Re: insertion of xml data to clob/xmltpe [message #654959 is a reply to message #654957] Wed, 17 August 2016 08:13 Go to previous messageGo to next message
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.

Re: insertion of xml data to clob/xmltpe [message #654975 is a reply to message #654959] Wed, 17 August 2016 22:11 Go to previous messageGo to next message
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

Re: insertion of xml data to clob/xmltpe [message #654981 is a reply to message #654975] Thu, 18 August 2016 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Thanks a lot for the solutions . I will try

Thanks
Prejib
Previous Topic: Dynamic splitting of column
Next Topic: JDeveloper ADF Clear Form Fields
Goto Forum:
  


Current Time: Thu Mar 28 12:24:56 CDT 2024