Home » SQL & PL/SQL » SQL & PL/SQL » SENDGRID | Curl (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production)
| SENDGRID | Curl [message #685354] |
Thu, 16 December 2021 00:48  |
 |
vippysharma
Messages: 73
Registered: May 2013
Location: www
|
Member |
|
|
Hi All,
I need to call Send Grid API through PLSQL but could not able to find any lead.
While debugging I found that we need to call Send Grid through Curl using below syntax...
curl --request POST \
--url https://api.sendgrid.com/v3/mail/send \
--header 'Authorization: Bearer YOUR_API_KEY' \
--header 'Content-Type: application/json' \
--data '{"personalizations": [{"to": [{"email": "recipient@example.com"}]}],"from": {"email": "sendeexampexample@example.com"},"subject": "Hello, World!","content": [{"type": "text/plain", "value": "Heya!"}]}'
How can I make this happen using PLSQL ? Is there any way to call ? or can I call it through ULT_HTTP ?
Appreciate any lead to this context.
thanks,
|
|
|
|
|
|
| Re: SENDGRID | Curl [message #685386 is a reply to message #685364] |
Mon, 20 December 2021 03:41  |
|
vippysharma
Messages: 73
Registered: May 2013
Location: www
|
Member |
|
|
I am getting below error msg while calling SENDGRID API using UTL_HTTP
Quote:
{"errors":[{"message":"Bad Request","field":null,"help":null}]}
-- below string (as varchar2) I am passing via utl_http.write_text against the request.
{"personalizations":[{"to":[{"email":"myname@xyz.com","name":"myname"}],"subject":"Subject is test email"}],"content": [{"type": "text/plain", "value": "Test body"}],"from":{"email":"Gaur@xyz.com","name":"Gaur"},"reply_to":{"email":"myname@xyz.com"}}
|
|
|
|
|
|
|
|
| Re: SENDGRID | Curl [message #685389 is a reply to message #685388] |
Mon, 20 December 2021 05:21 |
|
vippysharma
Messages: 73
Registered: May 2013
Location: www
|
Member |
|
|
Below is the code and O/P
create or replace procedure vip_mail_service
is
req utl_http.req;
res utl_http.resp;
url varchar2(4000) := 'https://api.sendgrid.com/v3/mail/send';
name varchar2(4000);
buffer varchar2(4000);
content NCLOB := '{"personalizations":[{"to":[{"email":"myname@xyz.com","name":"myname"}],"subject":"Hello, World!"}],"content": [{"type": "text/plain", "value": "Heya!"}],"from":{"email":"Gaur@xyz.com","name":"Gaur"},"reply_to":{"email":"myname@xyz.com","name":"myname"}}';
v_ErrorMsg VARCHAR2(4000);
v_ErrorLine VARCHAR2(4000);
begin
UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/DBNAME/wallet', '******');
begin
req := utl_http.begin_request(url, 'POST',NULL,NULL,'*.api.sendgrid.com');
utl_http.set_header(req, 'Authorization', 'Bearer ****************KEY*********************');
utl_http.set_header(req, 'content-Type', 'application/json');
begin
utl_http.write_text(req,content);
exception
when others then
v_ErrorLine := DBMS_UTILITY.format_error_backtrace;
v_ErrorMsg := v_ErrorMsg||SQLERRM ||CHR(13)||SQLCODE||CHR(13)|| v_ErrorLine;
dbms_output.put_line(v_ErrorLine||' || '||v_ErrorMsg);
end;
begin
res := utl_http.get_response(req);
exception
when others then
v_ErrorLine := DBMS_UTILITY.format_error_backtrace;
v_ErrorMsg := v_ErrorMsg||SQLERRM ||CHR(13)||SQLCODE||CHR(13)|| v_ErrorLine;
dbms_output.put_line(v_ErrorLine||' || '||v_ErrorMsg);
end;
-- process the response from the HTTP call
begin
loop
utl_http.read_line(res, buffer,true);
dbms_output.put_line('buffer value is- '||buffer);
end loop;
utl_http.end_response(res);
exception
when utl_http.end_of_body
then
utl_http.end_response(res);
end;
end;
dbms_output.put_line ('-------Completed successfully-------------------');
exception
when others then
RAISE
end vip_mail_service;
/
O/P :
buffer value is- {"errors":[{"message":"Bad Request","field":null,"help":null}]}
-------Completed successfully-------------------
|
|
|
|
| Re: SENDGRID | Curl [message #685390 is a reply to message #685389] |
Mon, 20 December 2021 05:51 |
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
The code you posted doesn't compile, because you have missed a semi-colon after RAISE. You should test things before posting them.
Then if you remove those stupid EXCEPTION clauses that are hiding the error, you may be able to debug it. I get this:orclz> set serverout on
orclz> exec vip_mail_service;
buffer value is- <html>
buffer value is- <head>
buffer value is- <title>SendGrid - 400 Bad Request</title>
buffer value is- <style>@import url("https://fonts.googleapis.com/css?family=Dosis:300,400,700,800");/** Styles for the 403 Page
**/.particle-error,.permission_denied,#particles-js { width: 100%; height: 100%; margin: 0px !important;}#particles-js { position:
fixed !important; opacity: 0.23;}.permission_denied { background: #294661 !important;}.permission_denied a { text-decoration:
none;}.denied__wrapper { max-width: 390px; width: 100%; height: 390px; display: block; margin: 0 auto; position: relative;
margin-top: 8vh;}.permission_denied h1 { text-align: center; color: #fff; font-family: "Dosis", sans-serif; font-size: 100px;
margin-bottom: 0px; font-weight: 800;}.permission_denied h3 { text-align: center; color: #fff; font-size: 19px; line-height:
23px; max-width: 330px; margin: 0px auto 30px auto; font-family: "Dosis", sans-serif; font-weight: 400;}.permission_denied h3 span
{ position: relative; width: 65px; display: inline-block;}.permission_denied h3 span:after { content: ""; border-bottom: 3px
solid #e12127; position: absolute; left: 0; top: 43%; width: 100%;}.denied__link { background: none; color: #fff; padding: 12px
0px 10px 0px; border: 1px solid #fff; outline: none; border-radius: 7px; width: 150px; width: 150px; font-size: 15px;
text-align: center; margin: 0 auto; vertical-align: middle; display: block; margin-bottom: 40px; margin-top: 25px; font-family:
"Dosis", sans-serif; font-weight: 400;}.denied__link:hover { color: #e12127; border-color: #e12127; cursor: pointer; opacity:
1;}.permission_denied .stars { animation: sparkle 1.6s infinite ease-in-out alternate;}@keyframes sparkle { 0% { opacity: 1; }
100% { opacity: 0.3; }}#astronaut { width: 43px; position: absolute; right: 20px; top: 210px; animation: spin 4.5s infinite
linear;}@keyframes spin { 0% { transform: rotateZ(0deg); } 100% { transform: rotateZ(360deg); }}@media (max-width: 600px) {
.permission_denied h1 { font-size: 75px; } .permission_denied h3 { font-size: 16px; width: 200px; margin: 0 auto;
line-height: 23px; } .permission_denied h3 span { width: 60px; } #astronaut { width: 35px; right: 40px; top: 170px;
}}.saturn,.saturn-2,.hover { animation: hover 2s infinite ease-in-out alternate;}@keyframes hover { 0% { transform:
translateY(3px); } 100% { transform: translateY(-3px); }
buffer value is- } </style>
buffer value is- </head>
buffer value is- <body class="permission_denied">
buffer value is- <div id="particles-js"></div>
buffer value is- <div class="denied__wrapper">
buffer value is- <h1>400</h1>
buffer value is- <h3>Nginx - Bad Request</h3>
buffer value is- <svg id="astronaut" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 50 50">
buffer value is- <g id="tiara-logo" transform="translate(8.000000, 8.000000)">
buffer value is- <rect id="99E1F4" fill="#99E1F4" x="8" y="16" width="8" height="8"></rect>
buffer value is- <rect id="99E1F4" fill="#99E1F4" x="0" y="8" width="8" height="8"></rect>
buffer value is- <rect id="009DD9" fill="#009DD9" x="8" y="8" width="8" height="8"></rect>
buffer value is- <rect id="00B3E3" fill="#00B3E3" x="16" y="8" width="8" height="8"></rect>
buffer value is- <rect id="00B3E3" fill="#00B3E3" x="8" y="0" width="8" height="8"></rect>
buffer value is- <rect id="1a82e2" fill="#1A82E2" x="0" y="16" width="8" height="8"></rect>
buffer value is- <rect id="1a82e2" fill="#1A82E2" x="16" y="0" width="8" height="8"></rect>
buffer value is- </g>
buffer value is- </svg>
buffer value is- <svg id="planet" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512">
buffer value is- <circle class="saturn" cx="256" cy="256" r="149.536" fill="#FFF"/>
buffer value is- <circle class="saturn" cx="256" cy="256" r="138" fill="#e12127"/>
buffer value is- <circle class="saturn" cx="256" cy="256" r="108" fill="#FFF"/>
buffer value is- <circle class="hover" cx="216" cy="214.081" r="32" fill="#e12127"/>
buffer value is- <circle class="hover" cx="298" cy="214.081" r="32" fill="#e12127"/>
buffer value is- <circle class="hover" cx="216" cy="296.081" r="32" fill="#e12127"/>
buffer value is- <circle class="hover" cx="298" cy="296.081" r="32" fill="#e12127"/>
buffer value is- <path class="stars" fill="#FFF" d="M112.456 363.093c-.056 7.866-6.478 14.197-14.344 14.142 7.866.056 14.198
6.48 14.142 14.345.056-7.866 6.48-14.198 14.345-14.142-7.868-.057-14.2-6.48-14.144-14.345zM432.436 274.908c-.056 7.866-6.478
14.198-14.344 14.142 7.866.057 14.197 6.48 14.142 14.345.056-7.866 6.48-14.197
14.345-14.142-7.868-.056-14.2-6.48-14.144-14.345zM159.75 58.352c-.12 16.537-13.62 29.848-30.157 29.73 16.537.118 29.848 13.62 29.73
30.156.118-16.537 13.62-29.848 30.156-29.73-16.54-.117-29.85-13.62-29.73-30.156z"/>
buffer value is- </svg>
buffer value is- <a href="https://sendgrid.com"><button class="denied__link">Return to Homepage</button></a>
buffer value is- </div>
buffer value is- </body>
buffer value is- <script src="https://cdn.jsdelivr.net/particles.js/2.0.0/particles.min.js"></script>
buffer value is- <script>var
particles={"particles":{"number":{"value":160,"density":{"enable":true,"value_area":800}},"color":{"value":"#ffffff"},"shape":{"type":
"circle","stroke":{"width":0,"color":"#000000"},"polygon":{"nb_sides":5},"image":{"src":"img/github.svg","width":100,"height":100}},"o
pacity":{"value":1,"random":true,"anim":{"enable":true,"speed":1,"opacity_min":0,"sync":false}},"size":{"value":3,"random":true,"anim"
:{"enable":false,"speed":4,"size_min":0.3,"sync":false}},"line_linked":{"enable":false,"distance":150,"color":"#ffffff","opacity":0.4,
"width":1},"move":{"enable":true,"speed":0.17,"direction":"none","random":true,"straight":false,"out_mode":"out","bounce":false,"attra
ct":{"enable":false,"rotateX":600,"rotateY":600}}},"interactivity":{"detect_on":"canvas","events":{"onhover":{"enable":false,"mode":"b
ubble"},"onclick":{"enable":false,"mode":"repulse"},"resize":false},"modes":{"grab":{"distance":400,"line_linked":{"opacity":1}},"bubb
le":{"distance":250,"size":0,"duration":2,"opacity":0,"speed":3},"repulse":{"distance":400,"duration":0.4},"push":{"particles_nb":4},"
remove":{"particles_nb":2}}},"retina_detect":true};particlesJS('particles-js',particles,function(){console.log('callback-particles.jsc
onfigloaded');});</script>
buffer value is- </html>
-------Completed successfully-------------------
PL/SQL procedure successfully completed.
orclz> |
|
|
|
| Re: SENDGRID | Curl [message #685391 is a reply to message #685390] |
Mon, 20 December 2021 06:21 |
|
vippysharma
Messages: 73
Registered: May 2013
Location: www
|
Member |
|
|
I removed all exception handlings and still getting same error.. below updated code and o/p
create or replace procedure vip_mail_service is
req utl_http.req;
res utl_http.resp;
url varchar2(4000) := 'https://api.sendgrid.com/v3/mail/send';
name varchar2(4000);
buffer varchar2(4000);
content varchar2(4000) := '{"personalizations":[{"to":[{"email":"myname@xyz.com","name":"myname"}],"subject":"Hello, World!"}],"content": [{"type": "text/plain", "value": "Heya!"}],"from":{"email":"Gaur@xyz.com","name":"Gaur"},"reply_to":{"email":"myname@xyz.com","name":"myname"}}';
begin
UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/DBNAME/wallet', '******');
req := utl_http.begin_request(url, 'POST',NULL,NULL,'*.api.sendgrid.com');
utl_http.set_header(req, 'Authorization', 'Bearer ****************KEY*********************');
utl_http.set_header(req, 'content-Type', 'application/json');
utl_http.write_text(req,content);
res := utl_http.get_response(req);
-- process the response from the HTTP call
begin
loop
utl_http.read_line(res, buffer,true);
dbms_output.put_line('buffer value is- '||buffer);
end loop;
utl_http.end_response(res);
exception
when utl_http.end_of_body
then
utl_http.end_response(res);
end;
dbms_output.put_line ('-------Completed successfully-------------------');
exception
when others then
raise;
end vip_mail_service;
/
Output:
SQL>
SQL>
SQL> exec vip_mail_service;
buffer value is- {"errors":[{"message":"Bad Request","field":null,"help":null}]}
-------Completed successfully-------------------
PL/SQL procedure successfully completed.
|
|
|
|
| Re: SENDGRID | Curl [message #685392 is a reply to message #685391] |
Mon, 20 December 2021 06:25 |
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You have not removed your exception clauses. They are still right there. This looks like trolling: being deliberately stupid in an attempt to make people angry.
Goodbye.
|
|
|
|
 Re: SENDGRID | Curl [message #685395 is a reply to message #685392] |
Tue, 21 December 2021 01:53 |
|
vippysharma
Messages: 73
Registered: May 2013
Location: www
|
Member |
|
|
Mr. Watson, no one is trolling anyone here, and no one deliberately asking anything stupid.
request you not to be quick judgmental about other's knowledge. And please don't be angry (don't let these small things drive your emotions) , if you don't like anything - please ignore and move on to another topic...
hoping others can help 
let's continue with this topic..
-- utl_http.end_of_body : this exception I thought should be there for handling buffer termination, I was wrong so I excluded this exception now from my code...
-- Below is the updated code and o/p after executing
create or replace procedure vip_mail_service is
req utl_http.req;
res utl_http.resp;
url varchar2(4000) := 'https://api.sendgrid.com/v3/mail/send';
name varchar2(4000);
buffer varchar2(4000);
DETAIL_ERR VARCHAR2(4000);
content varchar2(4000) := '{"personalizations":[{"to":[{"email":"myname@xyz.com","name":"myname"}],"subject":"Hello, World!"}],"content": [{"type": "text/plain", "value": "Heya!"}],"from":{"email":"Gaur@xyz.com","name":"Gaur"},"reply_to":{"email":"myname@xyz.com","name":"myname"}}';
begin
UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/DBNAME/wallet', '******');
req := utl_http.begin_request(url, 'POST',NULL,NULL,'*.api.sendgrid.com');
utl_http.set_header(req, 'Authorization', 'Bearer ****************KEY*********************');
utl_http.set_header(req, 'content-Type', 'application/json');
utl_http.write_text(req,content);
res := utl_http.get_response(req);
-- process the response from the HTTP call
begin
loop
utl_http.read_line(res, buffer,true);
dbms_output.put_line('buffer value is- '||buffer);
end loop;
utl_http.end_response(res);
end;
dbms_output.put_line ('-------Completed successfully-------------------');
exception
when others then
DETAIL_ERR:=utl_http.get_detailed_sqlerrm;
dbms_output.put_line('DETAIL ERROR IS:'|| DETAIL_ERR);
raise;
end vip_mail_service;
/
O/P -
SQL> exec vip_mail_service;
buffer value is- {"errors":[{"message":"Bad Request","field":null,"help":null}]}
DETAIL ERROR IS:ORA-29266: end-of-body reached
BEGIN vip_mail_service; END;
*
ERROR at line 1:
ORA-29266: end-of-body reached
ORA-06512: at "SYS.VIP_MAIL_SERVICE", line 35
ORA-06512: at "SYS.UTL_HTTP", line 836
ORA-06512: at "SYS.UTL_HTTP", line 1328
ORA-06512: at "SYS.VIP_MAIL_SERVICE", line 24
ORA-06512: at line 1
|
|
|
|
| Re: SENDGRID | Curl [message #686200 is a reply to message #685395] |
Thu, 30 June 2022 04:36 |
thiyagarajancs
Messages: 3
Registered: September 2009
Location: chennai
|
Junior Member |
 
|
|
Dears,
I am facing an issue while configuring the email via sendgrid. Kindly help me to resolve the issue.
###########################################Sample Procedure############################################################################
create or replace procedure vip_mail_service is
req utl_http.req;
res utl_http.resp;
url varchar2(4000) := 'https://api.sendgrid.com/v3/mail/send';
name varchar2(4000);
buffer varchar2(4000);
content varchar2(4000) := '{"personalizations":[{"to":[{"email":"test@mydomain.in","name":"myname"}],"subject":"Hello, World!"}],"content": [{"type": "text/plain", "value": "Heya!"}],"from":{"email":"test@mydomain.in","name":"Gaur"},"reply_to":{"email":"test@mydomain.in","name":"myname"}}';
begin
UTL_HTTP.set_wallet('file:C:\app\product\18.0.0\ADMIN\XE\WALLET', 'wallet_pwd');
req := utl_http.begin_request(url, 'POST',NULL,NULL,'*.api.sendgrid.com');
utl_http.set_header(req, 'Authorization', 'Bearer *****key**********');
utl_http.set_header(req, 'content-Type', 'application/json');
utl_http.write_text(req,content);
res := utl_http.get_response(req);
-- process the response from the HTTP call
begin
loop
utl_http.read_line(res, buffer,true);
dbms_output.put_line('buffer value is- '||buffer);
end loop;
utl_http.end_response(res);
exception
when utl_http.end_of_body
then
utl_http.end_response(res);
end;
dbms_output.put_line ('-------Completed successfully-------------------');
end vip_mail_service;
/
############################################################################################################################
set serverout on
exec VIP_MAIL_SERVICE
Output:
BEGIN VIP_MAIL_SERVICE; END;
Error report -
ORA-29273: HTTP request failed
ORA-28788: user provided invalid information, or an unknown error
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at "TEST.VIP_MAIL_SERVICE", line 11
ORA-06512: at line 1
29273. 00000 - "HTTP request failed"
*Cause: The UTL_HTTP package failed to execute the HTTP request.
*Action: Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.
But, I am getting response for the below query. I assume there is no certificate level issues. Am i correct ?
Query:-
select utl_http.request('https://api.sendgrid.com/v3/mail/send', NULL,'file:C:\APP\PRODUCT\18.0.0\ADMIN\XE\WALLET', 'wallet pwd') from dual;
Output:-
{"errors":[{"message":"POST method allowed only","field":null,"help":null}]}
|
|
|
|
Goto Forum:
Current Time: Tue Mar 28 23:49:05 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
