Invoking a Java program in an Oracle trigger

Because the project needs to have a written Java program that wants to be called in a trigger on a table in Oracle so that every time the data is updated, the Java program is called to modify the background data.

The process is now documented as follows:

1. Writing Java programs

[Java]View PlainCopy

2. Public class Main {
4. public static void Modify (String stattime)
6. {
8. String datas = "/var/spool/cron/oracle";
10. File data = new file (datas);
12. StringBuffer backup = null;
14. try {
16. StringBuffer content = new StringBuffer ();
18. Backup = new StringBuffer ();
20. BufferedReader san = new BufferedReader (new FileReader (data));
22. String line = null;
24. While ((Line=san.readline ()) =null)
26. {
28. Backup.append (line);
30. Backup.append ("/n");
32. }
34. Content.append ("15");
36. Content.append (Stattime.trim ());
38. Content.append ("* * * */home/oracle/task.sh");
40. Content.append ("/n");
44. Content.append ("13");
46. Content.append (Stattime.trim ());
48. Content.append ("* * * */home/oracle/tj_task/task2.sh");
50. Content.append ("/n");
54. FileWriter writer = new FileWriter (data,false);
56. Writer.write (Content.tostring ());
58. Writer.flush ();
62. } catch (FileNotFoundException e) {
64. E.printstacktrace ();
66. } catch (IOException e) {
68. Try
70. {
72. FileWriter writer = new FileWriter (data,false);
74. Writer.write (Backup.tostring ());
76. Writer.flush ();
78. }catch (Exception ex)
80. {
82. Ex.printstacktrace ();
84. }
86. }
88. }
90. }

Note that the Java program here does not need to follow the standard Java program, must have the main entry function, instead, the function here is generally not named Main, unless the format is the standard main function format, I made this mistake at the beginning, I have been unable to find the reason, Later through the csdn on the people, only to find that I put the above function name written in the main, and then change the name of the good. Also, this function must be static.

2. Load the Java program into Oracle

In the $oracle_home/bin directory, there is a Loadjava command that uses this command to load the newly written Java program into the database.

Loadjava-user test/[email protected] -o-v-f-r Main.java

If successful, will print out the message prompt success, if the program has compiled errors, will also prompt you the wrong place.

3. Modify Permissions

Because my Java program involves the read and write operation of the file, you need to modify the permissions first.

Log into the database as an administrator first

Sqlplus/as SYSDBA

And then execute

Begin

Dbms_java.grant_permission (' TEST ', ' SYS:java.io.FilePermission ', '/var/spool/cron/oracle ', ' Read,write,execute, Delete ');

End

/

After execution, when executing the above Java program in the database, you have permission to read and write the file.

4. Create a stored procedure

Into the database, I was in the Sqlplus command line

Create or Replace procedure Modify (name varchar2) as language Java name ' main.modify (java.lang.String) ';

/

You are prompted to create the stored procedure successfully.

5. Increase Trigger

I added trigger to a table directly in Toad, so I just wrote the key part of the code

Begin

If:old.name = ' time ' Then

Modify (: New.value);

End If;

End

At this point, the call to the Java program in Oracle's trigger is complete, and later experiments prove that whenever the table is updated, it is true that the Java program is executed and the file is read and written.

Invoking a Java program in an Oracle trigger