0

I would like to edit XML column which is displaying as (XMLTYPE) by SQL Developer editor (I go there by clicking on the field twice, edit, then save).

After that the displayed value changes to sqldev.xml:/home/myuser/.sqldeveloper/tmp/XMLType8226206531089284015.xml

Build after save retrieving next build context...

Build after save building project 1 of 1 queued projects

Compiling...
  Ignoring /home/username/.sqldeveloper/tmp/XMLType5691884284875805681.xml; not on source path
[11:45:33 AM] Compilation complete: 0 errors, 1 warnings.

Build after save finished

and when I try to commit:

UPDATE "USERNAME"."TABLENAME" SET  WHERE ROWID = 'AABWNKAAEAAABSbAAB' AND ORA_ROWSCN = '6951979'

One error saving changes to table "USERNAME"."TABLENAME":
Row 1: Illegal format in column NEXTCOLUMN.

I tried to look for this error and found people who also had it, but without the solution. If you have an advice how to report it to Oracle, it will be also helpful.

Improve this question
6
  • Hi, Your update statement is not full , set what to what ? Commented Jan 29, 2020 at 11:32
  • @VBoka That's what SQL Developer shows in messages log. I don't understand why there is no column name/value (XML) which I'm trying to update - probably because of the previous warning. Commented Jan 29, 2020 at 11:36
  • 1
    Ok, it would be great if you can give us samle data : what you try to change to and from... Commented Jan 29, 2020 at 11:38
  • <?xml version="1.0" encoding="UTF-8"?> <sampleTag1> <sampleTag2> value1 </sampleTag2> </sampleTag1> I'm trying to change value1 to value2. Commented Jan 29, 2020 at 11:50
  • Please tag a version of your oracle database ? Thanks! Commented Jan 29, 2020 at 12:17

1 Answer 1

Reset to default
2

Hope this will be of help to you:

UPDATE table_name 
SET table_column=
   UPDATEXML(table_column,
   '/sampleTag1/sampleTag2/text()','value2')
   WHERE some_column = some value --<< this part is where you put your condition

Here is where you can find more about it: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions205.htm

-------------------------

If your problem is with editing through SQL developer manually via integrated editor then it is, as far as my testing and researching can tell, because of the SQL Developer version.

You have noted in your comment that you use version 4.1.x and I have found few places where people confirm that they had the same problem with this version.

I also have 4.1.x version and I have also successfully repeated your error where the developer is referring to my .xml file in my ...\sqldeveloper\tmp folder not being on it's source path :

Compiling... Ignoring C:\Users\trle\AppData\Roaming\SQL Developer\tmp\XMLType6413036461637067751.xml; not on source path [4:33:29 PM] Compilation complete: 0 errors, 1 warnings.

I have then downloaded version 19.2.x where there is no such problem and all works just fine.

So my answer to your problem is to download some newer version of SQL developer. In my case 19.2. works.

-------------------------

UPDATE Just tested on version 4.2.x - also works

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Hi @Line , did you try to execute this query? Do you have questions?
Yes, it works. I don't have questions, yet this is not the answer to my problem. Rather workaround.
Hi @Line can you please tell me which version of SQL Developer do you use ? Thanks!
I'm using 4.1.3.20.
Hi @Line I have updated my answer. Hope this will help you. Cheers!
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.