Today, i'm going to discuss another powerful feature of Oracle. That is embedding your Java code inside Oracle Procedures. This gives a lot of flexibility & power to Oracle and certainly you can do plenty of things which generally are very difficult to implement directly.
In this purpose i cannot restrict myself to explanation made by Bulusu Lakshman and that is -
From Oracle 9i a new environments are taking place where Java and PL/SQL can interact as two major database languages. There are many advantages to using both languages -
PL/SQL Advantage:
- Intensive Database Access - It is faster than Java.
- Oracle Specific Functionality that has no equivalent in Java such as using dbms_lock & dbms_alert.
- Using the same data types and language construct as SQL providing seamless access to the database.
JAVA Advantage:
- Automatic garbage collection, polymorphism, inheritance, multi-threading
- Access to system resources outside of the database such as OS commands, files, sockets
- Functionality not avialable in PL/SQL such as OS Commands, fine-grained security policies, image generation, easy sending of e-mails with attachements using JavaMail.
But, i dis-agree with him in case of fine grained security policies as Oracle has drastically improves it and introduces security policies like - VPDB (Virtual Private Database) & Database Vault. Anyway, we'll discuss these topics on some other day.
For better understanding i'm follow categories and we will explore them one by one. Hope you get some basic idea on this powerful feature by Oracle.
Before proceed we have to know the basics of the main ingredients called dbms_java .
We've to prepare the environment.
In Sys,
sys@ORCL>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
Elapsed: 00:00:00.00
sys@ORCL>
sys@ORCL>
sys@ORCL>exec dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','writeFileDescriptor','');
PL/SQL procedure successfully completed.
Elapsed: 00:00:53.54
sys@ORCL>
sys@ORCL>exec dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','readFileDescriptor','');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
sys@ORCL>
sys@ORCL>exec dbms_java.grant_permission('SCOTT','SYS:java.io.FilePermission','D:\Java_Output\*.*','read,write,execute,delete');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
sys@ORCL>Let's concentrate on our test cases.
In Scott,
Type: 1
scott@ORCL>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
Elapsed: 00:00:02.77
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace and compile java source named "Print_Hello"
2 as
3 import java.io.*;
4 public class Print_Hello
5 {
6 public static void dislay()
7 {
8 System.out.println("Hello World...... In Java Through Oracle....... ");
9 }
10 };
11 /
Java created.
Elapsed: 00:00:44.17
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace procedure java_print
2 as
3 language java name 'Print_Hello.dislay()';
4 /
Procedure created.
Elapsed: 00:00:01.39
scott@ORCL>
scott@ORCL>call dbms_java.set_output(1000000);
Call completed.
Elapsed: 00:00:00.34
scott@ORCL>
scott@ORCL>set serveroutput on size 1000000;
scott@ORCL>
scott@ORCL>exec java_print;
Hello World...... In Java Through Oracle.......
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.22
scott@ORCL>Type: 2 (Returning Value from JAVA)
scott@ORCL>
scott@ORCL>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
Elapsed: 00:00:00.13
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace and resolve java source named "ReturnVal"
2 as
3 import java.io.*;
4
5 public class ReturnVal extends Object
6 {
7 public static String Display()
8 throws IOException
9 {
10 return "Hello World";
11 }
12 };
13 /
Java created.
Elapsed: 00:00:00.22
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace function ReturnVal
2 return varchar2
3 is
4 language java
5 name 'ReturnVal.Display() return String';
6 /
Function created.
Elapsed: 00:00:00.00
scott@ORCL>
scott@ORCL>
scott@ORCL>call dbms_java.set_output(1000000);
Call completed.
Elapsed: 00:00:00.00
scott@ORCL>
scott@ORCL>
scott@ORCL>column ReturnVal format a15
scott@ORCL>
scott@ORCL>
scott@ORCL>
scott@ORCL>
scott@ORCL>select ReturnVal from dual;
RETURNVAL
---------------
Hello World
Elapsed: 00:00:00.12
scott@ORCL>
scott@ORCL>So, you can return the value from the compiled Java source, too.
Type: 3 (Reading console value into JAVA)
scott@ORCL>ed
Wrote file C:\OracleSpoolBuf\BUF.SQL
1 create or replace java source named "ConsoleRead"
2 as
3 import java.io.*;
4 class ConsoleRead
5 {
6 public static void RDisplay(String Det)
7 {
8 String dd = Det;
9 System.out.println("Value Passed In Java Is: " + dd);
10 System.out.println("Exiting from the Java .....");
11 }
12* };
13 /
Java created.
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace procedure java_UserInput(InputStr in varchar2)
2 as
3 language java
4 name 'ConsoleRead.RDisplay(java.lang.String)';
5 /
Procedure created.
scott@ORCL>
scott@ORCL>
scott@ORCL>call dbms_java.set_output(100000);
Call completed.
scott@ORCL>
scott@ORCL>
scott@ORCL>set serveroutput on size 100000
scott@ORCL>
scott@ORCL>exec java_UserInput('Satyaki');
Value Passed In Java Is: Satyaki
Exiting from the Java .....
PL/SQL procedure successfully completed.
scott@ORCL>Type: 4 (Reading file from OS directory using JAVA)
scott@ORCL>ed
Wrote file C:\OracleSpoolBuf\BUF.SQL
1 create or replace java source named "ReadTextFile"
2 as
3 import java.io.*;
4 class ReadTextFile
5 {
6 public static void Process(String FileName) throws IOException
7 {
8 int i;
9 FileInputStream fin;
10 try
11 {
12 fin = new FileInputStream(FileName);
13 }
14 catch(FileNotFoundException e)
15 {
16 System.out.println("File Not Found....");
17 return;
18 }
19 catch(ArrayIndexOutOfBoundsException e)
20 {
21 System.out.println("Usage: showFile File");
22 return;
23 }
24 do
25 {
26 i = fin.read();
27 if(i != 1)
28 System.out.println((char) i);
29 }while(i != 1);
30 fin.close();
31 }
32* };
33 /
Java created.
scott@ORCL>
scott@ORCL>create or replace procedure Java_ReadTextFile(FileNameWithPath in varchar2)
2 as
3 language java
4 name 'ReadTextFile.Process(java.lang.String)';
5 /
Procedure created.
scott@ORCL>
scott@ORCL>
scott@ORCL>call dbms_java.set_output(100000);
Call completed.
scott@ORCL>
scott@ORCL>
scott@ORCL>exec Java_ReadTextFile('D:\Java_Output\Trial.txt');Type: 4 (Writing file in OS directory using JAVA)
In Scott,
scott@ORCL>
scott@ORCL>create or replace java source named "DynWriteTextFile"
2 as
3 import java.io.*;
4 class DynWriteTextFile
5 {
6 public static void proc(String ctent,String FlNameWithPath) throws IOException
7 {
8 int i,j;
9 String FileNm = FlNameWithPath;
10 RandomAccessFile rFile;
11
12 try
13 {
14 rFile = new RandomAccessFile(FileNm,"rw");
15 }
16 catch(FileNotFoundException e)
17 {
18 System.out.println("Error Writing Output File....");
19 return;
20 }
21
22 try
23 {
24 int ch;
25
26 System.out.println("Processing starts...");
27
28 ch = ctent.length();
29
30 rFile.seek(rFile.length());
31 for(int k=0; k<ch; k=k+ctent.length())
32 {
33 rFile.writeBytes(ctent);
34 }
35 }
36 catch(IOException e)
37 {
38 System.out.println("File Error....");
39 }
40 finally
41 {
42 try
43 {
44 System.out.println("Successfully file generated....");
45 rFile.close();
46 }
47 catch(IOException oe)
48 {
49 System.out.println("Exception in the catch block of finally is: " +oe);
50 System.exit(0);
51 }
52 }
53 }
54 };
55 /
Java created.
Elapsed: 00:00:00.17
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace procedure JavaDyn_WriteTextFile(para in varchar2,FileNameWithPath in varchar2)
2 as
3 language JAVA
4 name 'DynWriteTextFile.proc(java.lang.String, java.lang.String)';
5 /
Procedure created.
Elapsed: 00:00:00.15
scott@ORCL>In Sys,
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for 32-bit Windows: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production sys@ORCL>set timi on sys@ORCL> sys@ORCL> sys@ORCL>create or replace public synonym dbms_dwrite_file for scott.JavaDyn_WriteTextFile; Synonym created. Elapsed: 00:00:00.08 sys@ORCL> sys@ORCL>grant execute on dbms_dwrite_file to scott; Grant succeeded. Elapsed: 00:00:00.18 sys@ORCL>
In Scott,
scott@ORCL>
scott@ORCL>create or replace procedure DWrite_Content(dt in date,FileNmWithPath in varchar2)
2 is
3 cursor c1
4 is
5 select empno,ename,sal
6 from emp
7 where hiredate = dt;
8 r1 c1%rowtype;
9
10 str varchar2(500);
11 begin
12 str:= replace(FileNmWithPath,'\','\\');
13 dbms_dwrite_file('Employee No'||' '||'First Name'||' '||'Salary',str);
14 dbms_dwrite_file(chr(10),str);
15 dbms_dwrite_file('---------------------------------------------------',str);
16 dbms_dwrite_file(chr(10),str);
17 for r1 in c1
18 loop
19 dbms_dwrite_file(r1.empno||' '||r1.ename||' '||r1.sal,str);
20 dbms_dwrite_file(chr(10),str);
21 end loop;
22 exception
23 when others then
24 dbms_output.put_line(sqlerrm);
25 end;
26 /
Procedure created.
Elapsed: 00:00:00.43
scott@ORCL>
scott@ORCL>
scott@ORCL>call dbms_java.set_output(100000);
Call completed.
Elapsed: 00:00:00.02
scott@ORCL>
scott@ORCL>exec DWrite_Content(to_date('21-JUN-1999','DD-MON-YYYY'),'D:\Java_Output\satyaki.txt');
Processing starts...
Successfully file generated....
PL/SQL procedure successfully completed.Hope, this thread will give you some basic idea about using your Java code with Oracle PL/SQL.
I'll discuss another topic very soon. Till then - Keep following. ;)
Regards.