ppt db25 oracle 04

5/28/2018 Ppt Db25 Oracle 04

1/33

Java Stored ProceduresOracle Day 4

5/28/2018 Ppt Db25 Oracle 04

2/33

2

Copyright 2005, InfosysTechnologies Ltd

ER/CORP/CRS/DB25/003Version No. 2.0

Objectives

Integration of Java and Oracle

PL/SQL vs. Java Stored Procedures

Understanding Java Stored Procedures

5/28/2018 Ppt Db25 Oracle 04

3/33

3



What is Java?

Java is an object-oriented programming language developed by SunMicrosystems.

Modeled after C++,the Java language was designed to be:

Small

Simple

Portable across platforms

5/28/2018 Ppt Db25 Oracle 04

4/334



What is a Stored Procedure ?

A set of Structured Query Language (SQL) statements

Stored in a database in compiled form so that one can share it between anumber of programs

5/28/2018 Ppt Db25 Oracle 04

5/335



What is a Java Stored Procedure ?

Java methods stored in the database.

An open and portable alternative to proprietary procedural SQLextensions (PL/SQL), for implementing stored procedures.

Java stored procedures are Java classes, stored as Oracle schemaobjects, made accessible to Oracle SQL and PL/SQL through callspecifications.

5/28/2018 Ppt Db25 Oracle 04

6/336



Integration of Oracle and Java

Oracles Java Virtual Machine - Aurora

Since Oracle8i, the Oracle database embeds a Java VM, which letsrun Java directly in the database

The latest Oracle 10g server is compliant with J2SE (Java 2 Server Edition)1.4x

5/28/2018 Ppt Db25 Oracle 04

7/337



How does it work?

Data

Java

Applet

JDBC

Driver

PRO *

Client

ODBC

Client

Thin Client(s)

Fat Client(s)

Oracle

Forms

Oracle

Reports

Oracle Net

Connection

Manager

OracleN

et

OracleN

et

Oracle Net Oracle Net

PL/SQL storedProcedure

Relational

DataObject

RelationalData

PL/SQL cover for Java

Method

Aurora running Java

Method

Oracle Database

SQL *

Plus

5/28/2018 Ppt Db25 Oracle 04

8/338



PL/SQL vs. Java Stored Procedures

Whether to use PL/SQL or Java stored procedures is the legitimate

million dollar question.

Should I forget all the things I've been told about PL/SQL and move onto the greener Java pastures?

Lets cut to the chase..

5/28/2018 Ppt Db25 Oracle 04

9/339



PL/SQL Stored Procedures

PL/SQL is Oracles procedural extension to SQL.

PL/SQL is designed and optimized for stored procedures and functions.

Well suited for encapsulating SQL operations with procedural logic and formanipulating all database object types.

Extensively used by a large community of Oracle developers andcustomers for implementing database operations and packages.

5/28/2018 Ppt Db25 Oracle 04

10/3310



Java Stored Procedures1/2

Represent an open, database-independent (They can be written outside thedatabase) alternative to PL/SQL and bring:

Robustness

As opposed to PL/SQL, Java requires declaring exceptions that can be thrown bymethods in any class, thereby making Java stored procedures more robust.

Reuse of Java skills Java is one of the dominant programming languages, it is likely that Java

programmers already exist within ones company.

Cross-Vendor and Cross-Platform Portability

All major databases vendors provide Java support in their database, either through atight integration with the database runtime such as Oracle or through a looselycoupled integration. Java stored procedures not only inherit cross-platform portabilityfrom the database but, moreover, cross-vendor portabilityin other words,database independence.

5/28/2018 Ppt Db25 Oracle 04

11/3311



Java Stored Procedures- 2/2 Complex database logic and powerful usage patterns

Java programming in the database allows implementing more complex databaselogic than PL/SQL and extends database functionality.

Power, richness, and object-orientation of the Java language

5/28/2018 Ppt Db25 Oracle 04

12/3312



Java Stored Proc vs. PL/SQL - General Rule of Thumb

Use PL/SQL for database-centric logic that requires seamless integration

