Search This Blog

Sunday, December 10, 2017

Calling website or sending sms with parameters from oracle database 11g using pl/sql

DECLARE
  req   UTL_HTTP.REQ;
  resp  UTL_HTTP.RESP;
  value VARCHAR2(1024);  -- URL to post to
  v_url VARCHAR2(200) := 'http://yoursmsgateway.com/sendurlcomma.aspx?';
  -- Post Parameters
  v_param VARCHAR2(500);
  v_param_length NUMBER ;
CURSOR C1 IS

SELECT '97155xxxxx' Mobile,'Hello Ammar test from plsql' Text from dual
union all
SELECT '97155xxxxxx' Mobile,'Hello Afzal from plsql' Text from dual


BEGIN
FOR I IN C1 LOOP
  -- Set up proxy servers if required
  --  UTL_HTTP.SET_PROXY('proxy.my-company.com', 'corp.my-company.com');
   v_param :=  'user=12345'||'&'||'pwd=12345'||'&'||'senderid=SMSAlert'||'&'||'mobileno='||I.MOBILE||',mobileno'||'&'||'msgtext='||I.TEXT||'&'||'priority=High'||'&'||'CountryCode=ALL';
   v_param_length :=  length(v_param);
  req := UTL_HTTP.BEGIN_REQUEST (url=> v_url, method => 'POST');
  --  UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
  UTL_HTTP.SET_HEADER (r      =>  req,
                       name   =>  'Content-Type',
                       value  =>  'application/x-www-form-urlencoded');
  UTL_HTTP.SET_HEADER (r      =>   req,
                       name   =>   'Content-Length',
                       value  =>   v_param_length);
  UTL_HTTP.WRITE_TEXT (r      =>   req,
                       data   =>   v_param); 
  resp := UTL_HTTP.GET_RESPONSE(req);
 
  dbms_output.put_line(i.mobile);
 
  --LOOP
    UTL_HTTP.READ_LINE(resp, value, TRUE);
    DBMS_OUTPUT.PUT_LINE(value);
  --END LOOP;
 
 
  UTL_HTTP.END_RESPONSE(resp);
  END LOOP; 
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(resp);
END;
/