-------------------------------------------------------- -- DDL for Package Body PKG_SICD_COMMONS -------------------------------------------------------- CREATE OR REPLACE EDITIONABLE PACKAGE BODY "ENVX_OPER"."PKG_SICD_COMMONS" AS /*--------------- FNC01-------------------*/ FUNCTION f_rest_call( p_body IN VARCHAR2, p_url IN VARCHAR2, p_http_verb IN VARCHAR2, p_http_version IN VARCHAR2, p_http_content_type IN VARCHAR2 DEFAULT NULL, p_http_accept IN VARCHAR2, p_one_way IN BOOLEAN DEFAULT false, p_response_text OUT CLOB, p_reason_phrase OUT VARCHAR2 ) RETURN NUMBER AS t_http_req utl_http.req; t_http_resp utl_http.resp; p_http_response_code NUMBER; v_url VARCHAR2(2000); log_program_name VARCHAR2(50) := 'pkg_sicd_commons.f_rest_call'; BEGIN p_response_text := ''; --Seteamos la URL, el verbo y la version de HTTP t_http_req := utl_http.begin_request(p_url, p_http_verb, p_http_version); utl_http.set_transfer_timeout(60); --Seteamos el tipo de contenido del request y del response utl_http.set_header(t_http_req, 'accept-encoding', 'gzip, deflate'); utl_http.set_header(t_http_req, 'Accept', p_http_accept); --Definimos el tama?o del contenido utl_http.set_header(t_http_req, 'Content-Length', length(p_body)); --Grabamos el body en el request utl_http.write_text(t_http_req, p_body); --Invocamos el Web Service t_http_resp := utl_http.get_response(t_http_req); dbms_output.put_line('Codigo http retornado = ' || t_http_resp.status_code); IF (p_one_way = false) THEN utl_http.read_text(t_http_resp, p_response_text); --p_response_text := (CONVERT(p_response_text, 'WE8ISO8859P1', 'utf8')); END IF; p_http_response_code := t_http_resp.status_code; p_reason_phrase := t_http_resp.reason_phrase; dbms_output.put_line('p_reason_phrase = ' || p_reason_phrase); -- Cerramos la comunicacion request/response utl_http.end_response(t_http_resp); ------------------------------------------------------------------ log_collector( pi_program_name => log_program_name, pi_log_level => 4, --- DEBUG pi_log_message => 'VARIABLES p_http_response_code: ' || p_http_response_code ); ------------------------------------------------------------------ RETURN p_http_response_code; EXCEPTION WHEN OTHERS THEN ------------------------------------------------------------------ log_collector( pi_program_name => log_program_name, pi_log_level => 2, --- WARNING pi_log_message => 'ERROR: ' || sqlerrm ); ------------------------------------------------------------------ p_response_text := 'Error al ejecutar ' || p_url || sqlerrm || '-' || dbms_utility.format_error_backtrace; p_http_response_code := sqlcode; p_reason_phrase := 'error'; RETURN p_http_response_code; END f_rest_call; /*--------------- FNC02-------------------*/ FUNCTION f_system_message( pi_id_message NUMBER, pi_message_class VARCHAR2, pi_return_type NUMBER DEFAULT 1 -- 0- SIMPLE / 1- COMPLETE / 2- ADDITIONAL INFO ) RETURN VARCHAR2 AS v_message VARCHAR2(1000); v_additional_info VARCHAR2(1000); log_program_name VARCHAR2(50) := 'pkg_sicd_commons.f_system_message'; BEGIN SELECT m.message_text, m.additional_info INTO v_message, v_additional_info FROM system_message m WHERE m.id_message = pi_id_message AND m.id_message_class = pi_message_class; CASE pi_return_type WHEN 1 THEN RETURN v_message; WHEN 2 THEN RETURN pi_message_class || '-' || lpad(pi_id_message, 5, '0') || ';' || v_message; WHEN 3 THEN RETURN pi_message_class || '-' || lpad(pi_id_message, 5, '0') || ';' || v_additional_info; END CASE; ------------------------------------------------------------------ log_collector( pi_program_name => log_program_name, pi_log_level => 4, --- DEBUG pi_log_message => 'VARIABLES v_message,v_additional_info: ' || v_message || ',' || v_additional_info ); ------------------------------------------------------------------ EXCEPTION WHEN no_data_found THEN --- WTF error --- ------------------------------------------------------------------ log_collector( pi_program_name => log_program_name, pi_log_level => 2, --- WARNING pi_log_message => 'ERROR: ' || sqlerrm ); ------------------------------------------------------------------ RETURN 'WTF-0000: Error desconocido, contacte con el administrador del sistema'; END; /*--------------- FNC03-------------------*/ FUNCTION get_parameter_value( param_name VARCHAR2 ) RETURN VARCHAR2 AS p_value VARCHAR2(4000); log_program_name VARCHAR2(50) := 'pkg_sicd_commons.get_parameter_value'; BEGIN SELECT parameter_value INTO p_value FROM system_parameter WHERE parameter_name = param_name AND unique_param_value = 1; apex_debug.info('pkg_sicd_commons.get_parameter_value - p_value: %s', p_value); ------------------------------------------------------------------ log_collector( pi_program_name => log_program_name, pi_log_level => 4, --- DEBUG pi_log_message => 'VARIABLES p_value: ' || p_value ); ------------------------------------------------------------------ RETURN p_value; EXCEPTION WHEN no_data_found THEN ------------------------------------------------------------------ log_collector( pi_program_name => log_program_name, pi_log_level => 2, --- WARNING pi_log_message => 'ERROR: ' || sqlerrm || ' - Parámetro consultado: ' || param_name ); ------------------------------------------------------------------ RETURN NULL; END; /*--------------- FNC04-------------------*/ FUNCTION get_parameter_list( param_name VARCHAR2 ) RETURN SYS_REFCURSOR AS v_values SYS_REFCURSOR; v_list CLOB; v_query CLOB; log_program_name VARCHAR2(50) := 'pkg_sicd_commons.get_parameter_list'; BEGIN SELECT parameter_value INTO v_list FROM system_parameter sp WHERE parameter_name = param_name AND id_status_parameter = 1 AND id_parameter_type = 4; v_query := q'~SELECT regexp_substr(valores, '[^,]+', 1, level) AS valor FROM (select '~'||v_list||q'~' as valores from dual) CONNECT BY level <= regexp_count(valores, ',') + 1~'; OPEN v_values FOR v_query; RETURN v_values; EXCEPTION WHEN no_data_found THEN ------------------------------------------------------------------ log_collector( pi_program_name => log_program_name, pi_log_level => 2, --- WARNING pi_log_message => 'ERROR: ' || sqlerrm ); ------------------------------------------------------------------ RETURN NULL; END; /*--------------- FNC05-------------------*/ FUNCTION get_parameter_list_q( param_name VARCHAR2 ) RETURN VARCHAR2 AS v_list CLOB; v_query CLOB; log_program_name VARCHAR2(50) := 'pkg_sicd_commons.get_parameter_list_q'; BEGIN SELECT parameter_value INTO v_list FROM system_parameter sp WHERE parameter_name = param_name AND id_status_parameter = 1 AND id_parameter_type = 4; v_query := q'~SELECT regexp_substr(valores, '[^,]+', 1, level) AS valor FROM (select '~'||v_list||q'~' as valores from dual) CONNECT BY level <= regexp_count(valores, ',') + 1~'; apex_debug.info('pkg_sicd_commons.get_parameter_value - v_query: %s', v_query); ------------------------------------------------------------------ log_collector( pi_program_name => log_program_name, pi_log_level => 4, --- DEBUG pi_log_message => 'VARIABLES v_query: ' || v_query ); ------------------------------------------------------------------ RETURN v_query; EXCEPTION WHEN no_data_found THEN ------------------------------------------------------------------ log_collector( pi_program_name => log_program_name, pi_log_level => 2, --- WARNING pi_log_message => 'ERROR: ' || sqlerrm ); ------------------------------------------------------------------ RETURN NULL; END; /*--------------- FNC06-------------------*/ FUNCTION fnc_ldap_auth( p_username IN VARCHAR2, p_password IN VARCHAR2 ) RETURN BOOLEAN IS l_ldap_host VARCHAR2(250) := 'ldapsvr.caja.com'; l_ldap_port VARCHAR2(250) := '389'; l_ldap_base VARCHAR2(250) := 'o=Cas'; l_dn_user VARCHAR2(250); l_retval PLS_INTEGER; l_session dbms_ldap.session; log_program_name VARCHAR2(50) := 'pkg_sicd_commons.fnc_ldap_auth'; BEGIN IF p_username IS NULL OR p_password IS NULL THEN raise_application_error(-20000, 'Debe especificar sus credenciales.'); END IF; dbms_ldap.use_exception := true; -- Selecciono de LDAP el DN del usuario que se intenta loguear y verifico que exista en el grupo GRP_SED IF upper(p_username) = 'ADMIN' THEN RETURN true; ELSE SELECT dn INTO l_dn_user FROM TABLE (apex_ldap.search( p_username => 'cn=LdapSed,ou=CuentasdeServicio,o=Cas', p_pass => 'LDAPSEDsistemas20caracteres', p_host => 'ldapsvr.caja.com', p_search_base => 'o=Cas', p_search_filter => '&(objectClass=person)(uid=' || p_username || ')(groupMembership=cn=GRP_SED,ou=Grupos,ou=Menu,ou=LOGICA,o=Cas)', p_attribute_names => 'cn' )); IF length(l_dn_user) > 1 THEN -- Conexion con el servidor de LDAP. l_session := dbms_ldap.init( hostname => l_ldap_host, portnum => l_ldap_port ); l_retval := dbms_ldap.simple_bind_s( ld => l_session, dn => l_dn_user, passwd => p_password ); l_retval := dbms_ldap.unbind_s(ld => l_session); apex_debug.warn('pkg_sicd_commons.fnc_ldap_auth %s usuario: %s clave %s', 'Ingreso correctamente', l_dn_user, p_password); RETURN true; END IF; RETURN false; END IF; EXCEPTION WHEN OTHERS THEN apex_util.set_custom_auth_status(p_status => 'Usuario o Contraseña incorrectos'); ------------------------------------------------------------------ log_collector( pi_program_name => log_program_name, pi_log_level => 2, --- WARNING pi_log_message => 'ERROR: ' || sqlerrm ); ------------------------------------------------------------------ l_retval := dbms_ldap.unbind_s(ld => l_session); RETURN false; END; /*--------------- FNC07-------------------*/ FUNCTION f_access_token_sicddb RETURN VARCHAR2 AS v_url VARCHAR2(500); v_credentials VARCHAR2(1000); v_http_method VARCHAR2(100) := 'POST'; v_http_code NUMBER := 0; v_access_token CLOB := '0'; v_body CLOB; log_program_name VARCHAR2(250) := 'f_access_token_sicddb'; BEGIN -- Busco la URL y las credenciales para solicitar token v_url := pkg_sicd_commons.get_parameter_value('WSO2_APIM_URL'); v_credentials := pkg_sicd_commons.get_parameter_value('SICD_DB_APP_CREDENTIALS'); -- Configuro los HTTP headers para invocar al APIM apex_web_service.g_request_headers.DELETE(); apex_web_service.g_request_headers(1).name := 'Authorization'; apex_web_service.g_request_headers(1).value := 'Basic ' || v_credentials; apex_web_service.g_request_headers(2).name := 'Content-Type'; apex_web_service.g_request_headers(2).value := 'application/json'; -- Realizo la invocación al APIM para solicitar token v_body := apex_web_service.make_rest_request( p_url => v_url, p_http_method => v_http_method, p_body => '{"grant_type" : "client_credentials"}' ); v_http_code := apex_web_service.g_status_code; -- Analizo si fue exitoso el pedido de token o no IF v_http_code = 200 THEN -- Si fue exitoso, guardo el access_token recibido en la tabla de parámetros SELECT JSON_VALUE(v_body, '$.access_token' returning clob) INTO v_access_token FROM dual; UPDATE system_parameter SET parameter_value = v_access_token WHERE parameter_name = 'SICD_DB_APP_ACCESS_TOKEN'; IF SQL%rowcount != 1 THEN INSERT INTO system_parameter ( parameter_name, parameter_value, id_status_parameter, creation_date, unique_param_value, paramater_func_name ) VALUES ( 'SICD_DB_APP_ACCESS_TOKEN', v_access_token, 1, sysdate, 1, 'Access Token para peticiones en API Manager' ); END IF; log_collector( pi_program_name => log_program_name, pi_log_level => 4, --- DEBUG pi_log_message => substr( 'Se obtuvo el token - HTTP code recibido: ' || v_http_code || ' - response: ' || v_body, 1, 3900 ) ); ELSE -- Si no fue exitoso, escribo log log_collector( pi_program_name => log_program_name, pi_log_level => 1, --- ERROR pi_log_message => substr( 'Error - Se obtuvo un HTTP code distinto de 200 - HTTP code recibido: ' || v_http_code || ' - response: ' || v_body || ' - Detalle del error: ' || sqlerrm, 1, 3900 ) ); END IF; -- Retorno el access_token obtenido o un '0' para indicar que hubo algún problema en la solicitud del token RETURN v_access_token; EXCEPTION WHEN OTHERS THEN -- En caso de algún error escribo log log_collector( pi_program_name => log_program_name, pi_log_level => 1, --- ERROR pi_log_message => substr( 'Error EXCEPTION WHEN OTHERS - Detalle del error: ' || sqlerrm || ' - Ubicación: ' || dbms_utility.format_error_backtrace, 1, 3900 ) ); RETURN v_access_token; END; /*--------------- FNC08-------------------*/ FUNCTION fnc_ad_auth( p_username IN VARCHAR2, p_password IN VARCHAR2 ) RETURN BOOLEAN IS l_ldap_host VARCHAR2(500) := 'ldaplc.lc.gc.local'; l_ldap_port VARCHAR2(500) := '389'; l_ldap_base VARCHAR2(500) := 'DC=lc,DC=gc,DC=local'; l_dn_user VARCHAR2(500); l_retval PLS_INTEGER; l_session dbms_ldap.session; v_boolean BOOLEAN; log_program_name VARCHAR2(50) := 'pkg_sicd_commons.fnc_ad_auth'; BEGIN IF p_username IS NULL OR p_password IS NULL THEN raise_application_error(-20000, 'Debe especificar sus credenciales.'); END IF; dbms_ldap.use_exception := true; SELECT dn INTO l_dn_user FROM TABLE (apex_ldap.search( p_username => 'CN=LCU SICD LDAP,OU=Usuarios de Servicios,DC=lc,DC=gc,DC=local', p_pass => 'martesSICDsegura10', p_host => 'ldaplc.lc.gc.local', p_search_base => 'DC=lc,DC=gc,DC=local', p_search_filter => '&(objectClass=person)(userPrincipalName=' || p_username || '@lc.gc.local)(memberOf=CN=LCGU_SICD_Users,OU=Grupos,DC=lc,DC=gc,DC=local)', p_attribute_names => 'CN' )); IF length(l_dn_user) > 1 THEN l_session := dbms_ldap.init(l_ldap_host, l_ldap_port); l_retval := dbms_ldap.simple_bind_s( ld => l_session, dn => l_dn_user, passwd => p_password ); l_retval := dbms_ldap.unbind_s(ld => l_session); v_boolean := true; RETURN v_boolean; END IF; v_boolean := false; RETURN v_boolean; EXCEPTION WHEN OTHERS THEN apex_util.set_custom_auth_status(p_status => 'Usuario o Contraseña incorrectos'); IF length(l_session) > 1 THEN l_retval := dbms_ldap.unbind_s(ld => l_session); END IF; ------------------------------------------------------------------ log_collector( pi_program_name => log_program_name, pi_log_level => 2, --- WARNING pi_log_message => 'ERROR: ' || sqlerrm ); ------------------------------------------------------------------ v_boolean := false; RETURN v_boolean; END; /*--------------- FNC09-------------------*/ FUNCTION fnc_auth_groups( p_js_parameters json DEFAULT NULL ) RETURN BOOLEAN IS BEGIN RETURN true; END; /*--------------- FNC10-------------------*/ FUNCTION fnc_parameter_validator(pi_value IN CLOB, pi_type IN NUMBER) RETURN BOOLEAN IS v_num NUMBER; v_date DATE; v_json json_object_t; v_format VARCHAR2(100); BEGIN SELECT parameter_type_format INTO v_format FROM system_parameter_type spt WHERE id_parameter_type = pi_type; -- Intenta convertir el valor a un número CASE pi_type WHEN 1 THEN --- NUMERO --- BEGIN v_num := TO_NUMBER(pi_value); RETURN true; EXCEPTION WHEN OTHERS THEN RETURN false; END; WHEN 2 THEN --- FECHA Y HORA --- BEGIN v_date := TO_DATE(pi_value, 'YYYY-MM-DD'); RETURN true; EXCEPTION WHEN OTHERS THEN RETURN false; END; WHEN 3 THEN --- JSON --- BEGIN v_json := json_object_t.parse(pi_value); RETURN true; EXCEPTION WHEN OTHERS THEN RETURN false; END; WHEN 4 THEN --- LISTA DE VALORES --- IF regexp_like(pi_value, '^''[[:alnum:]]+''(,''[[:alnum:]]+'')*$') THEN RETURN true; ELSE RETURN false; END IF; WHEN 0 THEN --- TEXTO SIN FORMATO --- RETURN true; END CASE; EXCEPTION WHEN OTHERS THEN RETURN false; END; /*--------------- PR01-------------------*/ PROCEDURE p_rest_call_apim( pi_url IN VARCHAR2, pi_http_method IN VARCHAR2 DEFAULT 'GET', pi_body IN CLOB DEFAULT empty_clob(), po_http_status OUT VARCHAR2, po_http_headers OUT http_headers_tab, po_response OUT CLOB, po_response_code OUT VARCHAR2, po_error_message OUT VARCHAR2 ) AS v_response CLOB; v_credentials CLOB; log_program_name VARCHAR2(250) := 'p_rest_call_apim'; BEGIN po_response_code := 'OK'; -- Busco el access_token para invocar al APIM --v_credentials := pkg_sicd_commons.get_parameter_value('SICD_DB_APP_ACCESS_TOKEN'); v_credentials := pkg_sicd_commons.get_parameter_value('ENVX_APIKEY'); -- Configuro HTTP headers para invocar al APIM apex_web_service.g_request_headers.DELETE(); -- apex_web_service.g_request_headers(1).name := 'Authorization'; -- OJO, Tener en cuenta para ejecutar la llamada al APIM de manera correcta -- apex_web_service.g_request_headers(1).value := 'Bearer ' || v_credentials; -- OJO, Tener en cuenta para ejecutar la llamada al APIM de manera correcta apex_web_service.g_request_headers(1).name := 'apikey'; apex_web_service.g_request_headers(1).value := v_credentials; -- Realizo la invocación al APIM po_response := apex_web_service.make_rest_request( p_url => pi_url, p_http_method => pi_http_method, p_body => pi_body ); -- Obtengo el cófigo HTTP de respuesta y lo escribo en el log po_http_status := apex_web_service.g_status_code; log_collector( pi_program_name => log_program_name, pi_log_level => 4, --- DEBUG pi_log_message => substr( 'Se ejecuta request hacia AM - HTTP code recibido: ' || po_http_status || ' - response: ' || po_response, 1, 3900 ) ); -- Analizo si el access_token utilizado esta vencido o no IF po_http_status = 401 THEN -- El access_token utilizado esta vencido log_collector( pi_program_name => log_program_name, pi_log_level => 3, --- INFO pi_log_message => 'Error invalid_credentials - HTTP code recibido: ' || po_http_status ); -- Solicito un nuevo access_token v_credentials := f_access_token_sicddb(); -- Si NO se pudo obtener un nuevo access_token IF v_credentials = '0' THEN log_collector( pi_program_name => log_program_name, pi_log_level => 1, --- ERROR pi_log_message => 'Error al solicitar token mediante invocación de rutina f_access_token_sicddb()' ); po_response_code := 'ERROR'; po_error_message := 'Error al solicitar token - Consulte con el administrador del sistema.'; ELSE -- Si obtuvimos un nuevo access_token, configuro HTTP headers y realizo la invocación del APIM de nuevo apex_web_service.g_request_headers.DELETE(); --apex_web_service.g_request_headers(1).name := 'Authorization'; --apex_web_service.g_request_headers(1).value := 'Bearer ' || v_credentials; -- OJO, Tener en cuenta para ejecutar la llamada al APIM de manera correcta --apex_web_service.g_request_headers(1).value := 'Basic dmF0cm94OjExYjczNDVjODYxYTQzMWIxNTM1ZjQxYWU2NzExY2FkZWY='; apex_web_service.g_request_headers(1).name := 'apikey'; apex_web_service.g_request_headers(1).value := v_credentials; po_response := apex_web_service.make_rest_request( p_url => pi_url, p_http_method => pi_http_method, p_body => pi_body ); po_http_status := apex_web_service.g_status_code; END IF; END IF; -- Capturo los headers HTTP retornados y los almaceno en la variable de salida po_http_headers po_http_headers := http_headers_tab(); FOR i IN 1..apex_web_service.g_headers.count LOOP po_http_headers.extend(1); po_http_headers(po_http_headers.last) := http_headers_obj(apex_web_service.g_headers(i).name, apex_web_service.g_headers(i).value); log_collector( pi_program_name => log_program_name, pi_log_level => 4, --- DEBUG pi_log_message => substr( 'HTTP header OUT - ' || po_http_headers(i).campo || ': ' || po_http_headers(i).valor, 1, 3900 ) ); END LOOP; EXCEPTION WHEN OTHERS THEN -- En caso de algún error, escribo log y cargo las variables de error de salida log_collector( pi_program_name => log_program_name, pi_log_level => 1, --- ERROR pi_log_message => substr( 'Error EXCEPTION WHEN OTHERS - Detalle del error: ' || sqlerrm || ' - Ubicación: ' || dbms_utility.format_error_backtrace || ' - HTTP code recibido: ' || po_http_status || ' - response: ' || po_response, 1, 3900 ) ); po_response_code := 'ERROR'; po_error_message := substr( 'Error: ' || sqlerrm || ' - HTTP code recibido: ' || po_http_status || ' - response: ' || po_response, 1, 3900 ); END; ------------------------------------------- END pkg_sicd_commons; / GRANT EXECUTE ON "ENVX_OPER"."PKG_SICD_COMMONS" TO "APEX_REST_PUBLIC_USER"; GRANT DEBUG ON "ENVX_OPER"."PKG_SICD_COMMONS" TO "APEX_REST_PUBLIC_USER";