with SQL and therefore complete access to database objects, types, andfeatures.

Use Java as an open alternative to PL/SQL for database independence,and for integrating and bridging the worlds of SQL, XML, J2EE, and Web

services.

5/28/2018 Ppt Db25 Oracle 04

13/3313



Steps to create a Java Stored Procedure

Let us understand with an example

Create the Java code element

Load the Java classes into Oracle-LOADJAVA utility

Publish the Java class methods-WRAPPER program

Grant privileges

Calling the Java Stored Procedure

5/28/2018 Ppt Db25 Oracle 04

14/3314



Create the Java code elementSTEP 1/5

Why Public and Static?

We will use a simple Hello class, with one method, helloWorld(), thatreturns the

string "Hello world":

// Hello World Program

public class Hello{

public static String helloWorld(){

return "hello world";

}

}

Compile the program to generate the java class file.

5/28/2018 Ppt Db25 Oracle 04

15/3315



Load the Java class into Oracle-LOADJAVA utilitySTEP 2/5

Utility for loading Java source files, Java class files, and Java resource files

Invoked from the command line

loadjavau username/password@oracleSID -rv

For our Hello class run the following command from the command line:

loadjava -u scott/tiger@training -r -v Hello.class

5/28/2018 Ppt Db25 Oracle 04

16/3316



First time - run LOADJAVA utility.The first time one run LOADJAVA in a schema, it creates following elements for its

own use:

CREATE$JAVA$LOB$TABLE

JAVA$CLASS$MD5$TABLE

LOADLOBS

5/28/2018 Ppt Db25 Oracle 04

17/3317



Imp options - LOADJAVA utility.

-verbose | v

-resolve | r

-force | f

5/28/2018 Ppt Db25 Oracle 04

18/3318



Examining Loaded Java Elements in Oracle

USER_OBJECTS data dictionary view is used to check the loaded Java

elements in OracleFollowing query is used to figure out the loaded Java elements in Oracle:

COLUMN object_name FORMAT A30

SELECT object_name, object_type, status, timestamp

FROM user_objects

WHERE (object_name NOT LIKE 'SYS_%'

AND object_name NOT LIKE 'CREATE$%'

AND object_name NOT LIKE 'JAVA$%'

AND object_name NOT LIKE 'LOADLOB%')

AND object_type LIKE 'JAVA%'

ORDER BY object_type, object_name

5/28/2018 Ppt Db25 Oracle 04

19/3319



DROPJAVA utility

Reverses the action of loadjava

Converts filename into the name of schema objects, drops the schemaobjects, and finally deletes their digest table rows.

Syntax:

dropjava u username/password@OralceSID filename

To drop Hello class from Oracle execute the following command fromcommand line:

dropjavau scott/tiger@training Hello.class

5/28/2018 Ppt Db25 Oracle 04

20/3320



Publish the Java class methods- STEP 3/5

PL/SQL WRAPPER program are required to publish the Java Methods.

Following PL/SQL wrapper program is required to publish the Java methodhelloWorld:

SQL>CREATE OR REPLACE FUNCTION proc_get_msg

RETURN VARCHAR2

AS LANGUAGE JAVA

NAME 'Hello.helloWorld() return java.lang.String';

5/28/2018 Ppt Db25 Oracle 04

21/3321



Rules for PL/SQL Wrappers A PL/SQL wrapper and the Java method it publishes must reside in the same

schema.

One can publish only public static Java methods.

Default values are not allowed in the parameter list of the PL/SQL wrapper.

One have to take care of mapping datatypes between Java methods andPL/SQL wrapper.

5/28/2018 Ppt Db25 Oracle 04

22/33

22



Grant privilegesSTEP 4/5 . Optional Step

To allow Java related operation Oracle allow following roles:

JAVAUSERPRIV: To read or write a file, one only need theJAVAUSERPRIV role.

JAVASYSPRIV: To create a file through Java, one needJAVASYSPRIV role.

DBA has to Issue the following command to execute the grant:

SQL>GRANT JAVAUSERPRIV TO username

5/28/2018 Ppt Db25 Oracle 04

23/33

23



