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

Selecting multiple rows from a table in Oracle table with a java procedure

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

insert into j_sel_mult_tab values (10, 'ten');
insert into j_sel_mult_tab values (10, 'zehn');
insert into j_sel_mult_tab values (10, 'dix');

insert into j_sel_mult_tab values (20, 'twenty');
insert into j_sel_mult_tab values (20, 'zwanzig');
insert into j_sel_mult_tab values (20, 'vingt');

insert into j_sel_mult_tab values (30, 'thirty');
insert into j_sel_mult_tab values (30, 'dreissig');
insert into j_sel_mult_tab values (30, 'trente');

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

public class SelMultTab {

  #sql static iterator CUR (java.lang.String b);

  public static java.lang.String Go(oracle.sql.NUMBER p_a) throws SQLException {

    java.lang.String ret = new java.lang.String("");
    
    CUR cur;
    #sql cur = { select b from j_sel_mult_tab where a = :p_a };

    while (cur.next()) {
      ret += cur.b();
    }

    cur.close();

    return ret;
  }
}
/

show errors

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

show errors
   

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