Oracle calls JAVA class methods, oracle calls java

Oracle calls JAVA class methods, oracle calls java
Import jar package
Import the required jar package in oracle, compress the edited java class into a jar package, and directly write a simple call in oarcle,
1. The operating system must have jdk that supports loadjava commands.
2. Load the jlha. jar package to the oracle database.
Operation Process: in the dos environment, enter the command: loadjava-r-f-o-user usscares/usscares @ usscares jlha. jar
Note: The jar package must be compiled in the 1.4 environment. Right-click the project and choose properties java compiler compoler compliance level 1.4. Otherwise, a similar version 49 error will be reported and JAVA class will be called.

There are three main methods for oracle to call JAVA classes:

It may be convenient for debugging. It is said that this method is more common.
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 [])

The process has been created.

SQL> call prc_hehe ();

The call is complete.

SQL> set serveroutput on size 2000
SQL> call prc_hehe ();

The call is complete.

SQL & gt; exec dbms_java.set_output (2000 );

The PL/SQL process is successfully completed.

SQL> call prc_hehe ();
Hello
This is the method insertM.

The call is complete.

SQL> show errors;

Modify the java class. Delete and load the class first. The method is as follows:

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 )';

-- Call result

SQL> call prc_hehe ('opp ');
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 )';

-- Test Result

SQL> call prc_hello ('java ');
Java

Possible Errors

SQL> call prc_hello ('Jerry ');
Call prc_hello ('Jerry ')
*
Row 3 has an error:
ORA-29516: Aurora Assertion failure: Assertion failure at eox. c: 359
Uncaught exception System error: java/lang/UnsupportedClassVersionError

Cause: Multiple java versions are installed on the machine. The oracle java version is earlier than the version set for environment variables.

Solution: use $ ORACLE_HOME/jdk/javac to re-compile the java file.

Check that the java database has been imported

Select * from user_source where type LIKE 'java % 'and name =' <JAVA file>'

Create a function

Create or replace function <FUNCTION_NAME> (<parameter list in oracle datatype>) RETURN <oracle datatype of return variable>
LANGUAGE JAVA
Name' <clase. method> (<parameter list in java datatype>) return java datatype of return variable ';

Example:

Log on to a user and create java program resources. The java source code you have written is displayed in pl/SQL;
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 Import java. SQL .*; Import oracle. jdbc. driver .*; Public class bb_wx_replosssbk { /** * Loss of social security card */ 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/pwd@127.0.0.1: 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.exe cuteUpdate (); 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; } }

Create a function that calls a Java Resource
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 a procedure call Stored Procedure
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;
/

Appendix:

If you need java to access files, you must use the dba user to grant permissions.

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 ');
The statement for revoking permissions is as follows:

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 ');