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 Go to next message
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!"}]}'
Now my question is..

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 #685364 is a reply to message #685354] Fri, 17 December 2021 01:09 Go to previous messageGo to next message
John Watson
Messages: 8738
Registered: January 2010
Location: Global Village
Senior Member
You can do all that with utl_http no problem.
Re: SENDGRID | Curl [message #685386 is a reply to message #685364] Mon, 20 December 2021 03:41 Go to previous messageGo to next message
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 #685387 is a reply to message #685386] Mon, 20 December 2021 03:51 Go to previous messageGo to next message
vippysharma
Messages: 73
Registered: May 2013
Location: www
Member
and this same string is working fine in Curl...
Re: SENDGRID | Curl [message #685388 is a reply to message #685387] Mon, 20 December 2021 04:44 Go to previous messageGo to next message
John Watson
Messages: 8738
Registered: January 2010
Location: Global Village
Senior Member
It is hard to debug code that one has not seen.
Re: SENDGRID | Curl [message #685389 is a reply to message #685388] Mon, 20 December 2021 05:21 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8738
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 Go to previous messageGo to next message
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.

is there anything I need to look into ?
Re: SENDGRID | Curl [message #685392 is a reply to message #685391] Mon, 20 December 2021 06:25 Go to previous messageGo to next message
John Watson
Messages: 8738
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.
icon1.gif  Re: SENDGRID | Curl [message #685395 is a reply to message #685392] Tue, 21 December 2021 01:53 Go to previous messageGo to next message
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 Smile

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 Go to previous message
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}]}

Previous Topic: BASIC SYNTAX / Dynamic Pivot Table (merged)
Next Topic: Min and Max values of contiguous rows (3 merged)
Goto Forum:
  


Current Time: Fri Aug 19 23:50:53 CDT 2022