Not able to insert an XML in a table

Question

I have an XML that has to be inserted in production DB. A row got accidentally deleted due to which a batch job has failed to run. So, I need to insert an XML. I have this XML in test region schema.

I tried to convert XML from varchar2 to clob, but was not possible. I was then told to convert XML from varchar2 to XMLType and then to CLOB.

I tried this -

CREATE or replace procedure insert_xml_data 
 IS
   str varchar2(32767);
BEGIN
  str :='<huge xml data>';
  INSERT INTO TABLE VALUES (ID, XMLType(str));
END;
/

But, I get an error that says -

Error: ORA-06550: line 2, column 23940: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the >following:

* & = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset

SQLState: 65000

ErrorCode: 6550

Error occured in:

BEGIN

Can anyone please help me here? OR a better way to accomplish this?

XML finally has be to in CLOB type.

Looks like maybe your client can't handle the long string, rather than it being specifically an XML issue, from the column number. Where and how are you doing this - which client, for example? How long is the entire string? Can you run it without the insert statement? Why couldn't you create a CLOB directly (and not relevant, but why are you storing XML data as CLOB rather than XMLType)? — Alex Poole, Apr 07 '15 at 16:08
Am using squirrel sql. Even if I comment the insert statement I still get the same error. So, assigning the value to "str" is the main problem. Mostly, we have defined the column type as clob to store an xml. That's old and existing one. I have defined str varchar2(32767); and trying to assign my xml data to str variable. So, it should not have a problem. Definitely my xml data is less than 32767. — Chinmay, Apr 07 '15 at 16:12
Maybe you have [this issue](http://sourceforge.net/p/squirrel-sql/mailman/message/2956442/) then? I don't have that client to test with, but I get the same error if I artificially run everything up to (but not including) the second semicolon, so it sounds plausible. Use a different client if you can; SQL\*Plus or SQL Developer (or any other client I know of) will handle your code OK. — Alex Poole, Apr 07 '15 at 16:30
I made it work! Instead of assigning it to varchar2, i created new clob variable and assigned it the actual xml. There was no need to do to_clob or something. CREATE or replace procedure insert_xml_data IS xmlData CLOB;BEGIN xmlData := ''; INSERT INTO table VALUES(id, xmlData); END; / Thanks for your inputs. I made use of sql developer. — Chinmay, Apr 07 '15 at 18:44

Not able to insert an XML in a table

0 Answers0