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.

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;
/

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.