Calling the Java Stored ProcedureSTEP 5/5

The final step of our example:

Define the variable to get the message returned by function

SQL> VARIABLE var_msg VARCHAR2(20);

Call the Function (get the return value into the variable)

SQL> CALL proc_get_msg() INTO :var_msg;

Print the result

SQL> PRINT var_msg;

5/28/2018 Ppt Db25 Oracle 04

24/33

24



Calling the Java Stored Procedure ..

Java Stored Procedures can be called from:

SQL DML statements (INSERT, UPDATE, DELETE,SELECT, CALL, EXPLAIN PLAN, LOCK TABLE, andMERGE )

PL/SQL blocks, subprograms, and packages

Database triggers

5/28/2018 Ppt Db25 Oracle 04

25/33

25



Rules for calling Java Stored Procedure - Purity Rules If one calls a method from SELECT statement , he is not allowed to modify any

database tables.

If one calls a method from an INSERT, UPDATE, or DELETE statement, themethod cannot query or modify any database tables modified by thatstatement.

If one calls a method from a SELECT, INSERT, UPDATE, or DELETEstatement, the method cannot execute:

SQL transaction control statements e.g. COMMIT

SQL session control statements e.g. SET ROLE

DDL statements.

5/28/2018 Ppt Db25 Oracle 04

26/33

26



A working Java Stored Procedure exampleLearn More!

This sample Java stored procedure is going to insert a record in theemployee table.

The relation employee is defined as:

employee (emp_id, emp_f_name, emp_l_name,emp_salary,dept_id)

Create the Java code element

5/28/2018 Ppt Db25 Oracle 04

27/33

27



Create the Java code elementimport java.sql.*;

import oracle.jdbc.*;

public class EmpManager {

//Add an employee to the database.public static void addEmp(int emp_id, String emp_f_name, String emp_l_name, floatemp_salary, int dept_id) {System.out.println("Creating new employee...");try {

Connection conn = DriverManager.getConnection("jdbc:default:connection:");String sql = "INSERT INTO employee " +"(emp_id,emp_f_name,emp_l_name,emp_salary,dept_id) " +

"VALUES(?,?,?,?,?)";PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setInt(1,emp_id); pstmt.setString(2,emp_f_name);pstmt.setString(3,emp_l_name); pstmt.setFloat(4,emp_salary);pstmt.setInt(5,dept_id);pstmt.executeUpdate(); pstmt.close(); }

catch(SQLException e) {System.err.println("ERROR! Adding Employee: " + e.getMessage()); }

}}

5/28/2018 Ppt Db25 Oracle 04

28/33

28



Loading the Java class

loadjava -u scott/tiger@training -v -r EmpManager.class

5/28/2018 Ppt Db25 Oracle 04

29/33

29



Publishing java class methodsCREATE OR REPLACE PROCEDURE add_emp

(emp_id NUMBER,

emp_f_name VARCHAR2,

emp_l_name VARCHAR2,

emp_salary NUMBER,

dept_id NUMBER)

AS LANGUAGE JAVA

NAME EmpManager.addEmp(int, java.lang.String, java.lang.String,float, int)';

5/28/2018 Ppt Db25 Oracle 04

30/33

30



Calling Java stored procedure

SQL> SET SERVEROUTPUT ON

SQL> CALL dbms_java.set_output(2000);

SQL> EXECUTE add_emp(1,Anubhav', Pradhan',50000.00,1);

5/28/2018 Ppt Db25 Oracle 04

31/33

31



Conclusion

The integration of the database with a JVM that complies with J2SE allows

extending database features and programmability through Java Stored

Procedures.

5/28/2018 Ppt Db25 Oracle 04

32/33

32



Summary

What is Java Stored Procedure?

PL/SQL vs. Java Stored Procedures.

What are the steps to create a Java Stored Procedure?

How to load Java code elements?

What are Call specs.?

How to call Java Stored Procedures?

5/28/2018 Ppt Db25 Oracle 04

33/33

Copyright 2005, Infosys ER/CORP/CRS/DB25/003

Thank You!

ppt db25 oracle 04

Documents