CREATE OR REPLACE PACKAGE BODY TEST_WS 
IS 
PROCEDURE wrapper () 
IS 
 
  CURSOR c_track 
  IS 
  SELECT order_id 
  FROM   table1; -- giving 17000 orders 
 
  TYPE track_tab_typ IS TABLE OF c_track%ROWTYPE INDEX BY PLS_INTEGER; 
  track_tab_rec                 track_tab_typ; 
 
  l_batch_cnt      NUMBER := 0; 
   
BEGIN 
 
  OPEN  c_track; 
  LOOP 
   
    FETCH c_track BULK COLLECT INTO track_tab_rec LIMIT 500;     
    EXIT WHEN track_tab_rec.COUNT = 0; 
     
    l_batch_cnt := l_batch_cnt + 1;    
    l_batchid := l_curr_jobid||l_batch_cnt; 
 
    FORALL i IN track_tab_rec.FIRST..track_tab_rec.LAST 
	update header set process_batch = l_batchid	where order = track_tab_rec(i).order_id; 
	COMMIT; 
     
    app_jobs.schedulejob (commandsql       =>  'track_main('|| 
                                               'p_batchid=>'''||l_batchid||''')');-- calling track_main procedure in jobs to invoke web service for 500 orders respectively 
    									    
      
  END LOOP; 
  CLOSE c_track;   
 
EXCEPTION 
   WHEN OTHERS  THEN 
   raise_application_error (-20050, 'wrapper => ' || SQLERRM); 
END wrapper; 
 
PROCEDURE track_main(p_batchid   IN NUMBER) 
IS 
 
  CURSOR c_track 
  IS 
  SELECT order_id 
  FROM   header 
  WHERE  process_batch            = p_batchid; 
   
  TYPE track_tab_typ IS TABLE OF c_track%ROWTYPE INDEX BY PLS_INTEGER; 
  track_tab_rec        track_tab_typ; 
   
  l_request_id         details.request_id%type; 
   
BEGIN 
   
  OPEN  c_track; 
  LOOP 
     FETCH c_track BULK COLLECT INTO track_tab_rec; 
     EXIT WHEN track_tab_rec.COUNT = 0; 
      
     FOR i IN track_tab_rec.FIRST..track_tab_rec.LAST 
     LOOP 
   
        SELECT seq.nextval 
          INTO    l_request_id 
          FROM    dual; 
		   
		soap_request := xml_request --xml request for which i need response through web service 
		 
        INSERT INTO details(order_id,request_id, request_msg,date) 
        VALUES(track_tab_rec.order_id, l_request_id, soap_request, SYSDATE); 
 
    COMMIT; 
 
    invoke_ws(p_request_id => l_request_id, 
              p_error_msg  => p_error_msg); 
 
		 
		EXIT WHEN l_error_msg like '%ORA-24247%';   
     END LOOP; 
     track_tab_rec.DELETE; 
      
  END LOOP; 
  CLOSE c_track; 
  
 
EXCEPTION 
 WHEN OTHERS  THEN 
 raise_application_error (-20050, 'Error wrapper_tracking due to' || SQLERRM); 
 
END track_main; 
 
PROCEDURE invoke_ws (p_request_id    IN  NUMBER, 
                     p_error_msg     OUT  VARCHAR2) 
 
 IS 
 
  CURSOR c_fetch_details 
  IS 
  SELECT REPLACE(REPLACE(request_msg, CHR(10)),CHR(13)) request_msg 
  FROM   details 
  WHERE  request_id     = p_request_id;    
 
  http_req                   utl_http.req; 
  http_resp                  utl_http.resp; 
 
 BEGIN 
 
   --fetching wallet path and password from table-- 
   UTL_HTTP.set_wallet('file:'||l_wallet_path,l_wallet_pw); 
   UTL_HTTP.set_detailed_excp_support (TRUE); 
   UTL_HTTP.set_transfer_timeout(10);    
 
   OPEN  c_fetch_details; 
   FETCH c_fetch_details INTO  l_req_msg; 
   CLOSE c_fetch_details; 
 
   http_req:= utl_http.begin_request 
              ( l_req_url 
              , 'POST' 
              , 'HTTP/1.1' 
              ); 
 
   utl_http.set_header(http_req, 'Content-TYPE', 'text/xml; charset=utf-8'); 
   utl_http.set_header(http_req, 'Content-Length', lengthb(to_char(l_req_msg))); 
   utl_http.set_header(http_req, 'SOAPAction', l_action_url); 
 
   -- Write soap request text data in the HTTP request 
   utl_http.write_text(http_req,l_req_msg); 
 
   -- get_response output of record type utl_http.resp. 
   http_resp:= utl_http.get_response(http_req);  
   utl_http.read_text(http_resp, l_response_msg );  
   utl_http.end_response(http_resp); 
   --verify response text  
 
 EXCEPTION 
   WHEN OTHERS THEN 
     utl_http.end_response(http_resp); 
	 --No need to raise exception in case end_of_body is reached 
     IF SQLCODE <> -29266 THEN  
	    l_error_msg         :=    'Invoking webservice is failed due to : '|| SQLERRM||'-> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE; 
	    p_error_msg         :=    l_error_msg;	  
	 END IF; 
 END;