{"id":271,"date":"2019-09-04T16:56:39","date_gmt":"2019-09-04T14:56:39","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/?p=271"},"modified":"2020-07-09T09:48:59","modified_gmt":"2020-07-09T07:48:59","slug":"make-a-rest-call-with-json-content-to-a-https-webservice-from-pl-sql","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2019\/09\/04\/make-a-rest-call-with-json-content-to-a-https-webservice-from-pl-sql\/","title":{"rendered":"Make a REST call with JSON content to a HTTPS webservice from PL\/SQL"},"content":{"rendered":"\n

All keywords are now in the title. \ud83d\ude42<\/p>\n\n\n\n

I’ve started developing this small PL\/SQL procedure based on Lucas Jelema blog entry Invoke a REST service from PL\/SQL \u2013 make an HTTP POST request using UTL_HTTP in Oracle Database 11g XE<\/a> .<\/p>\n\n\n\n

Unfortunately it took me a while to find out why I was getting the error:<\/p>\n\n\n

\n&lt;!DOCTYPE HTML PUBLIC "-\/\/IETF\/\/DTD HTML 2.0\/\/EN">\n&lt;html>&lt;head>\n&lt;title>400 Bad Request&lt;\/title>\n&lt;\/head>&lt;body>\n&lt;h1>Bad Request&lt;\/h1>\n&lt;p>Your browser sent a request that this server could not understand.&lt;br \/>\n&lt;\/p>\n&lt;\/body>&lt;\/html>\n<\/pre><\/div>\n\n\n
There is a empty space at the beginning of a variable in the example on Lucas’ blog post – before the HTTP\/1.1 on the line below:<\/p>\n\n\n\n
  req := utl_http.begin_request(url, 'POST',' HTTP\/1.1'); <\/code><\/p>\n\n\n\n
My need is a bit more complex, so I leave here the complete code.<\/p>\n\n\n\n
Certificate and Oracle wallet<\/h2>\n\n\n\n
First you need to download the certificate. A good tutorial do to this is at Shellhacks.com<\/a>.<\/p>\n\n\n\n
Then you need to create a wallet on the database server and add the certificate:<\/p>\n\n\n
\nmkdir ${ORACLE_BASE}\/admin\/${ORACLE_SID}\/wallet\norapki wallet create -wallet ${ORACLE_BASE}\/admin\/${ORACLE_SID}\/wallet -pwd WalletPassword123 -auto_login\norapki wallet add -wallet ${ORACLE_BASE}\/admin\/${ORACLE_SID}\/wallet -trusted_cert -cert "\/tmp\/my_certificate.crt" -pwd WalletPassword123\n<\/pre><\/div>\n\n\n
Oracle ACLs<\/h2>\n\n\n\n
On the database (my case was Oracle 11.2) you need to have XDB installed and add the ACLs. I skip the instructions to install XDB. The user is called GREEN.<\/p>\n\n\n
\ngrant execute on utl_http to GREEN;\ngrant execute on dbms_lock to GREEN;\n\nBEGIN\n  DBMS_NETWORK_ACL_ADMIN.create_acl (\n    acl          => 'acl_file.xml', \n    description  => 'Allows sending alerts to REST service',\n    principal    => 'GREEN',\n    is_grant     => TRUE, \n    privilege    => 'connect',\n    start_date   => SYSTIMESTAMP,\n    end_date     => NULL);\nend;\n\/\n\nbegin\n  DBMS_NETWORK_ACL_ADMIN.assign_acl (\n    acl         => 'acl_file.xml',\n    host        => 'my_rest_service.com', \n    lower_port  => 443,\n    upper_port  => NULL);    \nend; \n\/\n<\/pre><\/div>\n\n\n
Procedure to invoke REST service<\/h2>\n\n\n\n
The rest service requires a username password. I use the encoded version, so there are no visible passwords in the code. You can encode the pair username:password here<\/a>.<\/p>\n\n\n
\nCREATE OR REPLACE PROCEDURE green.publish_event\n( p_robot in varchar2, p_business_service in varchar2, p_target_ci in varchar2, p_transaction in varchar2, p_status in number, p_message in varchar2) is\n  req utl_http.req;\n  res utl_http.resp;\n  url varchar2(4000) := 'https:\/\/my_rest_service.com\/api\/event';\n  buffer varchar2(32767); \n  content varchar2(8000) := '{ "robot":"'||p_robot||'", "business_service":"'||p_business_service||'", "target_ci":"'||p_target_ci||'", "transaction":"'||p_transaction||'", "status":"'||p_status||'", "message":"'||p_message||'", "target_support_group": "", "organization": "ANJO_EVENT"}';\n  \nBEGIN\n  utl_http.set_wallet('file:${ORACLE_BASE}\/admin\/${ORACLE_SID}\/wallet', null);\n\treq  :=UTL_HTTP.begin_request(url, 'POST');\n\tutl_http.set_header(req, 'Authorization', 'Basic f24iYsBpdGVzdDp3ZWJhcGl0ZXN0'); \n  utl_http.set_header(req, 'Content-Length', length(content));\n  utl_http.write_text(req, content);\n\n  res := utl_http.get_response(req);\n  -- process the response from the HTTP call\n  begin\n    loop\n      utl_http.read_text(res, buffer,32767);\n      dbms_output.put_line(buffer);\n    end loop;\n    utl_http.end_response(res);\n  exception\n    when utl_http.end_of_body \n    then\n      utl_http.end_response(res);\n  end;\nEND publish_event;\n\/\n<\/pre><\/div>\n\n\n
Trigger to automatically send event<\/h2>\n\n\n\n
In my case, the idea is to send an event every time a line is inserted into a table. So I just create a small trigger that calls the procedure above.<\/p>\n\n\n
\nCREATE OR REPLACE TRIGGER green.trg_table1_monitor\nAFTER INSERT\n   ON green.table1_monitor\n   FOR EACH ROW\nBEGIN\n   publish_event (\n\t   p_robot => :new.host_name,\n\t\t p_business_service => 'IT DB',\n\t\t p_target_ci => '356224bs5b1f6f401d2262eb0b9619ad',\n\t\t p_transaction => 'Table1',\n\t\t p_status => 7,\n\t\t p_message => 'Green-Table1 -Exception: '|| substr(:new.exception,0,100) \n\t  );\nEND;\n\/\n<\/pre><\/div>\n\n\n
Et voil\u00e0!<\/p>\n","protected":false},"excerpt":{"rendered":"
All keywords are now in the title. \ud83d\ude42 I’ve started developing this small PL\/SQL procedure based on Lucas Jelema blog entry Invoke a REST service from PL\/SQL \u2013 make an HTTP POST request using UTL_HTTP in Oracle Database 11g XE . Unfortunately it took me a while to find out why I was getting the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,46],"tags":[],"class_list":{"0":"post-271","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-json","7":"category-rest","8":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/271","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=271"}],"version-history":[{"count":2,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/271\/revisions"}],"predecessor-version":[{"id":378,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/271\/revisions\/378"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=271"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=271"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=271"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}