如何在Oracle SQL Developer中编辑BLOB(包含JSON)? [英] How do I edit BLOBs (containing JSON) in Oracle SQL Developer?

问题描述

如何在Oracle SQL Developer中编辑BLOB(包含JSON文本)?

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.

有问题的BLOB包含JSON文本.

推荐答案

如果您在SQL Developer 3.1(可能是早期版本)中运行了返回BLOB的查询，则可以双击感兴趣的特定BLOB在这种情况下，系统将提示您尝试将数据发送到外部编辑器，或者尝试使内置的SQL Developer显示控件尝试将数据解释为图像或文本.如果选择text选项，则您的JSON数据可能会正确显示.

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.

但是，如果要更改数据，则必须发出UPDATE才能实际设置数据. SQL Developer没有直接编辑LOB数据的功能.例如

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

将使用使用数据库字符集编码的新JSON数据更新指定的行.如果要将数据存储在其他字符集中，则string_to_raw采用可选的第二个参数来指定字符集.因此，如果您想使用UTF-8字符集存储数据，则可以执行类似的操作

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

当然，由于JSON数据是文本数据，所以最好将数据存储在旨在存储字符大对象的CLOB中.然后，SQL Developer(和其他工具)可以只显示文本，而不需要您选择结果，然后采取其他措施将其转换为文本.而且，您不必将数据转换为RAW即可更新数据库中的数据.

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.

如果数据太长而无法处理string_to_raw(这取决于字符集和数据，但是在RAW数据超过2000字节时会出现)，则可以将数据存储在CLOB中然后将其转换为用于更新表的BLOB.有点复杂，但更灵活.在此示例中，我将JSON数据填充为3200个字符，并带有'*'-显然，测试数据不再是有效的JSON，但这对于此问题而言并不重要.

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

这篇关于如何在Oracle SQL Developer中编辑BLOB(包含JSON)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！