sql - 使用 PL/SQL 将 JSON 解析为 Oracle 表

Question

我创建了以下脚本，以便从 Oracle SQL Developer 的 Mobile App DB(基于 MongoDB)读取数据:

宣布
l_param_list VARCHAR2(512);

l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;

l_response_text VARCHAR2(32767);
开始

-- 服务的输入参数

-- 准备请求...
l_http_request := UTL_HTTP.begin_request ('https://api.appery.io/rest/1/db/collections/Photos?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D'
， '得到'
, 'HTTP/1.1');

-- ...设置标题的属性
UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe');
--UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list));

-- ...设置输入参数
-- UTL_HTTP.write_text(l_http_request, l_param_list);

-- 获取响应并获取接收值
l_http_response := UTL_HTTP.get_response(l_http_request);

UTL_HTTP.read_text(l_http_response, l_response_text);

DBMS_OUTPUT.put_line(l_response_text);
插入 appery 值(l_response_text)；
——定稿
UTL_HTTP.end_response(l_http_response);

异常(exception)
当 UTL_HTTP.end_of_body 时
然后 UTL_HTTP.end_response(l_http_response);
结尾;
/

响应 (l_response_text) 是一个类似 JSON 的字符串。例如:

[{"Postcode":"47100","OutletCode":"128039251","MobileNumber":"0123071303","_createdAt":"2014-11-10 06:12:49.837","_updatedAt":"2014- 11-10 06:12:49.837"}, {"Postcode":"32100","OutletCode":"118034251", ..... ]

代码运行良好，并将响应插入到名为 appery 的列表中。但是，我需要解析此响应，以便每个数组都进入名为 appery_test 的表中的特定列。表 appery_test 的列数与 JSON 对的数量相同且顺序相同。

我搜索并发现了大部分关于将 Oracle 表解析为 JSON 而不是相反的结果。不过我发现，this链接有点类似于我的问题。但是，答案中建议的库没有关于如何使用它使用 PL/SQL 将 JSON 插入到常规表中的任何示例。

注意:我使用的是 11g 而不是 12c。所以built in功能对我不可用。

Answer 1

由于这个问题的结果得分很高，我想发布这个首选的替代方案:

Oracle 已发布 APEX 5.0 (2015 年 4 月 15 日)。有了它，你就可以访问一个很棒的 AP​​I 来处理 JSON

我在 11.2 上使用它，并且能够处理每个单独的 json，从简单到非常复杂的具有多个数组和 4/5 级别的对象。 APEX_JSON

如果您不想使用 APEX。只需安装运行时环境即可访问 API。

示例用法，数据来自 json.org's example :

declare
    sample_json   varchar2 (32767)
        := '{
    "glossary": {
        "title": "example glossary",
        "GlossDiv": {
            "title": "S",
            "GlossList": {
                "GlossEntry": {
                    "ID": "SGML",
                    "SortAs": "SGML",
                    "GlossTerm": "Standard Generalized Markup Language",
                    "Acronym": "SGML",
                    "Abbrev": "ISO 8879:1986",
                    "GlossDef": {
                        "para": "A meta-markup language, used to create markup languages such as DocBook.",
                        "GlossSeeAlso": ["GML", "XML"]
                    },
                    "GlossSee": "markup"
                }
            }
        }
    }
}';
begin
    apex_json.parse (sample_json);
    dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.title'));
    dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.GlossList.GlossEntry.GlossTerm'));
    dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.GlossList.GlossEntry.GlossDef.GlossSeeAlso[%d]', 2));
end;

结果:
执行的 PL/SQL 块

S
Standard Generalized Markup Language
XML