create or replace procedure get_json_SOMAT_TABLE is
 v_soap_request VARCHAR2(32767);
 v_http_req UTL_HTTP.REQ;
 v_http_resp UTL_HTTP.RESP;
 v_name  VARCHAR2(32767);
 v_value  VARCHAR2(32767);
 v_txt  VARCHAR2(32767);
 
l_list json_list;
mandt           VARCHAR2(200);
kod         VARCHAR2(100);
charg       VARCHAR2(100);
naim     VARCHAR2(100);
naim2     VARCHAR2(100);
naim3   VARCHAR2(100);
naim4    VARCHAR2(100);
edizm        VARCHAR2(100);
gl           VARCHAR2(100);
ag          VARCHAR2(100);
pl       VARCHAR2(100);
pg          VARCHAR2(100);
al    VARCHAR2(100);
pa     VARCHAR2(100);
ro VARCHAR2(100);
fl        VARCHAR2(100);



BEGIN
 -- Define the SOAP request according the the definition of the web service being called
 v_soap_request:= '[{"matnr":"723877","charg_d":"1571595"}, {"matnr":"723878","charg_d":""}]';
 -- Initialise the request
 v_http_req := UTL_HTTP.BEGIN_REQUEST('http://xxx.yyy.zzz:8001/sap/bc/zsomat_list?sap-client=112','POST','HTTP/1.1');
 -- this web service uses basic authentication so we include the username and password details
 UTL_HTTP.SET_AUTHENTICATION(v_http_req, 'RFC_REMOTE', 'trnsbbd');
 -- set header details 
 utl_http.set_body_charset(v_http_req, 'UTF8');
  utl_http.set_header(v_http_req, 'user-agent', 'Apache-HttpClient/4.1.1'); 
  UTL_HTTP.SET_HEADER(v_http_req, 'Content-Type', 'application/json; charset=windows-1251');
 UTL_HTTP.SET_HEADER(v_http_req, 'Content-Length', LENGTH(v_soap_request));
 -- add the soap part of the request
 UTL_HTTP.WRITE_TEXT(v_http_req, v_soap_request);
 -- call the web service and get the response
 v_http_resp:= UTL_HTTP.GET_RESPONSE(v_http_req);
 -- loop through the response headers and write them out
 FOR i IN 1..UTL_HTTP.GET_HEADER_COUNT(v_http_resp) LOOP
  UTL_HTTP.GET_HEADER(v_http_resp, i, v_name, v_value);

 END LOOP;
 -- create a block to trap the end of body exception when no more lines exist.
 BEGIN
  -- loop through the response text which is the soap e details
   UTL_HTTP.READ_LINE(v_http_resp, v_txt);
   l_list := json_list(v_txt);
   FOR i IN 1..l_list.count
LOOP 
  mandt           := json_ext.get_string(json(l_list.get(i)),'mandt');
  kod         := json_ext.get_string(json(l_list.get(i)),'kod');
  charg       := json_ext.get_string(json(l_list.get(i)),'charg');
  naim     := json_ext.get_string(json(l_list.get(i)),'naim');
  naim2     := json_ext.get_string(json(l_list.get(i)),'naim2');
  naim3   := json_ext.get_string(json(l_list.get(i)),'naim3');
  naim4    := json_ext.get_string(json(l_list.get(i)),'naim4');
  edizm        := json_ext.get_string(json(l_list.get(i)),'edizm');
  gl           := json_ext.get_string(json(l_list.get(i)),'gl');
  ag          := json_ext.get_string(json(l_list.get(i)),'ag');
  pl       := json_ext.get_string(json(l_list.get(i)),'pl');
  pg          := json_ext.get_string(json(l_list.get(i)),'pg');
  al    := json_ext.get_string(json(l_list.get(i)),'al');
  pa     := json_ext.get_string(json(l_list.get(i)),'pa');
  ro := json_ext.get_string(json(l_list.get(i)),'ro');
  fl        := json_ext.get_string(json(l_list.get(i)),'fl');

 insert into somat_json ( mandt, kod, CHARG, NAIM,NAIM2,NAIM3,NAIM4,EDIZM,GL,AG,PL,PG,AL,PA,RO,FL) 
 values (mandt,kod,charg,naim,naim2,naim3,naim4,edizm,gl,ag,pl,pg,al,pa,ro,fl) ;
 commit;
end loop;
      
         UTL_HTTP.END_RESPONSE(v_http_resp);

 

 EXCEPTION
  -- need to trap end of body exception as there is no other graceful way to do it.
  WHEN UTL_HTTP.END_OF_BODY THEN
       UTL_HTTP.END_RESPONSE(v_http_resp);

 END;
EXCEPTION
 WHEN OTHERS THEN
  -- make sure we free up any connections that might stay around
  UTL_TCP.CLOSE_ALL_CONNECTIONS;
END;