How do I edit BLOBs (containing JSON) in Oracle SQL Developer?

Question

How do I edit BLOBs (containing JSON text) in Oracle SQL Developer?

I can open and view them, but do I need an external editor to edit them? Any help on what to use, even if just notepad, and how to go about it would be greatly appreciated in the answer.

EDIT: BLOBs in question contain JSON text.

Tull

Answer 1

If you run a query in SQL Developer 3.1 (and probably earlier releases) that returns a BLOB, you can double-click on the particular BLOB you're interested in where you'll be prompted either to try to send the data to an external editor or to try to have the built-in SQL Developer display control attempt to interpret the data as an image or as text. Your JSON data will probably display correctly if you choose the text option.

If you want to change the data, however, you're going to have to issue an UPDATE to actually set the data. SQL Developer doesn't have the functionality to directly edit the LOB data. For example

UPDATE table_name
   SET column_with_json_data = 
          utl_i18n.string_to_raw( '{"foo": {"id": "1", "value": "2"}}' )
 WHERE primary_key = <<some value>>

will update the specified row with the new JSON data encoded using the database character set. If you want to store the data in some other character set, string_to_raw takes an optional second parameter that specifies the character set. So if you want to store the data using the UTF-8 character set, you'd do something like this

UPDATE table_name
   SET column_with_json_data = 
          utl_i18n.string_to_raw( '{"foo": {"id": "1", "value": "2"}}', 'AL32UTF8' )
 WHERE primary_key = <<some value>>

Of course, since JSON data is textual, you'd be far better off storing the data in a CLOB which is designed to store character large objects. Then SQL Developer (and other tools) could just display the text rather than requiring you to select the result and then take additional actions to convert it to text. And you wouldn't have to convert the data to RAW in order to update the data in the database.

If the data is too long for string_to_raw to handle (which depends on the character set and the data but will occur any time the RAW data exceeds 2000 bytes), you can store the data in a CLOB and then convert that into a BLOB that you use to update the table. That's a bit more complex but it is more flexible. In this example, I'm padding the JSON data out to 3200 characters with a '*'-- obviously the test data is no longer valid JSON but that's not important for purposes of this question.

declare
  l_blob        blob;
  l_clob        clob := rpad('{"foo": {"id": "1", "value": "2", "name": "bob"}}',3200,'*');
  l_amt         integer := dbms_lob.lobmaxsize;
  l_dest_offset integer := 1;
  l_src_offset  integer := 1;
  l_csid        integer := dbms_lob.default_csid;
  l_ctx         integer := dbms_lob.default_lang_ctx;
  l_warn        integer;
begin
  dbms_lob.createTemporary( l_blob, false );
  dbms_lob.convertToBlob( l_blob,
                          l_clob,
                          l_amt,
                          l_dest_offset,
                          l_src_offset,
                          l_csid,
                          l_ctx,
                          l_warn );

  -- You'll want to add a WHERE clause as well
  update json_data
     set data = l_blob;

  dbms_lob.freeTemporary( l_blob );
end;
/

Incognizant

Ok, I edited the BLOB and have the text I wish it was. How do I perform the UPDATE with this text file? Connect me here and I'll know how to do it all! – Tull

@MatthewDoucette - Updated my answer with an example of the UPDATE statement. – Incognizant

awesome. Let me try her out!! – Tull

it seems to work but I am now getting this error, if you can help me out with it: #9743584 It relates to this question if you wish to add it to your answer. Thanks sooo much. – Tull

this is odd as the text I am using is only 2,368 characters long, not breaking the 4,000 byte limit. Makes me wonder what is setting off the raw variable length too long error. Thanks so much for this solution however. – Tull

@JusticCave, why are you using rpad for (at all)? – Tull

I had to set the rpad length to the exact length of the text I was inserting, but it worked! – Tull

@MatthewDoucette - You shouldn't need to use RPAD at all. I was just using it to create a 3200 character string since I was too lazy to type a valid 3200 character long hunk of JSON. – Incognizant

@MatthewDoucette - Actually, I was mistaken-- the maximum length of a RAW is 2000 bytes, not 4000. Updated my answer to reflect that. – Incognizant

createTemporary creations are dropped at the end of the script, I assume? It's so obvious, but I just wanted to double check. – Tull

@MatthewDoucette - They are freed when the local variable goes out of scope. I added a call to dbms_lob.freeTemporary at the end of the script just for good code hygiene. – Incognizant

Answer 2

If you're looking to change individual blobs without having to write any SQL, you can do this in Oracle SQL Developer:

1. Double click on the cell that says (BLOB). An edit button (pencil for an icon) should appear to the right side of the cell. Click on it.
2. Click Download, to the right of Saved Data. Save it somewhere.
3. Edit the file in whatever program you want. Save.
4. Back in the Edit Value dialog of Oracle SQL Developer, click Load, to the right of Local Data. Find the file wherever you saved it after editing. Click OK in the Edit Value dialog now.
5. Click the Commit Changes button if you're satisfied with your changes, or the Rollback Changes button if you've changed your mind.

Much easier, I think, than doing the whole dbms_lob.convertToBlob if this is just a quick one-off thing.

Coliseum

Why sometimes the 'Load' link and whole 'Local Data' session is disabled? What is the criteria to enable those buttons? – Frenchman

@Frenchman Perhaps you're in a query results pane? Changing data is only possible when opening the table and going to the Data tab (though I don't understand this limitation.) See BLOB load unavailable (greyed out). – Sardella