Last active
June 18, 2021 21:36
-
-
Save hackvan/13f2dee983cc64fdebd0 to your computer and use it in GitHub Desktop.
PL/SQL Oracle: Obtener información JSON de un Web Service.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Create Or Replace Procedure getRespWebService(pevc_url In Varchar2) Is | |
l_http_request utl_http.req; | |
l_http_response utl_http.resp; | |
l_response_text Clob; | |
l_content_type Varchar2(100); | |
l_IpAddress Varchar2(16); | |
l_Port Varchar2(10); | |
l_list json_list; | |
-- Variables del JSON de respuesta: | |
tipoDocumental Varchar2(200); | |
itemId Varchar2(100); | |
dataId Varchar2(100); | |
-- Atributos: | |
GCAnumRadica Varchar2(100); | |
GCAfecha Varchar2(100); | |
GCAasunto Varchar2(100); | |
GCAnomDst Varchar2(100); | |
GCAapeDst Varchar2(100); | |
GCAdpdDst Varchar2(100); | |
GCAnomRmt Varchar2(100); | |
GCAapeRmt Varchar2(100); | |
GCAdpdRmt Varchar2(100); | |
GCAcodDoc Varchar2(100); | |
GCAreferencia Varchar2(100); | |
GCANota Varchar2(100); | |
GCAanexos Varchar2(100); | |
GCAcorreoPARA Varchar2(100); | |
GCAcorreoCC Varchar2(100); | |
GCANotaProc Varchar2(100); | |
-- Procedimiento para obtener la IP y el Puerto de una URL. | |
Procedure ProObtenerIpPort(pevc_URL In Varchar2, psvc_Ip Out Varchar2, psvc_Port Out Varchar2) Is | |
vcTemp Varchar2(200); | |
nuPos Integer := 0; | |
Begin | |
vcTemp := pevc_URL; | |
nuPos := Instr(vcTemp, '//') + 2; | |
vcTemp := Substr(vcTemp, nuPos); | |
nuPos := Instr(vcTemp, '/') - 1; | |
vcTemp := Substr(vcTemp, 1, nuPos); | |
nuPos := Instr(vcTemp, ':'); | |
If nuPos > 0 Then | |
psvc_Ip := Substr(vcTemp, 1, nuPos - 1); | |
psvc_Port := Substr(vcTemp, nuPos + 1); | |
Else | |
psvc_Ip := vcTemp; | |
psvc_Port := '80'; | |
End If; | |
End ProObtenerIpPort; | |
Begin | |
dbms_output.put_line('Request> URL: ' || pevc_url); | |
ProObtenerIpPort(pevc_url, l_IpAddress, l_Port); | |
utl_http.set_transfer_timeout(30); | |
l_http_request := utl_http.begin_request(pevc_url, 'GET', 'HTTP/1.1'); | |
--utl_http.set_header(l_http_request, 'User-Agent', 'Mozilla/4.0'); | |
--utl_http.set_header(l_http_request, 'Content-Type', 'text/html;charset=UTF-8'); | |
utl_http.set_header(l_http_request, 'Host', l_IpAddress || ':' || l_Port); | |
utl_http.set_header(l_http_request, 'Accept-Encoding', 'gzip,deflate'); | |
utl_http.set_header(l_http_request, 'Connection', 'Keep-Alive'); // Linea importante para mantener la comunicación. | |
-- Obtener la respuesta de la petición: | |
l_http_response := utl_http.get_response(l_http_request); | |
utl_http.get_header_by_name(l_http_response, 'Content-Type', l_content_type); | |
dbms_output.put_line('Response> status_code..: "' || l_http_response.status_code || '"'); | |
dbms_output.put_line('Response> reason_phrase: "' || l_http_response.reason_phrase || '"'); | |
dbms_output.put_line('Response> http_version.: "' || l_http_response.http_version || '"'); | |
dbms_output.put_line('Response> content_type.: "' || l_content_type || '"'); | |
utl_http.read_text(l_http_response, l_response_text); | |
utl_http.end_response(l_http_response); | |
dbms_output.put_line(''); | |
dbms_output.put_line('l_response_text>'); | |
dbms_output.put_line(l_response_text); | |
dbms_output.put_line(''); | |
dbms_output.put_line(''); | |
-- Si la respuesta es del tipo JSON: | |
If Instr(Lower(l_content_type), 'application/json') > 0 Then | |
-- Se utiliza el objeto generico JSON del proyecto: https://github.com/pljson/pljson | |
l_list := json_list(l_response_text); | |
For i IN 1 .. l_list.count Loop | |
tipoDocumental := json_ext.get_string(json(l_list.get(i)),'tipoDocumental'); | |
itemId := json_ext.get_string(json(l_list.get(i)),'itemId'); | |
dataId := json_ext.get_string(json(l_list.get(i)),'dataId'); | |
-- JSON de atributos: | |
GCAnumRadica := json_ext.get_string(json(l_list.get(i)),'atributos.GCAnumRadica'); | |
GCAfecha := json_ext.get_string(json(l_list.get(i)),'atributos.GCAfecha'); | |
GCAasunto := json_ext.get_string(json(l_list.get(i)),'atributos.GCAasunto'); | |
GCAnomDst := json_ext.get_string(json(l_list.get(i)),'atributos.GCAnomDst'); | |
GCAapeDst := json_ext.get_string(json(l_list.get(i)),'atributos.GCAapeDst'); | |
GCAdpdDst := json_ext.get_string(json(l_list.get(i)),'atributos.GCAdpdDst'); | |
GCAnomRmt := json_ext.get_string(json(l_list.get(i)),'atributos.GCAnomRmt'); | |
GCAapeRmt := json_ext.get_string(json(l_list.get(i)),'atributos.GCAapeRmt'); | |
GCAdpdRmt := json_ext.get_string(json(l_list.get(i)),'atributos.GCAdpdRmt'); | |
GCAcodDoc := json_ext.get_string(json(l_list.get(i)),'atributos.GCAcodDoc'); | |
GCAreferencia := json_ext.get_string(json(l_list.get(i)),'atributos.GCAreferencia'); | |
GCANota := json_ext.get_string(json(l_list.get(i)),'atributos.GCANota'); | |
GCAanexos := json_ext.get_string(json(l_list.get(i)),'atributos.GCAanexos'); | |
GCAcorreoPARA := json_ext.get_string(json(l_list.get(i)),'atributos.GCAcorreoPARA'); | |
GCAcorreoCC := json_ext.get_string(json(l_list.get(i)),'atributos.GCAcorreoCC'); | |
GCANotaProc := json_ext.get_string(json(l_list.get(i)),'atributos.GCANotaProc'); | |
dbms_output.put_line('GCAnumRadica..= '||GCAnumRadica); | |
dbms_output.put_line('GCAfecha......= '||To_Char(To_Date(GCAfecha, 'YYYY-MM-DD'), 'DD/MM/YYYY')); | |
dbms_output.put_line('GCAasunto.....= '||GCAasunto); | |
dbms_output.put_line(''); | |
End Loop; | |
End If; | |
End; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Begin | |
-- Crear un nuevo archivo XML para el ACL Oracle: | |
dbms_network_acl_admin.create_acl ( | |
acl => 'networkacl.xml', | |
description => 'Allow Network Connectivity', | |
principal => 'PUBLIC', | |
is_grant => TRUE, | |
privilege => 'connect', | |
start_date => SYSTIMESTAMP, | |
end_date => NULL); | |
-- Asignar la IP-Puerto al cual se le dara permiso de conexión: | |
dbms_network_acl_admin.assign_acl ( | |
acl => 'networkacl.xml', | |
host => '192.168.1.111', | |
lower_port => Null, | |
upper_port => Null); | |
-- Asignar el Priviligio para resolver el archivo ACL a un usuario: | |
dbms_network_acl_admin.add_privilege ( | |
acl => 'networkacl.xml', | |
principal => User, | |
is_grant => TRUE, | |
privilege => 'resolve', | |
position => null); | |
Commit; | |
End; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment