Dears,
Kindly i need your support as we have an issue an error
UTL_HTTP.READ_TEXT(V_HTTP_RSPNS, V_ENV);
code :
Stored procedure
----------------------------
CREATE OR REPLACE PROCEDURE MCICRValidation_CALL_LOOP(V_CR IN VARCHAR2) AS
V_RQST MCICRValidation.WS_RQST;
V_RSPNS MCICRValidation.WS_RSPNS;
V_XMLNS VARCHAR2(200);
V_XMLNS_XPATH VARCHAR2(200);
V_URL VARCHAR2(200);
V_OP_NM VARCHAR2(100);
V_PWD VARCHAR2(100);
V_CHARSET VARCHAR2(100);
V_CHARSET2 VARCHAR2(100);
V_MSG VARCHAR2(2000);
P_RTRN_CD VARCHAR2(50);
P_RTRN_DESC VARCHAR2(50);
RqID VARCHAR2(250) := '_' || TO_CHAR(SYSDATE, 'YYYYMMDD') ||
SMS_Seq.Nextval;
V_B1 VARCHAR2(400) := '<ejad:MsgRqHdr><ejad:RqUID>TAB' || RqID ||
'</ejad:RqUID><ejad:SCId>TBLU</ejad:SCId><ejad:ServiceId>MCICRValidation</ejad:ServiceId><ejad:FuncId>14000000</ejad:FuncId><ejad:RqMode>0</ejad:RqMode><ejad:ClientDt>2019-04-29T15:08:06</ejad:ClientDt><ejad:EchoData>123456789987654321</ejad:EchoData><ejad:Version>1.0</ejad:Version></ejad:MsgRqHdr><ejad:Body>';
--V_B2 VARCHAR2(400):='<q0:NotificationMethodInfo><q0:NotificationMethod>SMS</q0:NotificationMethod>'
--<q0:Contact>0557609660</q0:Contact><q0:EmailFrom>Amlak</q0:EmailFrom><q0:Username>amlak</q0:Username><q0:Password>123456</q0:Password></q0:NotificationMethodInfo></q0:Body>';
BEGIN
V_CHARSET := 'utf-8'; --'windows-1256'; 'iso-8859-6';
--V_MSG := MOF_WS.MSG_CNST;
V_CHARSET2 := 'UTF8';
V_XMLNS := 'http://www.ejada.com'; --'http://www.ejada.com';
V_XMLNS_XPATH := 'xmlns="http://www.ejada.com"';
--V_URL := 'https://10.14.8.25:7907/NotificationSend11';
V_URL := 'https://ry1drvesbiib01.mof.gov.sa:7907/MCICRValidation11';
V_OP_NM := 'MCICRValidationRq';
/* SELECT TO_CHAR(SUM(AMOUNT),
'99G999G999G9999',
'NLS_NUMERIC_CHARACTERS=",."') AS_OF_DATE_AMOUNT
INTO V_MSG
FROM MOF_REV_EXP_ADJ_DM_DV T
WHERE GL_DATE = TO_DATE(TO_CHAR(SYSDATE - 1, 'DD/MM/YYYY'));*/
--Call the webservice client wrapped java classes
V_RQST := MCICRValidation.GET_NEW_RQST(V_OP_NM,
V_XMLNS,
V_CHARSET,
V_CHARSET2);
MCICRValidation.ADD_TO_BDY(V_B1, V_RQST);
-- Add Notification Content
--MOF_WS.ADD_PRMTR('q0:CommercialRegistrationNumber', 'xsd:string', V_MSG, V_RQST);
MCICRValidation.ADD_TO_BDY('<ejad:CommercialRegistrationNumber>1010205240</ejad:CommercialRegistrationNumber>',
V_RQST);
-- Add Receiver
-- MOF_WS.ADD_PRMTR('q0:Contact', 'xsd:string', V_CR, V_RQST);
-- Add Sender
-- MOF_WS.ADD_PRMTR('q0:EmailFrom', 'xsd:string', 'MOF-BI', V_RQST);
-- Add user name
--MOF_WS.ADD_PRMTR('q0:Username', 'xsd:string', 'TBLU', V_RQST);
-- Add Password
-- MOF_WS.ADD_PRMTR('q0:Password',
-- 'xsd:string',
-- 'Rb8UaPH52NpyWBa5EHazaWMiBjCoi',
-- V_RQST);
MCICRValidation.ADD_TO_BDY('</ejad:Body>', V_RQST);
EJADA_WS.SET_WLLT('file:/oracle/product/12.2.0.1/owm/wallets/SMSINTG',
'DwHPrDm0FSmS#');
P_RTRN_CD := '000000';
P_RTRN_DESC := 'Success';
MCICRValidation.INVOKE(V_RQST,
V_URL,
'',
FALSE,
'mof-RY1ROOTCA-CA',
V_RSPNS,
P_RTRN_CD,
P_RTRN_DESC);
MCICRValidation.CHCK_FLT(V_RSPNS, P_RTRN_CD, P_RTRN_DESC);
END MCICRValidation_CALL_LOOP;
-----------------------------------------------------------------
CREATE OR REPLACE PACKAGE MCICRValidation IS
-- Author : ADMIN
-- Created : 12/20/2006 11:20:34 AM
-- Purpose : Contains the functions and procedures to call externel web services
num1 number := 1;
num2 number := 2;
V_SLEEP number;
http_req utl_http.req;
http_resp utl_http.resp;
TYPE WS_RQST IS RECORD(
MTHD VARCHAR2(100),
NMSPC VARCHAR2(500),
CHARSET VARCHAR2(100),
CHARSET2 VARCHAR2(100),
BDY VARCHAR2(32767));
TYPE WS_RSPNS IS RECORD(
DOC XMLTYPE);
FUNCTION GET_NEW_RQST(P_MTHD IN VARCHAR2,
P_NMSPC IN VARCHAR2,
P_CHARSET IN VARCHAR2 DEFAULT 'iso-8859-6',
P_CHARSET2 IN VARCHAR2 DEFAULT 'AR8ISO8859P6')
RETURN WS_RQST;
FUNCTION GNRT_ENV(P_RQST IN WS_RQST) RETURN VARCHAR2;
PROCEDURE ADD_TO_BDY(P_BDY_STRNG IN VARCHAR2, P_RQST IN OUT WS_RQST);
PROCEDURE ADD_PRMTR(P_NM IN VARCHAR2,
P_TYPE IN VARCHAR2,
P_VAL IN VARCHAR2,
P_RQST IN OUT WS_RQST,
P_NM_SPC IN VARCHAR2 DEFAULT NULL);
PROCEDURE SET_HTTP_PRXY(P_PRXY IN VARCHAR2,
P_PRT IN VARCHAR2,
P_DMN IN VARCHAR2);
PROCEDURE SET_WLLT(P_WLLT_LCTN VARCHAR2, P_WLLT_PWD VARCHAR2);
PROCEDURE INVOKE(P_RQST IN WS_RQST,
P_URL IN VARCHAR2,
P_ACTN IN VARCHAR2,
P_ELMNT_QUALIFIED IN BOOLEAN,
P_HTTPS_HOST IN VARCHAR2,
P_RSPNS OUT WS_RSPNS,
P_RTRN_CD OUT VARCHAR2,
P_RTRN_DESC OUT VARCHAR2);
PROCEDURE CHCK_FLT(P_RSPNS IN WS_RSPNS,
P_RTRN_CD OUT VARCHAR2,
P_RTRN_DESC OUT VARCHAR2);
END MCICRValidation;
---------------------------------------------------
CREATE OR REPLACE PACKAGE BODY MCICRValidation IS
/*Returns a new request object and populate it with the speceified
method name and namespace*/
FUNCTION GET_NEW_RQST(P_MTHD IN VARCHAR2,
P_NMSPC IN VARCHAR2,
P_CHARSET IN VARCHAR2 DEFAULT 'iso-8859-6',
P_CHARSET2 IN VARCHAR2 DEFAULT 'AR8ISO8859P6')
RETURN WS_RQST AS
V_RQST WS_RQST;
BEGIN
V_RQST.MTHD := P_MTHD;
V_RQST.NMSPC := P_NMSPC;
V_RQST.CHARSET := P_CHARSET;
V_RQST.CHARSET2 := P_CHARSET2;
RETURN V_RQST;
END;
----Created by Ibrahim Youssef
/* Constructs and returns the SOAP envelope of the request. The target namespace is exist in the method name element*/
FUNCTION GNRT_ENV(P_RQST IN WS_RQST) RETURN VARCHAR2 AS
V_ENV clob;
BEGIN
V_ENV := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><soapenv:Body><' ||
P_RQST.MTHD || ' xmlns="' || P_RQST.NMSPC || '">' ||
P_RQST.BDY || '</' || P_RQST.MTHD ||
'></soapenv:Body></soapenv:Envelope>';
RETURN V_ENV;
END;
/* Constructs and returns the SOAP envelope of the request. The target namespace is exist in the soap envelope element*/
FUNCTION GNRT_ENV_SOAP_NMSPC(P_RQST IN WS_RQST) RETURN VARCHAR2 AS
V_ENV clob;
V_ID NUMBER;
BEGIN
V_ENV := '<soapenv:Envelope xmlns:ejad="' || P_RQST.NMSPC ||
'" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><soapenv:Body><' ||
'ejad:' || P_RQST.MTHD || '>' || P_RQST.BDY || '</' || 'ejad:' ||
P_RQST.MTHD || '></soapenv:Body></soapenv:Envelope>';
RETURN V_ENV;
END;
/*
Add to the body of the speceified request, the speceified string, to format
a custom message body.
p_bdy_strng: The string to add to the body of the request.
p_rqst: The request object.
*/
PROCEDURE ADD_TO_BDY(P_BDY_STRNG IN VARCHAR2, P_RQST IN OUT WS_RQST) AS
BEGIN
P_RQST.BDY := P_RQST.BDY || P_BDY_STRNG;
END;
/*Add the speceified paramter (name, type and value) to the body
of the speceified messasge request*/
PROCEDURE ADD_PRMTR(P_NM IN VARCHAR2,
P_TYPE IN VARCHAR2,
P_VAL IN VARCHAR2,
P_RQST IN OUT WS_RQST,
P_NM_SPC IN VARCHAR2 DEFAULT NULL) AS
BEGIN
P_RQST.BDY := P_RQST.BDY || '<' || P_NM || ' ' || P_NM_SPC || '>' ||
P_VAL || '</' || P_NM || '>';
END;
/* Set the http proxy, if the database is behind a proxy.
p_prxy: The host name or IP of the proxy.
p_prt: The port on which the proxy is accessed.
p_dmn: The domain of the proxy.
*/
PROCEDURE SET_HTTP_PRXY(P_PRXY IN VARCHAR2,
P_PRT IN VARCHAR2,
P_DMN IN VARCHAR2) AS
BEGIN
UTL_HTTP.SET_PROXY(P_PRXY || ':' || P_PRT, P_DMN);
END;
/*Set the wallet to be used in SSL communication
p_wllt_lctn: The location of the wallet.
p_wllt_pwd: The password of the wallet.
*/
PROCEDURE SET_WLLT(P_WLLT_LCTN VARCHAR2, P_WLLT_PWD VARCHAR2) AS
BEGIN
UTL_HTTP.SET_WALLET(P_WLLT_LCTN, P_WLLT_PWD);
END;
/*Invoke a web service that is exist at the speceified url with the speceified request
and action*/
PROCEDURE INVOKE(P_RQST IN WS_RQST,
P_URL IN VARCHAR2,
P_ACTN IN VARCHAR2,
P_ELMNT_QUALIFIED IN BOOLEAN,
P_HTTPS_HOST IN VARCHAR2,
P_RSPNS OUT WS_RSPNS,
P_RTRN_CD OUT VARCHAR2,
P_RTRN_DESC OUT VARCHAR2) AS
V_ENV clob;
V_HTTP_RQST UTL_HTTP.REQ;
V_HTTP_RSPNS UTL_HTTP.RESP;
V_ID NUMBER;
V_STG VARCHAR2(10) := 1;
BEGIN
/* request that exceptions are raised for error Status Codes */
UTL_HTTP.SET_RESPONSE_ERROR_CHECK(ENABLE => TRUE);
/* allow testing for exceptions like Utl_Http.Http_Server_Error */
UTL_HTTP.SET_DETAILED_EXCP_SUPPORT(ENABLE => TRUE);
if (P_ELMNT_QUALIFIED = true) then
V_ENV := GNRT_ENV(P_RQST);
else
V_ENV := GNRT_ENV_SOAP_NMSPC(P_RQST);
end if;
--v_tmprslt := utl_http.request(p_url,'10.4.0.8:8080','file:C:\ORA_WALLETS','administrator1');
V_HTTP_RQST := UTL_HTTP.BEGIN_REQUEST(P_URL,
'POST',
'HTTP/1.1',
'',
P_HTTPS_HOST);
--The request is established with the remote site.
V_STG := 2;
/* UTL_HTTP.SET_HEADER(V_HTTP_RQST,
'Transfer-Encoding',
-- 'text/xml; charset=ISO-8859-6'); --iso-8859-6
'chunked'); --iso-8859-6*/
--New Change
/* UTL_HTTP.SET_HEADER(V_HTTP_RQST,
'Content-Type',
-- 'text/xml; charset=ISO-8859-6'); --iso-8859-6
-
'text/xml; charset=AR8MSWIN1256'); P_RQST.CHARSET2 */
--New Change
-------------------
--UTL_HTTP.set_header( V_HTTP_RQST, 'Transfer-Encoding', 'chunked');
--UTL_HTTP.set_body_charset( V_HTTP_RQST, 'AR8MSWIN1256');
-- V_ENV := '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:ejad="http://www.ejada.com"><soap:Header/><soap:Body><ejad:MCICRValidationRq><ejad:MsgRqHdr><ejad:RqUID>TBLU201904291508060866</ejad:RqUID><ejad:SCId>TBLU</ejad:SCId><ejad:ServiceId>MCICRValidation</ejad:ServiceId><ejad:FuncId>14000000</ejad:FuncId><ejad:RqMode>0</ejad:RqMode><ejad:ClientDt>2019-04-29T15:08:06</ejad:ClientDt><ejad:EchoData>123456789987654321</ejad:EchoData><ejad:Version>1.0</ejad:Version></ejad:MsgRqHdr><ejad:Body><ejad:CommercialRegistrationNumber>1010205240</ejad:CommercialRegistrationNumber></ejad:Body></ejad:MCICRValidationRq></soap:Body></soap:Envelope>';
/* UTL_HTTP.SET_HEADER(V_HTTP_RQST,
'Content-Type',
-- 'text/xml; charset=ISO-8859-6'); || P_RQST.CHARSET --iso-8859-6
'text/xml; charset=' || P_RQST.CHARSET);
UTL_HTTP.SET_HEADER(V_HTTP_RQST,
'Content-Length',
LENGTH(CONVERT(V_ENV, P_RQST.CHARSET2)));
*/
utl_http.set_header(V_HTTP_RQST, 'Content-Type', 'text/xml');
utl_http.set_header(V_HTTP_RQST, 'Transfer-Encoding', 'chunked');
--New Change
-- BEGIN
-- SELECT SLEEP(5000) INTO V_SLEEP FROM DUAL;
--END;
UTL_HTTP.set_body_charset(P_RQST.CHARSET);
---------------------
UTL_HTTP.WRITE_TEXT(V_HTTP_RQST, V_ENV);
V_STG := 3;
UTL_HTTP.READ_TEXT(V_HTTP_RSPNS, V_ENV);
UTL_HTTP.END_RESPONSE(V_HTTP_RSPNS);
P_RSPNS.DOC := XMLTYPE.CREATEXML(V_ENV);
P_RSPNS.DOC := P_RSPNS.DOC.EXTRACT('/soap:Envelope/soap:Body/child::node()',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"');
CHCK_FLT(P_RSPNS, P_RTRN_CD, P_RTRN_DESC);
EXCEPTION
WHEN OTHERS THEN
P_RTRN_CD := '999998';
P_RTRN_DESC := V_STG || ' ' || UTL_HTTP.GET_DETAILED_SQLERRM;
END;
/*Check if there is a fault returned inside the speceified response messsage and set
the passed return code and return description accordingly*/
PROCEDURE CHCK_FLT(P_RSPNS IN WS_RSPNS,
P_RTRN_CD OUT VARCHAR2,
P_RTRN_DESC OUT VARCHAR2) AS
V_FLT_NODE XMLTYPE;
BEGIN
V_FLT_NODE := P_RSPNS.DOC.EXTRACT('/soap:Fault',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/');
IF (V_FLT_NODE IS NOT NULL) THEN
P_RTRN_CD := V_FLT_NODE.EXTRACT('/soap:Fault/faultcode/child::text()','xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/')
.GETSTRINGVAL();
P_RTRN_DESC := V_FLT_NODE.EXTRACT('/soap:Fault/faultstring/child::text()','xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/')
.GETSTRINGVAL();
ELSE
P_RTRN_CD := '000000';
P_RTRN_DESC := 'Success';
END IF;
END;
END MCICRValidation;
---------------------------------------------------------------------------------------