I have trouble calling a REST-GET service that uses a procedure that returns a CLOB in JSON. When the result has some records, it works fine, but if there are many, it locks.
Oracle 12.1
I have defined a table with postal codes. In this, for each municipality identifier there is a register with a postal code; but in large municipalities, for the same municipality identifier there may be several postal codes, depending on the address within the city.
For example: - for the municipality of Salvatierra de los Barros (id = 6117), there is only one register with your postal code. - for the municipality of Badajoz (id = 6015), there are several records with: * several different postal codes, for each dependent population of Badajoz, and * several postal codes, corresponding to the different streets of this.
In practice:
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
I created a REST service to obtain the postal codes of a municipality. This service invokes a stored procedure that performs the query, get an XMLTYPE, apply JSON format, and return it to the client as CLOB.
The call for the simple case "Salvatierra de los Barros", returns the information correctly; a record of results of the query, and another with the status of success / error. The call for the most complex case "Badajoz", gives error.
Message in the REST log in SQL Developer:
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
Note: If we invoke the procedure directly from an anonymous block, it works correctly in both cases.
In file C: \ app \ client \ diag \ rdbms \ orcl \ orcl \ trace \ orcl.log, - if it works, save a couple of lines:
2018-07-10T11:30:17.410753+02:00
Thread 1 advanced to log sequence 248 (LGWR switch)
Current log# 2 seq# 248 mem# 0: C:\APP\CLIENT\ORADATA\ORCL\REDO02.LOG
- in case of failure, do not write down any information.
I have tried to modify some parameters by entering as Ords Administrator and manipulating inactivity timeout times from 1800 to 3600 sec., and from connection abandonment from 900 to 3600 sec .; however, it also fails.
Definition of the table consulted.
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")
);
Definition of the procedure that builds the query, launches it, returns the result as XMLTYPE, and convert it to JSON before returning it.
CREATE OR REPLACE PACKAGE
PAQ_TGEOCODPOSTALES
IS
FUNCTION OBTENER_DESCONOCIDO
RETURN TGEOCODPOSTALES%ROWTYPE;
/*
Devuelve el registro correspondiente a 'Código postal desconocido'.
*/
PROCEDURE JSON_OBTENER_TODOS
(
pe_idmunicipio IN TGEOMUNICIPIOS.ID%TYPE,
ps_json_resultado OUT CLOB,
ps_json_estado OUT VARCHAR2
);
/*
Devuelve en formato JSON una lista de todos las códigos postales de un municipio.
Devuelve estado de finalización.
*/
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;
Successful example of direct call to the procedure from anonymous block:
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 ( 'Lista de códigos postales del municipio de 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 ( 'Lista de códigos postales del municipio de 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;
The output is:
Procedimiento PL/SQL terminado correctamente.
Lista de códigos postales del municipio de 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}}}
Lista de códigos postales del municipio de 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}]}
Definition of the REST service:
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 ( 'Definición del módulo de servicios para el Sistema Geográfico' );
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 (
'** DEFINICIÓN DE PLANTILLA DEL SERVICIO: OBTENER TODOS, CON PARÁMETROS DE ENTRADA **' );
dbms_output.put_line (
'Plantilla operación GET para todos los cógigos postales de un municipio' );
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 (
' ** CONTROLADOR DE LECTURA CON PARÁMETROS DE ENTRADA **' );
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 ( 'Parámetro de entrada con el identificador del municipio' );
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 ( 'Parámetro de salida con el resultado de la operación.' );
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 ( 'Parámetro de salida con el estado de la operación.' );
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
Correct example of REST call from POSTMAN client:
http://localhost:8080/ords/educomunes/sisgeografico/codpostales/6117
Salida:
{
"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.\"}}}"
}
Example with REST call error from POSTMAN client:
http://localhost:8080/ords/educomunes/sisgeografico/codpostales/6015
Salida (Pretty JSON): Unexpected '<'
Status: 500 Server Error
Time:24906 ms
Size:370.1 KB