oracle調用JAVA類的方法主要有以下三種:
可能是調試方便,據說這種方法比較通用。
c:\test\hello.java
Java Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public class hello
{
public static void main(String[] args)
{
System.out.println("Hello");
hello h = new hello();
h.insertM(9);
}
public static void insertM(int pid)
{
System.out.println("This is the method insertM.");
}
}
C:\test>loadjava -u test/test@mydb -v -resolve hello.java
SQL> create procedure prc_hehe as language java name 'hello.main(java.lang.String[])
過程已創建。
SQL> call prc_hehe();
調用完成。
SQL> set serveroutput on size 2000
SQL> call prc_hehe();
調用完成。
SQL> exec dbms_java.set_output(2000);
PL/SQL 過程已成功完成。
SQL> call prc_hehe();
Hello
This is the method insertM.
調用完成。
SQL>show errors;
修改java類,先刪除再裝載,方法:
dropjava -u test/test@mydb -v -resolve hello.java
loadjava -u test/test@mydb -v -resolve hello.java
Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create or replace and compile java source named hehe
AS
public class hello
{
public static void msg(String name)
{
System.out.println("hello," + name);
}
};
create or replace procedure prc_hehe
(
p_name VARCHAR2
)
as
language java name 'hello.msg(java.lang.String)';
-- 調用結果
SQL> call prc_hehe('oopp');
hello,oopp
create or replace directory CLASS_DIR as 'c:\test';
create or replace java class using bfile(class_dir,'hello.class');
create or replace procedure prc_hello
(
p_name VARCHAR2
)
as
language java name 'hello.msg(java.lang.String)';
-- 測試結果
SQL> call prc_hello('java');
java
SQL> call prc_hello('Jerry');
call prc_hello('Jerry')
*
第 1 行出現錯誤:
ORA-29516: Aurora 斷言失敗: Assertion failure at eox.c:359
Uncaught exception System error: java/lang/UnsupportedClassVersionError
原因:機器裝了多個java版本,oracle的java版本低於環境變量設置的版本。
解決方法:用$ORACLE_HOME/jdk/javac 重新編譯java文件
核對java已經導入數據庫
select * from user_source where type LIKE 'JAVA%' AND NAME = '<java file>'
建立function
CREATE OR REPLACE FUNCTION <FUNCTION_NAME> (<PARAMETER LIST IN ORACLE DATATYPE>) RETURN <ORACLE DATATYPE OF RETURN VARIABLE> AS
LANGUAGE JAVA
NAME '<clase.method>(<parameter list in java datatype>) return java datatype of return variable';
例:
登陸某一用戶登錄,並創建java程序資源,在pl/sql中java source中顯示你所編寫的java代碼;
SQL 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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
create or replace and compile java source named bb_wx_replosssbk as
import java.sql.*;
import oracle.jdbc.driver.*;
public class bb_wx_replosssbk
{
/**
* 社保卡掛失
*/
public static String callProc(String sSfzh, String sPwd, String sType)
{
OracleDriver driver = new OracleDriver();
Connection connection = null;
CallableStatement cstmt = null;
String sRtn = "beg";
try
{
sRtn = " try beg";
connection = DriverManager.getConnection("jdbc:oracle:thin:user/[email protected]:1521:orcl");
sRtn = "con";
cstmt = connection.prepareCall("{call run_replosssbk(?,?,?,?)}");
sRtn = "invoke";
cstmt.setString(1, sSfzh);
cstmt.setString(2, sPwd);
cstmt.setString(3, sType);
cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
sRtn = "set value";
cstmt.executeUpdate();
sRtn = "execute";
sRtn = cstmt.getString(4);
}
catch (Exception e)
{
sRtn = e.toString();
e.printStackTrace();
}
finally
{
try
{
if (cstmt != null)
{
cstmt.close();
}
if (connection != null)
{
connection.close();
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
return sRtn;
}
}
創建調用Java資源的函數
create or replace function run_bb_wx_replosssbk(
sSfz in varchar2,
sPwd in varchar2,
sType in varchar2
)
return varchar2
as
language java name 'bb_wx_replosssbk.callProc(java.lang.String,java.lang.String,java.lang.String) return java.lang.String';
建立一過程調用存儲過程
create or replace procedure RUN(
sSfz in varchar2,
sPwd in varchar2,
sType in varchar2
sRtn out varchar2
)
as
begin
--sRtn := run_bb_wx_replosssbk(sSfz in varchar2,sPwd in varchar2,sType in varchar2);
Select run_bb_wx_replosssbk(sSfz in varchar2,sPwd in varchar2,sType in varchar2)
Into sRtn from dual;
end;
/
附:
如果需要java存取文件,需要使用dba用戶賦權
EXEC Dbms_Java.Grant_Permission('ONBOARDING', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC Dbms_Java.Grant_Permission('ONBOARDING', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
EXEC dbms_java.grant_permission( 'ONBOARDING', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute' );
收回權限的語句如下
EXEC Dbms_Java.revoke_Permission('ONBOARDING', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC Dbms_Java.revoke_Permission('ONBOARDING', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
EXEC dbms_java.revoke_permission( 'ONBOARDING', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute' );