Home

# Create Java Source

可以在 PL/SQL 中直接编译 Java 源代码

1
2
3
4
5
6
7
create or replace and compile java source named babb as
public class Babb
{
  public static String getStrMsg(String str){
    return str+"\t 共有字符:"+str.length()+"个";
  }
}

也可以使用 loadjava 工具加载 Class 文件

1
2
3
4
5
6
7
// Babb.java
public class Babb
{
  public static String getStrMsg(String str){
    return str+"\t 共有字符:"+str.length()+"个";
  }
}

javac Babb.java 编译生成 Babb.class (注意编译的 java 版本要和数据库版本对应)

loadjava.bat -user xxx/xxx@DEV -force -oci8 D:/study/java/oracle_call_java/Babb.class

# Create Call Function

1
2
3
create or replace function javaTest(str varchar2)
return varchar2 as
LANGUAGE JAVA NAME 'Babb.getStrMsg(java.lang.String) return java.lang.String';

# Test

1
2
3
select javatest('abcd') from dual;

-- abcd 共有字符:4个

# Drop Java Source

1
drop java source babb;

# Comments

# Clean CSV Line

Java Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
package com.jbn;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class EDIJavaUtils {

    public static String cleanCSVLine(String content, String separator) {
        String contentWithSeparator = separator + content + separator;
        // Java Backreference in pattern need use \\N
        String regexStr = separator + "(\"?)(.*?)\\1(?=" + separator + ")";
        // (?=pattern) zero-width positive lookahead assertion
        // Pattern pattern = Pattern.compile("/(\"?)(.*?)\\1(?=/)");
        Pattern pattern = Pattern.compile(regexStr);
        Matcher matcher = pattern.matcher(contentWithSeparator);
        // Java Backreference in replacement need use $N
        String result = matcher.replaceAll("$2\n");
        return result.substring(0, result.length() - 2);
    }

    public static void main(String[] args) {
        String content = "a/\"b \" c\"/\"d\"";
        String result = cleanCSVLine(content, "/");
        System.out.println(result);
    }
}

// a
// b " c
// d

Compile Java Source

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create or replace and compile java source named som_edi_java_utils as
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class EDIJavaUtils {
    public static String cleanCSVLine(String content, String separator) {
        String contentWithSeparator = separator + content + separator;
        String regexStr = separator + "(\"?)(.*?)\\1(?=" + separator + ")";
        // Pattern pattern = Pattern.compile("/(\"?)(.*?)\\1(?=/)");
        Pattern pattern = Pattern.compile(regexStr);
        Matcher matcher = pattern.matcher(contentWithSeparator);
        String result = matcher.replaceAll("$2\n");
        return result.substring(0, result.length() - 2);
    }
}

Create Call Function in Package

1
2
3
4
5
6
7
8
9
create or replace package body som_edi_morrison_pkg is
  function clean_csv_line(content varchar2, separator varchar2) return varchar2;
end som_edi_custom_utils_pkg;

create or replace package body som_edi_custom_utils_pkg is
  function clean_csv_line(content varchar2, separator varchar2) return varchar2 
  as
  LANGUAGE JAVA NAME 'EDIJavaUtils.cleanCSVLine(java.lang.String, java.lang.String) return java.lang.String';
end som_edi_custom_utils_pkg;

Test

1
2
3
4
5
6
7
select som_edi_custom_utils_pkg.clean_csv_line('ab/95"508/"AP"PLE""/"10/FEB/12"', '/') 
  from dual;

-- ab
-- 95"508
-- AP"PLE"
-- 10/FEB/12

# Return an array from Java to PL/SQL

Reference: oracle - How to return an array from Java to PL/SQL? - Stack Overflow

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
create type edi_varchar_list_type is table of varchar2(32767);

create or replace and compile java source named edi_java_test as
import java.lang.*;
public class edi_java_test {
    public static java.sql.Array split_str(String str, String separator) throws java.sql.SQLException {
        String[] elements = str.split(separator);
        
        oracle.jdbc.OracleDriver driver = new oracle.jdbc.OracleDriver();
        java.sql.Connection conn = driver.defaultConnection();
        oracle.jdbc.OracleConnection oracleConn = (oracle.jdbc.OracleConnection)conn;
        
        String typeName = "edi_varchar_list_type";
        /* Yeah - typeName have to be UPPERCASE, really. */
        java.sql.Array sqlArr = oracleConn.createARRAY(typeName.toUpperCase(), elements);
        return sqlArr;
    }
};

create or replace function edi_split_str(p_str varchar2, p_separator varchar2) return edi_varchar_list_type as
language java name 'edi_java_test.split_str(java.lang.String, java.lang.String) return java.sql.Array';

declare
  l_elements edi_varchar_list_type := edi_split_str('Babb,18,IT,Male', ',');
begin
  for i in l_elements.first .. l_elements.last loop
    dbms_output.put_line(l_elements(i));
  end loop;
end;

select * from table(edi_split_str('Babb,,18,,IT,,Male', ',,'));
Edited on