{"id":45,"date":"2012-05-17T09:07:00","date_gmt":"2012-05-17T07:07:00","guid":{"rendered":"https:\/\/ilmarkerm.eu\/blog\/2012\/05\/calling-java-procedure-from-database\/"},"modified":"2017-11-13T18:37:55","modified_gmt":"2017-11-13T17:37:55","slug":"calling-java-procedure-from-database","status":"publish","type":"post","link":"https:\/\/ilmarkerm.eu\/blog\/2012\/05\/calling-java-procedure-from-database\/","title":{"rendered":"Calling Java procedure from database"},"content":{"rendered":"<p>Oracle Database has had the possibility to run Java code inside the database for a long time. It&#8217;s a very rare occasion when you need to use it but still. Here is one example I used to download content from HTTPS website that required user certificates for authentication. Please take the code below more as an example how to put simple Java code inside the database, not as a solution for user certificates authentication, because UTL_HTTP can do the same thing (although I wasn&#8217;t successful in implementing it under 11.2.0.2).<\/p>\n<p>First, load the Java source into database. The code below shows:<\/p>\n<ul>\n<li>How to return simple datatype (int) from Java function &#8211; <b>makeConnection<\/b><\/li>\n<li>How to return Oracle CLOB datatype from Java &#8211; <b>makeConnectionClob<\/b><\/li>\n<li>How to execute SQL from Java, in the same calling session<\/li>\n<\/ul>\n<p>Note that method <b>main<\/b> is just added for testing from command line.<\/p>\n<p><script src=\"https:\/\/gist.github.com\/ilmarkerm\/37cd08ec6240aaba18f7c14cbf43a0df.js\"><\/script><\/p>\n<p>Then you need to create a wrapper package in database. This declares the PL\/SQL wrapper function names and input\/output parameters.<\/p>\n<p><script src=\"https:\/\/gist.github.com\/ilmarkerm\/0ff046ef284ba3aa1b9cb894f2dda4f1.js\"><\/script><\/p>\n<p>Download the source: <a href=\"http:\/\/content.wuala.com\/contents\/ilmarkerm\/Database\/Oracle\/Java\/Java%20source%20in%20DB.java\">java_source.java<\/a> and <a href=\"http:\/\/content.wuala.com\/contents\/ilmarkerm\/Database\/Oracle\/Java\/PLSQL%20wrapper%20for%20calling%20Java%20procedure.pks\">PL\/SQL wrapper.sql<\/a>.<\/p>\n<p>When you first execute the code, you will most likely get some privilege errors, but the error message will tell you how to grant the needed privileges. For example, for this code the following grants were needed:<\/p>\n<pre>\nexec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStore', 'write' );\nexec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.trustStore', 'write' );\nexec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStorePassword', 'write' );\nexec dbms_java.grant_permission( 'OWNER', 'SYS:java.net.SocketPermission', 'site.that.requires.user.cert', 'resolve' );\nexec dbms_java.grant_permission( 'OWNER', 'SYS:java.net.SocketPermission', '1.2.3.4:443', 'connect,resolve' );\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Oracle Database has had the possibility to run Java code inside the database for a long time. It&#8217;s a very rare occasion when you need to use it but still. Here is one example I used to download content from HTTPS website that required user certificates for authentication. Please take the code below more as [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[16,4,13],"class_list":["post-45","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-java","tag-oracle","tag-plsql"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/45","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=45"}],"version-history":[{"count":2,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/45\/revisions"}],"predecessor-version":[{"id":403,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/45\/revisions\/403"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=45"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=45"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=45"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}