| Error in PL/SQL Procedure using UTL_HTTP [message #607231] |
Tue, 04 February 2014 02:15  |
sarfraz_attari
Messages: 123
Registered: July 2005
|
Senior Member |
|
|
Dear all
We need to send SMS to our customers through Oracle PL/SQL Procedure
We prepared the procedure as attached
But when we are running the procedure following error appears
SQL> connect whadmin/whdev@dev
Connected.
SQL> set serveroutput on
SQL> EXEC UTL_HTTP.set_wallet('file:/disk9/app/oracle/admin/dev/wallet', 'test1234');
PL/SQL procedure successfully completed.
SQL> execute expert_http
https://www.experttexting.com/exptapi/exptsms.asmx/SendSMS?Userid=MAC&pwd=1833334&APIKEY=efff50e05dc
145&FROM=DEFAULT&To=96599464527&MSG=This is a Test mail
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN""http://www.w3.org/TR/html4/strict.dtd">
<HTML><HEAD><TITLE>Bad Request</TITLE>
<META HTTP-EQUIV="Content-Type" Content="text/html; charset=us-ascii"></HEAD>
<BODY><h2>Bad Request</h2>
<hr><p>HTTP Error 400. The request is badly formed.</p>
</BODY></HTML>
PL/SQL procedure successfully completed.
SQL>
We are not receiving sms
Thanks and regards
Sarfarazh Attari
|
|
|
|
| Re: Error in PL/SQL Procedure using UTL_HTTP [message #607238 is a reply to message #607231] |
Tue, 04 February 2014 02:58  |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Please read How to use [code] tags and make your code easier to read your code is very difficult to read without tags.
When I put your URL into a browser, I get this:
This XML file does not appear to have any style information associated with it. The document tree is shown below.
<ExpertTextAPI><Status>SUCCESS</Status><Country>965</Country><TO>96599464527</TO><MsgId>1136376</MsgId></ExpertTextAPI> |
|
|
|
| Re: Error in PL/SQL Procedure using UTL_HTTP [message #607240 is a reply to message #607238] |
Tue, 04 February 2014 03:33 |
sarfraz_attari
Messages: 123
Registered: July 2005
|
Senior Member |
|
|
Dear John
Thanks for your reply
It is working fine when we are running through web browser but while using oracle PL/SQL it is giving error
So according to Oracle representative it is related to web application or web service
So I need to know what modification or addition we need to do in procedure to run the procedure successfully
Thanks and regards
Sarfarazh attari
|
|
|
|
|
|
|
|
| Re: Error in PL/SQL Procedure using UTL_HTTP [message #607250 is a reply to message #607245] |
Tue, 04 February 2014 04:28 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Well, I've tried your code and it runs for me without error. Of course, I do not know if you are getting the SMSs. See below, I'm using http, not https, because I don't have your digital certificate:orclz>
orclz> conn / as sysdba
Connected.
orclz>
orclz> exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('scott.xml',-
> 'scott everywhre',-
> 'SCOTT',true,'connect')
PL/SQL procedure successfully completed.
orclz> exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('scott.xml','*');
PL/SQL procedure successfully completed.
orclz> create or replace
2 PROCEDURE expert_http IS
3 req UTL_HTTP.REQ;
4 resp UTL_HTTP.RESP;
5 value VARCHAR2(1024);
6 url VARCHAR2(32767);
7 OPT varchar2(1000);
8 BEGIN
9 --URL := 'https://www.experttexting.com/exptapi/exptsms.asmx/SendSMS?Userid=MAC'||'&'||'pwd=1833334'||'&'||'APIKEY=efff50e05
dc145'||'&'||'FROM=DEFAULT'||'&'||'To=96599464527'||'&'||'MSG=This is a Test mail';
10 URL := 'http://www.experttexting.com/exptapi/exptsms.asmx/SendSMS?Userid=MAC&pwd=1833334&APIKEY=efff50e05dc145&FROM=DEFAUL
T&To=96599464527&MSG=This is a Test mail';
11 DBMS_OUTPUT.PUT_LINE(URL);
12 req := UTL_HTTP.begin_REQUEST(url);
13 UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
14 resp := UTL_HTTP.GET_RESPONSE(req);
15 begin
16 LOOP
17 UTL_HTTP.READ_LINE(resp,value,TRUE);
18 DBMS_OUTPUT.PUT_LINE(value);
19 END LOOP;
20 UTL_HTTP.END_RESPONSE(resp);
21 EXCEPTION
22 WHEN UTL_HTTP.END_OF_BODY THEN
23 UTL_HTTP.END_RESPONSE(resp);
24 end ;
25 END;
26 /
Procedure created.
orclz> execute expert_http
PL/SQL procedure successfully completed.
orclz>
Or could the issue be with your certificate?
|
|
|
|
| Re: Error in PL/SQL Procedure using UTL_HTTP [message #607254 is a reply to message #607250] |
Tue, 04 February 2014 04:41 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Sorry, I just ran it with serveroutout on and I get exaclt your result:orclz> set serverout on
orclz> execute expert_http
http://www.experttexting.com/exptapi/exptsms.asmx/SendSMS?Userid=MAC&pwd=1833334&APIKEY=efff50e05dc145&FROM=DEFAULT&To=9
6599464527&MSG=This is a Test mail
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN""http://www.w3.org/TR/html4/strict.dtd">
<HTML><HEAD><TITLE>Bad Request</TITLE>
<META HTTP-EQUIV="Content-Type" Content="text/html; charset=us-ascii"></HEAD>
<BODY><h2>Bad Request</h2>
<hr><p>HTTP Error 400. The request is badly formed.</p>
</BODY></HTML>
PL/SQL procedure successfully completed. orclz> set serverout on
orclz> execute expert_http
http://www.experttexting.com/exptapi/exptsms.asmx/SendSMS?Userid=MAC&pwd=1833334&APIKEY=efff50e05dc145&FROM=DEFAULT&To=9
6599464527&MSG=TestMail
BEGIN expert_http; END;
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1367
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.EXPERT_HTTP", line 13
ORA-06512: at line 1
orclz>
orclz>
|
|
|
|
|
|
|
|
| Re: Error in PL/SQL Procedure using UTL_HTTP [message #607679 is a reply to message #607258] |
Tue, 11 February 2014 01:21 |
sarfraz_attari
Messages: 123
Registered: July 2005
|
Senior Member |
|
|
I am still waiting for the solution of this problem
when I modified the code of my PL/SQL with this
[Code]
PROCEDURE expert_http IS
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
name VARCHAR2(1024);
value VARCHAR2(1024);
url VARCHAR2(4000);
OPT varchar2(1000);
--CURSOR MOB IS SELECT '96566466352' X FROM daul;
BEGIN
utl_http.set_response_error_check(FALSE);
-- UTL_HTTP.SET_PROXY('proxy.my-company.com', 'corp.my-company.com');
--FOR I IN 1..1
--LOOP
--DBMS_OUTPUT.PUT_LINE(I.MOB);
URL := ' https://www.experttexting.com/exptapi/exptsms.asmx/SendSMS?Userid=MAC&pwd=1833334&APIKEY=efff50e05dc145&FROM=DEFAULT& To=96566466352&MSG=ThisisaTestmail' ;
--URL := 'https://www.experttexting.com/exptapi/exptsms.asmx/SendSMS?Userid=MAC '||'&'||'pwd=1833334'||'&'||'APIKEY=efff50e05dc145'||'&'||'FROM=DEFAULT'||'&'||'To=96566466352'||'&'||'MSG=Thisis aTestmail' ;
--URL := 'https://www.experttexting.com/exptapi/exptsms.asmx/SendSMS?Userid=MAC'||'&'||'pwd=1833334'||'&'||'APIKEY=efff50e05dc145'||'&'||'FROM=DEFAULT'||'&'||'To=96599464527'||'&'||'MSG=This is a Test mail' ;
--URL:='http://www.meru.co.in/wip/sendsms?username=11'||'&'||'password=22'||'&'||'to='||I.MOB||'&'||'message=TEST';
DBMS_OUTPUT.PUT_LINE(URL);
req := UTL_HTTP.begin_REQUEST(url);
UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
resp := UTL_HTTP.GET_RESPONSE(req);
dbms_output.put_line('HTTP response status code: ' || resp.status_code);
dbms_output.put_line('HTTP response reason phrase: ' || resp.reason_phrase);
FOR i IN 1..utl_http.get_header_count(resp) LOOP
utl_http.get_header(resp, i, name, value);
dbms_output.put_line(name || ': ' || value);
END LOOP;
begin
LOOP
-- dbms_output.put_line('HTTP response status code: ' || resp.status_code);
-- dbms_output.put_line('HTTP response reason phrase: ' || resp.reason_phrase);
UTL_HTTP.READ_LINE(resp, value, TRUE);
DBMS_OUTPUT.PUT_LINE('Hi'||value);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
-- UTL_HTTP.END_RESPONSE(resp);
end ;
--END LOOP;
END;
[Code]
Now Following message is appearing after running the procedure
SQL> execute expert_http
https://www.experttexting.com/exptapi/exptsms.asmx/SendSMS?Userid=MAC&pwd=183333
4&APIKEY=efff50e05dc145&FROM=DEFAULT&To=96566466352&MSG=ThisisaTestmail
HTTP response status code: 301
HTTP response reason phrase: Moved Permanently
Content-Type: text/html; charset=UTF-8
Location:
https://www.experttexting.com/exptapi/exptsms.asmx/SendSMS?Userid=MAC&pwd=183333
4&APIKEY=efff50e05dc145&FROM=DEFAULT&To=96566466352&MSG=ThisisaTestmail
Server: Microsoft-IIS/7.5
X-Powered-By: ASP.NET
Date: Tue, 11 Feb 2014 07:12:10 GMT
Connection: close
Content-Length: 294
Hi<head><title>Document Moved</title></head>
Hi<body><h1>Object Moved</h1>This document may be found <a
HREF="https://www.experttexting.com/exptapi/exptsms.asmx/SendSMS?Userid=MAC&
pwd=1833334&APIKEY=efff50e05dc145&FROM=DEFAULT&To=96566466352&MS
G=ThisisaTestmail">here</a></body>
PL/SQL procedure successfully completed.
Can you help me to sort out this issue
Thanks and regards
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
