oracle - ORDS GET 过程以 JSON 形式返回 CLOB
问题描述
我在调用使用以 JSON 格式返回 CLOB 的过程的 REST-GET 服务时遇到问题。当结果有几条记录时，它运行正常，但如果它们很多，它就会崩溃。
甲骨文 12.1
我已经定义了一个带有邮政编码的表格。在此，每个城市标识符都有一个带有邮政编码的记录。但在大城市中，每个城市都可以有多个邮政编码，具体取决于城市内的地址。
例如，
- 对于“Salvatierra de los Barros”市（id = 6117），只有一个带有邮政编码的登记处
- 对于“Badajoz”市（id = 6015），有几条记录：
* 几个不同的邮政编码，每个村庄都依赖于“Badajoz”，以及
* 几个邮政编码，对应“Badajoz”的不同街道。
在实践中：
SELECT ID, CODPOSTAL, TGEOMUNICIPIO_ID, TGEOUNIPOBLACIONAL_ID,
DENOUPOBLACIONAL, DENODIRECCION
FROM EDUCOMUNES.TGEOCODPOSTALES
WHERE TGEOMUNICIPIO_ID IN ( 6117, 6015 )
ID CODPOSTAL TGEOMUNICIPIO_ID TGEOUNIPOBLACIONAL_ID DENOUPOBLACIONAL DENODIRECCION
------ ---------- ---------------- --------------------- --------------------------- ---------------------
3148 06175 6117 61170001 SALVATIERRA DE LOS BARROS
ID CODPOSTAL TGEOMUNICIPIO_ID TGEOUNIPOBLACIONAL_ID DENOUPOBLACIONAL DENODIRECCION
------ ---------- ---------------- --------------------- --------------------------- ---------------------
3108 06183 6015 60150012 NOVELDA DEL GUADIANA
3146 06181 6015 60150014 SAGRAJAS
3176 06194 6015 60150016 VALDEBOTOA
48558 06003 6015 60150004 Badajoz MARIA LUISA DE CARVAJAL
48562 06003 6015 60150004 Badajoz MARIN DE RODEZNO
48576 06001 6015 60150004 Badajoz MAYOR
48577 06001 6015 60150004 Badajoz MELCHOR DE EVORA
48579 06001 6015 60150004 Badajoz MELENDEZ VALDES (Impares del 19 al final) (Pares del 16 al final)
...
48774 06011 6015 60150004 Badajoz REPUBLICA DE CHILE
1.166 filas seleccionadas
我创建了一个 REST 服务来获取市政当局的邮政编码。该服务调用一个执行查询的存储过程，获取一个 XMLTYPE，应用 JSON 格式，并将它作为 CLOB 返回给客户端。
对简单案例“Salvatierra de los Barros”的调用正确地返回了信息，即，它返回了查询结果和成功状态。调用最复杂的案例“Badajoz”会出错。
SQL Developer 中 REST 日志中的消息：
jul 06, 2018 1:41:10 PM
INFORMACIÓN: Error occurred during execution of: [CALL, BEGIN
paq_tgeocodpostales.json_obtener_todos (
pe_idmunicipio => /*in:idmunicipio*/?,
ps_json_resultado => /*out:json_resultado*/?,
ps_json_estado => /*out:json_estado*/? );
END;,
[idmunicipio, in, class java.lang.Integer],
[json_resultado, out, class java.lang.String],
[json_estado, out, class java.lang.String]]
with values: {1=6015}
jul 06, 2018 1:41:10 PM
INFORMACIÓN: ORA-06502: PL/SQL: error numérico o de valor
ORA-06512: en línea 2
java.sql.SQLException: ORA-06502: PL/SQL: error numérico o de valor
ORA-06512: en línea 2
注意：如果我们直接从匿名块调用该过程，它在这两种情况下都能正常工作。
- 如果它有效，它会节省几行，
- 在失败的情况下，它不记录任何信息。
在文件 C:\app\client\diag\rdbms\orcl\orcl\trace\orcl.log 中，
- 如果它有效，它会保存几行，“2018-07-10T11:30:17.410753+02:00 线程 1 高级到日志序列 248（LGWR 开关）当前日志#2 序列#248 内存#0 : C:\APP\CLIENT\ORADATA\ORCL\REDO02.LOG"
- 在失败的情况下，它不记录任何信息。
我试图通过以 Ords 管理员身份输入并操作从 1800 到 3600 秒的空闲超时时间以及从 900 到 3600 秒的连接放弃来修改一些参数。但是，它也失败了。
源表的定义：
CREATE TABLE "EDUCOMUNES"."TGEOCODPOSTALES"
(
"CODPOSTAL" VARCHAR2(15 BYTE) CONSTRAINT "CHEQUEO_C01" NOT NULL ENABLE,
"ID" NUMBER(*,0) NOT NULL ENABLE,
"TGEOPROVINCIA_ID" NUMBER(*,0),
"TGEOUNIPOBLACIONAL_ID" NUMBER(*,0),
"DENOUPOBLACIONAL" VARCHAR2(100 BYTE),
"TGEOMUNICIPIO_ID" NUMBER(*,0),
"DENODIRECCION" VARCHAR2(255 BYTE),
"BUSCAR" VARCHAR2(255 BYTE),
CONSTRAINT "TGEOCODPOSTALES_PK" PRIMARY KEY ("ID")
);
打包过程的定义：
CREATE OR REPLACE PACKAGE
PAQ_TGEOCODPOSTALES
IS
FUNCTION OBTENER_DESCONOCIDO
RETURN TGEOCODPOSTALES%ROWTYPE;
/*
Returns the record corresponding to 'Unknown postal code'.
*/
PROCEDURE JSON_OBTENER_TODOS
(
pe_idmunicipio IN TGEOMUNICIPIOS.ID%TYPE,
ps_json_resultado OUT CLOB,
ps_json_estado OUT VARCHAR2
);
/*
Returns a list of all postal codes in a municipality in JSON format.
返回完成状态。
*/
END PAQ_TGEOCODPOSTALES;
CREATE OR REPLACE PACKAGE BODY
PAQ_TGEOCODPOSTALES
IS
--====================== SUBRUTINAS ==========
-- ...other funcionality
FUNCTION OBTENER_DESCONOCIDO
RETURN TGEOCODPOSTALES%ROWTYPE
IS
registro TGEOCODPOSTALES%ROWTYPE;
BEGIN
SELECT *
INTO registro
FROM TGEOCODPOSTALES
WHERE BUSCAR LIKE '%DESCONOCIDO%';
RETURN registro;
END OBTENER_DESCONOCIDO;
PROCEDURE JSON_OBTENER_TODOS
(
pe_idmunicipio IN TGEOMUNICIPIOS.ID%TYPE, --==> NUMBER(38,0)
ps_json_resultado OUT CLOB,
ps_json_estado OUT VARCHAR2
)
IS
l_select VARCHAR2 ( 1000 );
l_from VARCHAR2 ( 100 );
l_where VARCHAR2 ( 1000 );
l_order_by VARCHAR2 ( 100 );
l_sql VARCHAR2 ( 2000 );
xml_estado XMLTYPE;
xml_resultado XMLTYPE;
v_json XMLTYPE;
BEGIN
l_select := 'SELECT ID, CODPOSTAL, TGEOMUNICIPIO_ID,
TGEOUNIPOBLACIONAL_ID, DENODIRECCION';
l_from := ' FROM EDUCOMUNES.TGEOCODPOSTALES';
IF ( pe_idmunicipio IS NOT NULL )
THEN
l_where := ' WHERE TGEOMUNICIPIO_ID = ' || pe_idmunicipio;
ELSE
l_where := ' WHERE ID = ' || OBTENER_DESCONOCIDO().ID;
END IF;
l_order_by := ' ORDER BY CODPOSTAL, DENODIRECCION';
l_sql := l_select || l_from || l_where || l_order_by;
paq_xml_json.sql_a_xml ( l_sql, xml_resultado, xml_estado );
ps_json_estado := paq_xml_json.xml_a_jsonvc( xml_estado );
if not ( xml_resultado is null )
then
v_json := paq_xml_json.xml_a_json ( xml_resultado );
ps_json_resultado := paq_xml_json.xml_a_clob ( v_json );
end if;
END JSON_OBTENER_TODOS;
END PAQ_TGEOCODPOSTALES;
从匿名块直接调用过程的成功例子：
set echo off
set verify off
set serveroutput on
SPOOL test_JSON_OBTENER_TODOS.txt
PROMPT 'Prueba de JSON_OBTENER_TODOS'
DECLARE
json_resultado CLOB;
json_estado VARCHAR2 (3200);
PROCEDURE PRINT_CLOB ( p_clob IN CLOB )
IS
MAXLONGBUFFER INTEGER := 32767;
v_longclob INTEGER := 0;
v_numlecturas INTEGER := 0;
v_bytes_restantes INTEGER := 0;
v_desplazamiento INTEGER := 0;
i INTEGER := 1;
BEGIN
v_longclob := dbms_lob.getlength ( p_clob );
v_numlecturas := ( v_longclob / MAXLONGBUFFER );
v_bytes_restantes := MOD ( v_longclob, MAXLONGBUFFER );
IF ( MOD ( v_longclob, MAXLONGBUFFER ) <> 0 )
THEN
v_numlecturas := v_numlecturas + 1;
END IF;
v_desplazamiento := 1;
while ( i < v_numlecturas )
loop
dbms_output.put_line(
dbms_lob.substr(
p_clob,
MAXLONGBUFFER,
v_desplazamiento ) );
v_desplazamiento := v_desplazamiento + MAXLONGBUFFER;
i := i + 1;
end loop;
if ( v_bytes_restantes <> 0 )
then
dbms_output.put_line(
dbms_lob.substr(
p_clob,
v_bytes_restantes,
v_desplazamiento ) );
end if;
END PRINT_CLOB;
BEGIN
dbms_output.ENABLE ( NULL );
dbms_output.new_line;
dbms_output.put_line ( 'List of postal codes of the municipality of Salvatierra de los Barros.' );
dbms_output.new_line;
PAQ_TGEOCODPOSTALES.JSON_OBTENER_TODOS(
6117,
json_resultado,
json_estado );
dbms_output.put_line ( json_estado );
dbms_output.new_line;
PRINT_CLOB ( json_resultado );
dbms_output.new_line;
dbms_output.put_line ( 'List of postal codes of the municipality of Badajoz.' );
dbms_output.new_line;
PAQ_TGEOCODPOSTALES.JSON_OBTENER_TODOS(
6015,
json_resultado,
json_estado );
dbms_output.put_line ( json_estado );
PRINT_CLOB ( json_resultado );
END;
输出：
Procedimiento PL/SQL terminado correctamente.
List of postal codes of the municipality of Salvatierra de los Barros.
{"ROWSET":{"ROW":{"CODERR":0,"MENERR":"La consulta devolvió registros."}}}
{"ROWSET":{"ROW":{"ID":3148,"CODPOSTAL":06175,"TGEOMUNICIPIO_ID":6117,"TGEOUNIPOBLACIONAL_ID":61170001,"DENODIRECCION":null}}}
List of postal codes of the municipality of Badajoz.
{"ROWSET":{"ROW":{"CODERR":0,"MENERR":"La consulta devolvió registros."}}}
{"ROWSET":[{"ID":3006,"CODPOSTAL":06000,"TGEOMUNICIPIO_ID":6015,"TGEOUNIPOBLACIONAL_ID":60150004,"DENODIRECCION":null},
{"ID":47952,"CODPOSTAL":06001,"TGEOMUNICIPIO_ID":6015,"TGEOUNIPOBLACIONAL_ID":60150004,"DENODIRECCION":"ARIAS MONTANO (Impares del 5 al final) (Pares del 8 al final)"},
{"ID":47990,"CODPOSTAL":06001,"TGEOMUNICIPIO_ID":6015,"TGEOUNIPOBLACIONAL_ID":60150004,"DENODIRECCION":"BARTOLOME JOSE GALLARDO"},
...
{"ID":3195,"CODPOSTAL":06195,"TGEOMUNICIPIO_ID":6015,"TGEOUNIPOBLACIONAL_ID":60150018,"DENODIRECCION":null}]}
REST服务的定义：
SPOOL PLANTILLAS_REST_codpostales.txt
DECLARE
l_modulo ORDS_METADATA.ords_modules.name%type;
l_ruta_base ORDS_METADATA.ords_modules.uri_prefix%type;
l_patron_todos ORDS_METADATA.ords_templates.uri_template%type;
l_patron_detalle ORDS_METADATA.ords_templates.uri_template%type;
l_metodo ORDS_METADATA.ords_handlers.method%type;
l_codigo_tipo_fuente ORDS_METADATA.ords_handlers.source_type%type;
l_codigo_fuente ORDS_METADATA.ords_handlers.source%type;
l_registros_por_pag ORDS_METADATA.ords_modules.items_per_page%type; --DEFAULT 25
par_idmunicipio ORDS_METADATA.ords_parameters%rowtype;
par_idcodpostal ORDS_METADATA.ords_parameters%rowtype;
par_resultado ORDS_METADATA.ords_parameters%rowtype;
par_estado ORDS_METADATA.ords_parameters%rowtype;
BEGIN
l_modulo := 'mod_geografico';
l_ruta_base := 'sisgeografico/';
l_patron_todos := 'codpostales/';
l_metodo := 'GET';
l_codigo_tipo_fuente := ORDS.source_type_plsql;
l_registros_por_pag := 0;
par_resultado.name := 'RESULTADO';
par_resultado.bind_variable_name := 'json_resultado';
par_resultado.source_type := 'RESPONSE';
par_resultado.access_method := 'OUT';
par_resultado.param_type := 'STRING';
par_estado.name := 'ESTADO';
par_estado.bind_variable_name := 'json_estado';
par_estado.source_type := 'RESPONSE';
par_estado.access_method := 'OUT';
par_estado.param_type := 'STRING';
DBMS_OUTPUT.PUT_LINE ( 'Definition of the REST services module' );
ORDS.DEFINE_MODULE(
p_module_name => l_modulo,
p_base_path => l_ruta_base,
p_items_per_page => l_registros_por_pag
);
dbms_output.new_line;
dbms_output.put_line (
'** Definition Of Service Template: Obtain All, With Entry Parameters **' );
dbms_output.put_line (
'GET operation template for all postal codes of a municipality' );
par_idmunicipio.name := 'IDMUNICIPIO';
par_idmunicipio.bind_variable_name := 'idmunicipio';
par_idmunicipio.source_type := 'URI';
par_idmunicipio.access_method := 'IN';
par_idmunicipio.param_type := 'INT';
l_patron_todos := l_patron_todos || ':' || par_idmunicipio.bind_variable_name;
ORDS.define_template(
p_module_name => l_modulo,
p_pattern => l_patron_todos
);
dbms_output.new_line;
dbms_output.put_line (
' ** Reading Controller With Entry Parameters **' );
ORDS.define_handler(
p_module_name => l_modulo,
p_pattern => l_patron_todos,
p_method => l_metodo,
p_source_type => l_codigo_tipo_fuente,
p_source => 'BEGIN
paq_tgeocodpostales.json_obtener_todos (
pe_idmunicipio => :' || par_idmunicipio.bind_variable_name || ',
ps_json_resultado => :' || par_resultado.bind_variable_name || ',
ps_json_estado => :' || par_estado.bind_variable_name || ' );
END;',
p_items_per_page => l_registros_por_pag);
DBMS_OUTPUT.PUT_LINE ( 'Input parameter with the municipality identifier' );
ORDS.define_parameter(
p_module_name => l_modulo,
p_pattern => l_patron_todos,
p_method => l_metodo,
p_name => par_idmunicipio.name,
p_bind_variable_name => par_idmunicipio.bind_variable_name,
p_source_type => par_idmunicipio.source_type,
p_access_method => par_idmunicipio.access_method,
p_param_type => par_idmunicipio.param_type
);
DBMS_OUTPUT.PUT_LINE ( 'Output parameter with the result of the operation.' );
ORDS.define_parameter(
p_module_name => l_modulo,
p_pattern => l_patron_todos,
p_method => l_metodo,
p_name => par_resultado.name,
p_bind_variable_name => par_resultado.bind_variable_name,
p_source_type => par_resultado.source_type,
p_access_method => par_resultado.access_method,
p_param_type => par_resultado.param_type
);
DBMS_OUTPUT.PUT_LINE ( 'Output parameter with the status of the operation.' );
ORDS.define_parameter(
p_module_name => l_modulo,
p_pattern => l_patron_todos,
p_method => l_metodo,
p_name => par_estado.name,
p_bind_variable_name => par_estado.bind_variable_name,
p_source_type => par_estado.source_type,
p_access_method => par_estado.access_method,
p_param_type => par_estado.param_type
);
COMMIT;
END;
/
SPOOL OFF
来自 POSTMAN 客户端的 REST 调用成功示例：
http://localhost:8080/ords/educomunes/sisgeografico/codpostales/6117
Output:
{
"RESULTADO": "{\"ROWSET\":{\"ROW\":{\"ID\":3148,\"CODPOSTAL\":06175,\"TGEOMUNICIPIO_ID\":6117,\"TGEOUNIPOBLACIONAL_ID\":61170001,\"DENODIRECCION\":null}}}",
"ESTADO": "{\"ROWSET\":{\"ROW\":{\"CODERR\":0,\"MENERR\":\"La consulta devolvió registros.\"}}}"
}
来自 POSTMAN 客户端的 REST 调用错误示例：
http://localhost:8080/ords/educomunes/sisgeografico/codpostales/6015
Salida (Pretty JSON): Unexpected '<'
Status: 500 Server Error
Time:24906 ms
Size:370.1 KB
解决方案
推荐阅读
- php - 从浏览器运行 phar 文件
- java - 使用 Web Sphere 服务器，我们可以从部署的 .war/.ear/ 中更新和读取文件吗？文件？
- node.js - 寻找node-sqlite3的优化方法
- java - XML Parsing Query Linq 用于搜索所需
- asp.net - asp.net mvc 如何获取到我的网站的传入帖子请求的 Web 服务器 ip
- c++ - 修改 not_null 以禁止与 nullptr 进行比较
- javascript - 使用自动完成 jquery 和 Odoo rpc 调用的输入不显示建议
- angularjs - How to use track by in ng-repeat having array in ng-model
- java - 如何将位图图像添加到int数组
- android - singleLine true for Textview,But multiple lines comes in one line..,Need to show 1st line