Oracle SQL | Tag Archives: certificates

I don’t like to import certificates, so i cannot use httpuritype for HTTPS pages and I decided to create package which will work with https as http.
It was pretty easy with java stored procedures 🙂
github/XT_HTTP

java source: xt_http.jsp

[sourcecode language=”sql”]
create or replace and compile java source named xt_http as
package org.orasql.xt_http;

import javax.net.ssl.HttpsURLConnection;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.HttpURLConnection;

import java.sql.Connection;
import oracle.jdbc.driver.*;
import oracle.sql.CLOB;

public class XT_HTTP {

/**
* Function getPage
* @param String Page URL
* @return String
*/
public static CLOB getPage(java.lang.String sURL)
throws java.sql.SQLException
{
OracleDriver driver = new OracleDriver();
Connection conn = driver.defaultConnection();
CLOB result = CLOB.createTemporary(conn, false, CLOB.DURATION_CALL);
result.setString(1," ");
try {
URL url = new URL(sURL);
HttpURLConnection con = (HttpURLConnection)url.openConnection();
//HttpsURLConnection con = (HttpsURLConnection)url.openConnection();
if(con!=null){
BufferedReader br =
new BufferedReader(
new InputStreamReader(con.getInputStream()));
StringBuilder sb = new StringBuilder();
String line;
while ((line = br.readLine()) != null){
sb.append(line);
}
br.close();
result.setString(1,sb.toString());
}
} catch (MalformedURLException e) {
result.setString(1, e.getMessage());
} catch (IOException e) {
result.setString(1, e.getMessage());
}
return result;
}

public static java.lang.String getString(java.lang.String sURL) {
String result="";
try {
URL url = new URL(sURL);
HttpURLConnection con = (HttpURLConnection)url.openConnection();
if(con!=null){
BufferedReader br =
new BufferedReader(
new InputStreamReader(con.getInputStream()));
StringBuilder sb = new StringBuilder();
String line;
while ((line = br.readLine()) != null){
sb.append(line);
}
br.close();
result = sb.toString().substring(0,3999);
}
} catch (MalformedURLException e) {
return e.getMessage();
} catch (IOException e) {
return e.getMessage();
}
return result;
}
}
/
[/sourcecode]

[collapse]

package xt_http

[sourcecode language=”sql”]
create or replace package XT_HTTP is
/**
* Get page as CLOB
*/
function get_page(pURL varchar2)
return clob
IS LANGUAGE JAVA
name ‘org.orasql.xt_http.XT_HTTP.getPage(java.lang.String) return oracle.sql.CLOB’;

/**
* Get page as varchar2(max=4000 chars)
*/
function get_string(pURL varchar2)
return varchar2
IS LANGUAGE JAVA
name ‘org.orasql.xt_http.XT_HTTP.getString(java.lang.String) return java.lang.String’;

end XT_HTTP;
/
[/sourcecode]

[collapse]

We have to grant connection permissions:

dbms_java.grant_permission(
   grantee           => 'XTENDER'                       -- username
 , permission_type   => 'SYS:java.net.SocketPermission' -- connection permission
 , permission_name   => 'ya.ru:443'                     -- connection address and port
 , permission_action => 'connect,resolve'               -- types
);

And now we can easily get any page:

USAGE example:
declare
  c clob;
  s varchar2(8000);
begin
  --- Through HTTPS as CLOB:
  c:=xt_http.get_page('https://google.com');

  --- Through HTTP as CLOB
  c:=xt_http.get_page('http://ya.ru');
  
  --- Through HTTPS as varchar2:
  s:=xt_http.get_string('https://google.com');

  --- Through HTTP as varchar2
  s:=xt_http.get_string('http://ya.ru');
end;
/
select length( xt_http.get_page('https://google.com') ) page_size from dual