如何在 Oracle SQL Developer 中编辑 BLOB（包含 JSON）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9218474/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I edit BLOBs (containing JSON) in Oracle SQL Developer?
提问by Xonatron
How do I edit BLOBs (containing JSON text) in Oracle SQL Developer?
如何在 Oracle SQL Developer 中编辑 BLOB（包含 JSON 文本）？
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.
编辑：有问题的 BLOB 包含 JSON 文本。
采纳答案by Justin Cave
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.
如果您在 SQL Developer 3.1（可能还有更早的版本）中运行返回 BLOB 的查询，您可以双击您感兴趣的特定 BLOB，系统会提示您尝试将数据发送到外部编辑器或尝试使用内置的 SQL Developer 显示控件尝试将数据解释为图像或文本。如果您选择文本选项，您的 JSON 数据可能会正确显示。
If you want to change the data, however, you're going to have to issue an UPDATEto actually set the data. SQL Developer doesn't have the functionality to directly edit the LOB data. For example
但是，如果要更改数据，则必须发出 anUPDATE来实际设置数据。SQL Developer 没有直接编辑 LOB 数据的功能。例如
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_rawtakes 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
将使用使用数据库字符集编码的新 JSON 数据更新指定的行。如果您想将数据存储在其他字符集中，则string_to_raw需要一个可选的第二个参数来指定字符集。所以如果你想使用 UTF-8 字符集存储数据，你会做这样的事情
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 RAWin order to update the data in the database.
当然，由于 JSON 数据是文本数据，因此最好将数据存储在旨在存储字符大对象的 CLOB 中。然后 SQL Developer（和其他工具）可以只显示文本，而不需要您选择结果，然后采取其他操作将其转换为文本。而且您不必RAW为了更新数据库中的数据而将数据转换为。
If the data is too long for string_to_rawto handle (which depends on the character set and the data but will occur any time the RAWdata exceeds 2000 bytes), you can store the data in a CLOBand then convert that into a BLOBthat 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.
如果数据太长而string_to_raw无法处理（这取决于字符集和数据，但只要RAW数据超过 2000 字节就会发生），您可以将数据存储在 a 中CLOB，然后将其转换为BLOB用于更新桌子。这有点复杂，但它更灵活。在这个例子中，我用 '*' 将 JSON 数据填充到 3200 个字符——显然测试数据不再是有效的 JSON，但这对于这个问题来说并不重要。
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;
/
回答by ArtOfWarfare
If you're looking to change individual blobs without having to write any SQL, you can do this in Oracle SQL Developer:
如果您希望在无需编写任何 SQL 的情况下更改单个 blob，您可以在 Oracle SQL Developer 中执行此操作：
- 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. - Click
Download, to the right of Saved Data. Save it somewhere. - Edit the file in whatever program you want. Save.
- 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. ClickOKin the Edit Value dialog now. - Click the
Commit Changesbutton if you're satisfied with your changes, or theRollback Changesbutton if you've changed your mind.
- 双击显示 的单元格
(BLOB)。编辑按钮（图标的铅笔）应该出现在单元格的右侧。点击它。 - 单击
Download“已保存数据”右侧的。把它保存在某个地方。 - 在您想要的任何程序中编辑该文件。节省。
- 返回 Oracle SQL Developer 的 Edit Value 对话框，单击Local Data
Load右侧的。编辑后在您保存文件的任何位置找到该文件。现在单击“编辑值”对话框。OK Commit Changes如果您对更改感到满意，请单击该按钮，Rollback Changes如果您改变了主意，请单击该按钮。
Much easier, I think, than doing the whole dbms_lob.convertToBlobif this is just a quick one-off thing.
我认为，dbms_lob.convertToBlob如果这只是一件快速的一次性事情，那么比做整个事情要容易得多。
