René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Selecting from a table in Oracle table with a java procedure

create table j_sel_tab (a number, b varchar2(10));

insert into j_sel_tab values (10, 'ten');
insert into j_sel_tab values (20, 'twenty');
insert into j_sel_tab values (30, 'thirty');


create or replace and compile java source named "SelTab"
as
import java.sql.*;

public class SelTab {
  public static java.lang.String Go(oracle.sql.NUMBER p_a) throws SQLException {
    java.lang.String p_b;
    #sql {
      select b into :p_b from j_sel_tab where a = :p_a
    };

    return p_b;
  }
}
/

show errors

create or replace function j_sel_tab_f(v_a in number)
  return varchar2
  as language java
    name 'SelTab.Go(oracle.sql.NUMBER) return java.lang.String';
/

show errors
   

select j_sel_tab_f(20) from dual;
  
See also Java in Oracle