Oracle SQL

    Tag Archives: certificates

    Very simple oracle package for HTTPS and HTTP

    Posted on October 9, 2015 by Sayan Malakshinov Posted in java stored procedures, oracle, PL/SQL 2 Comments

    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

    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;
        }
    }
    /
    

    [collapse]

    package xt_http

    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;
    /
    

    [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
    
    certificates HTTP HTTPS httpuritype SSL

    Simple Android Oracle client

    Get it on Google Play

    About us

    photo Sayan Malakshinov

    Oracle ACE Associate Oracle ACE Associate
    Oracle performance tuning expert
    Russia / Moscow / Transmedia Dynamics
    photo Bair Malakshinov

    Ph.d candidate
    Senior Oracle Developer
    Poland / Cracow / Luxoft

    Popular Posts

    • Differences between integer(int) in SQL and PL/SQL 0 comments
    • Deterministic function vs scalar subquery caching. Part 1 8 comments
    • Amazing optimization of getting distinct values from the index, and TopN for each of them 4 comments
    • SQL*Plus tips #6: Colorizing output 4 comments
    • SQL*Plus tips #5: sql_text/sql_fulltext formatting(sql beatifier) 13 comments
    • SQL*Plus tips. #1 5 comments
    • A couple of well-known but often forgotten things for PL/SQL developers 2 comments
    • SYS_OP_MAP_NONNULL is in the documentation now 0 comments
    • SQL*Plus tips #4: Branching execution 0 comments
    • Oracle 12c: Lateral, row_limiting_clause 3 comments

    Recent Posts

    • Top-N again: fetch first N rows only vs rownum
    • Docker with Oracle database: install patches automatically
    • Top N biggest tables (with lobs, indexes and nested table)
    • “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”

    Email Subscription

    Recent Comments

    • SQL*Plus 256 Colours in terminal | EDUARDO CLARO on SQL*Plus tips #6: Colorizing output
    • A simple SQL*Plus parameter parser | EDUARDO CLARO on SQL*Plus tips. #1
    • Sayan Malakshinov on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • Frits on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without 'serveroutput on' - SSWUG.ORG on SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”
    • Adaptive serial direct path read decision ignores object statistics since 12.1 - SSWUG.ORG on Adaptive serial direct path read decision ignores object statistics since 12.1
    • Oracle issues after upgrade to 12.2 - SSWUG.ORG on Oracle issues after upgrade to 12.2
    • Ampersand instead of colon for bind variables - SSWUG.ORG on Ampersand instead of colon for bind variables
    • Евгений Бабин on Oracle issues after upgrade to 12.2
    • Oracle SQL | How even empty trigger increases redo generation on Triggers and Redo: changes on 12.2

    Blogroll

    • Alex Fatkulin
    • Alexander Anokhin
    • Andrey Nikolaev
    • Charles Hooper
    • Christian Antognini
    • Coskan Gundogar
    • David Fitzjarrell
    • Igor Usoltsev
    • Jonathan Lewis
    • Karl Arao
    • Mark Bobak
    • Martin Bach
    • Martin Berger
    • Neil Chandler
    • Randolf Geist
    • Richard Foote
    • Riyaj Shamsudeen
    • Tanel Poder
    • Timur Akhmadeev
    • Valentin Nikotin

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

    • Log in
    • Entries RSS
    • Comments RSS
    • WordPress.org
    ©Sayan Malakshinov. Oracle SQL