oracle大文件存储,将大型JSON文件存储到Oracle DB中

我正在使用以下脚本从MongoDB中读取数据作为

JSON文件.

DECLARE

l_param_list VARCHAR2(512);

l_http_request UTL_HTTP.req;

l_http_response UTL_HTTP.resp;

l_response_text CLOB;

--l_response_text VARCHAR2(32767);

l_list json_list;

A_id VARCHAR2(100);

Photo VARCHAR2(32767);

A_Name VARCHAR2(100);

Remarks VARCHAR2(100);

Status VARCHAR2(100);

UserId VARCHAR2(100);

A_Date VARCHAR2(100);

A_Time VARCHAR2(100);

MSG_status VARCHAR2(100);

Oracle_Flag VARCHAR2(100);

acl VARCHAR2(100);

BEGIN

-- service's input parameters

-- preparing Request...

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'

, 'GET'

, 'HTTP/1.1');

-- ...set header's attributes

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

-- ...set input parameters

-- UTL_HTTP.write_text(l_http_request, l_param_list);

-- get Response and obtain received value

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

l_list := json_list(l_response_text);

FOR i IN 1..l_list.count

LOOP

A_id := json_ext.get_string(json(l_list.get(i)),'_id');

Photo := json_ext.get_string(json(l_list.get(i)),'Photo');

A_Name := json_ext.get_string(json(l_list.get(i)),'Name');

Remarks := json_ext.get_string(json(l_list.get(i)),'Remarks');

Status := json_ext.get_string(json(l_list.get(i)),'Status');

UserId := json_ext.get_string(json(l_list.get(i)),'UserId');

A_Date := json_ext.get_string(json(l_list.get(i)),'Date');

A_Time := json_ext.get_string(json(l_list.get(i)),'Time');

MSG_status := json_ext.get_string(json(l_list.get(i)),'MSG_status');

Oracle_Flag := json_ext.get_string(json(l_list.get(i)),'Oracle_Flag');

acl := json_ext.get_string(json(l_list.get(i)),'acl');

insert into Appery_Photos values(

A_id,

Photo,

A_Name,

Remarks,

Status,

UserId,

A_Date,

A_Time,

MSG_status ,

Oracle_Flag,

acl

);

end loop;

-- finalizing

UTL_HTTP.end_response(l_http_response);

EXCEPTION

WHEN UTL_HTTP.end_of_body

THEN UTL_HTTP.end_response(l_http_response);

END;

/

该脚本适用于小型JSON文件.但是,当文件包含base64文件(在base64 formate中表示的照片)时,脚本无法给出错误(未找到字符串结尾).

显然,错误是因为没有复制整个文件,因此JSON解析器无法找到字符串“]”或“}”的结尾.

我尝试使用最大大小为32767的CLOB和VARCHAR2,但这还不够.

我想过解码base64文件,但问题是我需要先读取文件才能解码该字段.

任何建议都将深表感谢.

结果

这两个答案都提供了阅读大型JSON文件(> 32KB)的解决方案,我使用了@Jeffrey Kemp.但是,作为下一个问题,其中一个json_values /字段本身大于32KB,json_ext.get_string只返回一个VARCHAR2,这意味着它最多限制为32767个.因此对于字段Photo,其值大于32KB我使用了json_ext.get_json_value和dbms_lob.createtemporary.整理一下后得到的相关脚本如下：

DECLARE

l_val json_value;

l_param_list VARCHAR2(512);

l_http_request UTL_HTTP.req;

l_http_response UTL_HTTP.resp;

l_response_text CLOB;

--l_response_text VARCHAR2(32767);

l_list json_list;

A_id VARCHAR2(100);

Photo VARCHAR2(32767);

A_Name VARCHAR2(100);

Remarks VARCHAR2(100);

Status VARCHAR2(100);

UserId VARCHAR2(100);

A_Date VARCHAR2(100);

A_Time VARCHAR2(100);

MSG_status VARCHAR2(100);

Oracle_Flag VARCHAR2(100);

acl VARCHAR2(100);

BEGIN

-- service's input parameters

-- preparing Request...

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'

, 'GET'

, 'HTTP/1.1');

-- ...set header's attributes

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

-- ...set input parameters

-- UTL_HTTP.write_text(l_http_request, l_param_list);

-- get Response and obtain received value

l_http_response := UTL_HTTP.get_response(l_http_request);

BEGIN

LOOP

UTL_HTTP.read_text(l_http_response, buf);

l_response_text := l_response_text || buf;

END LOOP;

EXCEPTION

WHEN UTL_HTTP.end_of_body THEN

NULL;

END;

l_list := json_list(l_response_text);

FOR i IN 1..l_list.count

LOOP

A_id := json_ext.get_string(json(l_list.get(i)),'_id');

l_val := json_ext.get_json_value(json(l_list.get(i)),'Photo');

dbms_lob.createtemporary(Photo, true, 2);

json_value.get_string(l_val, Photo);

A_Name := json_ext.get_string(json(l_list.get(i)),'Name');

Remarks := json_ext.get_string(json(l_list.get(i)),'Remarks');

Status := json_ext.get_string(json(l_list.get(i)),'Status');

UserId := json_ext.get_string(json(l_list.get(i)),'UserId');

A_Date := json_ext.get_string(json(l_list.get(i)),'Date');

A_Time := json_ext.get_string(json(l_list.get(i)),'Time');

MSG_status := json_ext.get_string(json(l_list.get(i)),'MSG_status');

Oracle_Flag := json_ext.get_string(json(l_list.get(i)),'Oracle_Flag');

acl := json_ext.get_string(json(l_list.get(i)),'acl');

insert into Appery_Photos values(

A_id,

Photo,

A_Name,

Remarks,

Status,

UserId,

A_Date,

A_Time,

MSG_status ,

Oracle_Flag,

acl

);

end loop;

-- finalizing

UTL_HTTP.end_response(l_http_response);

EXCEPTION

WHEN UTL_HTTP.end_of_body

THEN UTL_HTTP.end_response(l_http_response);

END;

/