HttpRequest in Oracle SQL über Java Function

Mittel Java kann Oracle nahezu beliebig aufgebohrt werden.

Zunächst eine Klasse zum Laden von Webseiten per URL:

create or replace and compile java source named java_http
as
import java.net.*;
import java.io.*;
public class java_http {
  public static String get(String url) throws Exception {
    URL u = new URL(url);
    URLConnection c = u.openConnection();
    c.setDoOutput(true);
    if (c instanceof HttpURLConnection) {
       ((HttpURLConnection)c).setRequestMethod("GET");
    }
    OutputStreamWriter out = new OutputStreamWriter(
      c.getOutputStream()
    );
    out.close();
    BufferedReader in = new BufferedReader(
      new InputStreamReader(
        c.getInputStream()
      )
    );
    String result = "";
    String s = null;
    while ((s = in.readLine()) != null) {
      result += s;
    }
    in.close();
    return result;
  }
}

Für diese Klasse eine Stored Function in Oracle definieren:

create or replace function javahttpget(p_string in varchar2)
return varchar2
as language java name 'java_http.get(java.lang.String) return java.lang.String';

Testaufruf:

select javahttpget('http://www.oracle.com/') from dual;

Fehler wegen Berechtigung:

ORA-29532: Java-Aufruf durch nicht abgefangene Java-Exception beendet:
java.security.AccessControlException: the Permission (java.net.SocketPermission
www.oracle.com resolve) has not been granted to SCOTT. The PL/SQL to grant this
is dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission','www.oracle.com', 'resolve' )

Berechtigung vergeben:

conn scott as sysdba
exec dbms_java.grant_permission('SCOTT','SYS:java.net.SocketPermission','www.oracle.com','resolve')
exit
select javahttpget('http://www.oracle.com/') from dual;

Oracle Stored Procedure und Function in JAVA

In Oracle können Stored Procedure und Stored Function in Java realisiert werden.
Über diesen Weg lassen sich nahezu alle Fremdsystem in PLSQL einbinden (z.B. Webservices).

Prozedur in Java

Zuerst muß die Javaklasse erstellt werden:

create or replace and compile java source named java_hallowelt
as
import java.util.*;
import java.sql.*;
public class java_hallowelt {
  public static void hallowelt() {
    System.out.println("Java Hallo Welt");
  }
}

Danach kann mit Bezug auf die Javaklasse eine Prozedur definiert werden:

create or replace procedure hallowelt
as language java name 'java_hallowelt.hallowelt()';

Der Aufruf erfolgt mit:

exec hallowelt;

Funktion in Java

create or replace and compile java source named java_gethallo
as
public class java_gethallo {
  public static String gethallo() {
    return "Hallo Welt";
  }
}

Wichtig: an dieser Stelle wird nicht geprüft, ob die Klasse java_gethallo existiert! (Erst zur Laufzeit)

create or replace function gethallo
return varchar2
as language java name 'java_gethallo.gethallo() return java.lang.String';

Der Aufruf liefert „Hallo Welt“.

select gethallo from dual;

Funktionen mit Parametern und Rückgabe

create or replace and compile java source named java_upper
as
public class java_util {
  public static String upper(String p_string) {
    return p_string.toUpperCase();
  }
}

create or replace function javaupper(p_string in varchar2)
return varchar2
as language java name 'java_util.upper(java.lang.String) return java.lang.String';

select javaupper('d') from dual;

PLSQL Oracle Stored Function

Create Function

create or replace function fu_add(p_in in number)
  return number
as
  v_out number;
begin
  v_out := p_in + 1;
  return v_out;
end;

exec dbms_output.put_line(fu_add(10));

select fu_add(10) from dual;

PLSQL Oracle Stored Procedure

Create Procedure

Zum Anlegen oder Ändern einer Procedure wird das Recht „CREATE PROCEDURE“ benötigt.

Anlegen:

create or replace procedure proc_ascii
as
  type t_ascii is varray(256) of char(1);
  a_ascii t_ascii;
begin null;
  a_ascii := t_ascii();
  a_ascii.extend(256);
  for i in 1 .. a_ascii.limit-1 loop
    a_ascii(i) := chr(i-1);
    dbms_output.put_line(i || ' ' || chr(i-1));
  end loop;
end;

Drop Procedure

Das Löschen einer Procedure erfolgt mit „DROP PROCEDURE -name-“.

Aufruf Procedure

Ausführen (1):

exec proc_acsii

Ausführen (2):

begin proc_acsii;
end;

Kompilerfehler

Wenn eine Fehlermeldung beim Anlegen kommt kann mit „show errors“ die Meldung angezeigt werden.

Parameter

Parameter können innerhalb der Procedure nur lesend verwendet werden.

create or replace procedure proc_ascii(p_bis in number default 256)
as
  type t_ascii is table of char(1);
  a_ascii t_ascii;
begin null;
  a_ascii := t_ascii();
  a_ascii.extend(p_bis);
  for i in 1 .. p_bis loop
    a_ascii(i) := chr(i-1);
    dbms_output.put_line(i || ' ' || chr(i-1));
  end loop;
end;

Rückgaben

create or replace procedure proc_add(p_in in number, p_out out number)
as
begin null;
  p_out := p_in + 1;
end;

declare
  v_out number;
begin
  proc_add(10,v_out);
  dbms_output.put_line(v_out); /* 11 */
end;

declare
  v_out number;
begin
  proc_add(p_out=>v_out,p_in=>10);
  dbms_output.put_line(v_out); /* 11 */
end;

By-Reference:

create or replace procedure proc_add(p_inout in out number)
as
begin null;
  p_inout := p_inout + 1;
end;

declare
  v_inout number;
begin
  v_inout := 10;
  proc_add(v_inout);
  dbms_output.put_line(v_inout); /* 11 */
end;

PLSQL Oracle Tabelle mit Bulk Insert aus Array einfügen

PLSQL um in Oracle mehrere Zeilen „gleichzeitig“ einzufügen:

declare
  type t_emp is table of emp%rowtype;
  a_emp t_emp;
begin
  select * bulk collect into a_emp from emp;
  dbms_output.put_line('emp.count ' || a_emp.count);
  -- Datensätze clonen
  for i in a_emp.first .. a_emp.last loop
    a_emp(i).empno := a_emp(i).empno + 1000;
    dbms_output.put_line('empno ' || a_emp(i).empno);
  end loop;
  -- Bulk insert
  forall i in a_emp.first .. a_emp.last
    insert into emp values a_emp(i);
end;