Java & PL/SQL: For Oracle RDBMS

JAVA & PL/SQL
For Oracle RDBMS (8i & 9i)

By Joel A. Thompson ([email protected])
www.rhinosystemsinc.com
05/2004
copyright 2004
all rights reserved www.rhinosys

temsinc.com
INTRODUCTION
Joel
A. Thompson
15 years of industry experience
Consultant: Architecture, Software
Engineering and Project Manager
Java and Oracle expert
copyright 200
all rights reserved w
OVERVIEW
Basics
of PLSQL
Optimizing RDBMS Calls from JAVA
Return Values & Cursors
Logic processing on Server, ARRAY inserts
And more
Examples
WRAP
UP / Q&A
copyright 200
Getting Started with Java

Must
install a JDK & IDE
JDK (Java Developers Kit) from Sun

http://java.sun.com/j2se/1.4/index.html
IDE (Integrated Developers Environment)
Oracles JDeveloper (technet.oracle.com)
IntelliJ: (www.intellij.net)
Textpad: http://www.textpad.com
copyright 200
Getting Started with Oracle
Server Install Oracle (Steps)

Download/Install from technet.oracle.com
Create the database, use Oracle's Database Configuration
Assistant.
Make sure network listeners are started typically your
instance will be available on port 1525 use Oracle's Net
Configuration Assistant, to configure a "Listener".
Client Requirement
Copy the $ORACLE_HOME/jdbc/lib/classes12.jar to your
client side's CLASSPATH.
Connect string example (thin driver) requires no client side
libraries: jdbc:oracle:thin:joel/welcome@localhost:1521:UCDBA
copyright 200
Client Server Java/PLSQL Architecture
copyright 200
What is PL/SQL?
A programming
language
If/then/else
Loops
Function calls
Transactional
Procedural
Feature
rich
Performance Gain
Oracle only
copyright 200
Some Good Reasons to use

PL/SQL
Basically you'd like to do some logic on the server side in one

call to the database
Transaction support within the PL/SQL
Temporary table queries/inserts to filter data further before
returning result set
Take advantage of server side resources while processing you
PL/SQL send message out through Oracle's Advanced
Queuing.
Distributed Computing update another database
You want to abstract your database layer into Oracle, such
that you can change the table's or columns around without
affecting the client program.
copyright 200
Ways to call PL/SQL
ADHOC
Send the PL/SQL block of code from the client java
program to the server for processing.
FUNCTION
Client Side calls a PL/SQL function in Oracle.
PL/SQL is already compiled and loaded in Oracle.
Function will return a value
PROCEDURE
Client calls a PL/SQL procedure
Similar to function, but does not return values.
copyright 200
ADHOC: Java Client Example 1

SQL
embedded in call from Java Client
// EXAMPLE OF SIMPLE SELECT STATEMENT

Statement stmt=connection.createStatement();
// QUERY THE ENTIRE TABLE
String sSQL="select ACCOUNT_ID,SSNO,FNAME,LNAME,PHONE from PERSON order by account_id";
// GET THE RESULT SET AND PROCESS IT.
ResultSet rs=stmt.executeQuery(sSQL);
while(rs.next())
{
nPERSON_ID=rs.getInt(1);
sSSNO=rs.getString(2);
/// check to see if matches with new account you'd like to add.
}
// IF ACCOUNT DOESN'T ALREADY EXIST, THEN ADD IT HERE:
stmt.executeUpdate("insert into PERSON(ACCOUNT_ID,SSNO,FNAME,LNAME) values(account_id_seq.nextval," +
"'" + sSSNO + "'," + "'" + sFNAME + "'," +"'" + sLNAME + "'");
copyright 200
10
Issues Regarding Example 1

Selecting
from entire table is generally a

bad idea. (unless small "lookup table").
One call for the Query, and 2 nd call to
create the account.
copyright 200
11
Prepared Statement/Bind from Java Client

public void initializeOnce()
{
// THE SQL INSERT TO BE MADE.
String sQueryNotify="insert into PERSON (PERSON_ID,SSNO,FNAME,LNAME)" +
" values (person_id_seq.nextval,?,?,?)";
// CREATE A PREPARED STATEMENT, BASED ON THE ABOVE SQL
m_psInsertPerson=conn.prepareStatement(sQueryNotify);
}
public void insertNewPerson(String SSNO,String FNAME,String LNAME)
{
// BIND THE PARAMETER - REUSE THE ALREADY CREATED PREPARED STATEMENT
m_psInsertPerson.setString(1, SSNO);
m_psInsertPerson.setString(2, FNAME);
m_psInsertPerson.setString(3, LNAME);
// EXECUTE THE INSERT
m_psInsertPerson.executeUpdate();
return;
}
copyright 200
12
Issues Regarding Example 2
Good Created the preparedStatement once

at initialization time & then binding values in
function.
Loader routine purpose? still worse
performance than a SQL batch processing.
Does not check to see if account exists
already.
GOOD uses sequence number generation
from oracle
copyright 200
13
Query based on Prepared Statement

{ // IN CONSTRUCTOR or INITIALIZATION BLOCK
// THE SQL QUERY TO BE MADE.
String sQueryNotify="select email_author,notification,subject,s.name as name " +
" from project p, status s " +
"
where p.id=? " +
" and s.id=p.status_id";
// CREATE A PREPARED STATEMENT, BASED ON THE ABOVE SQL
m_psLookupProjectID=conn.prepareStatement(sQueryNotify);
}
public EmailInfo queryEmailInfo(int id)
{
// BIND THE PARAMETER
m_psLookupProjectID.setInt(1,id); // REUSE THE ALREADY CREATED PREPARED STATEMENT
// GET THE RESULT SET AND PROCESS IT.
ResultSet rs=m_psLookupProjectID.executeQuery();
return info;
}
copyright 200
14
ADHOC: PL/SQL Example 4

PreparedStatement ps=null;
sSQL=new String("declare" +
"
l_id number:=0;" +
"
ret number:=0;" +
"
lssno varchar2(32):=?;" +
"
error_msg varchar2(1026):=null;" +
" BEGIN" +
" select count(*) into ret from person where SSNO= lssno ;" +
" if(ret=0) then" +
" insert into person(id,ssno) values(seq_id.nextval, lssno) returning id into ret;" +
" end if;" +
" ?:=ret;" +
" EXCEPTION WHEN OTHERS THEN" +
" ?:=SQLERRM;" +
" END;");
3
4
ps =connection.prepareStatement(sbSQL.toString());
int indx=1;
ps.setString(indx++,SSNO);
int rettype=Types.INTEGER;
ps.registerOutParameter(indx++,rettype);
rettype=Types.VARCHAR;
ps.registerOutParameter(indx++,rettype);
ps.executeUpdate();
l_sError=ps.getString(2);
if(l_sError!=null)
{
System.err.println("PL/SQL Error: " + l_sError);
return;
}else
{
ret=ps.getInt(1);
System.out.println("New USERID: " + ret);
}
copyright 200
15
Analysis of Example 4
Refer to labels in red from example.

1) Notice that "DECLARE" begins our PL/SQL code.
You don't need a DECLARE section, however, you
can setup variables here by binding them to Java
variables on your client side. Also notice the BEGIN
statement, which is matched with the "END;"
statement in #4.
DECLARE
BEGIN
END;
copyright 200
16

2) Test to see if the person exists before inserting
into the table. (notice the RETURNING clause on the

insert).
3) If there are any exceptions that are thrown in the
surrounding block, then execute this line of code
assign the ERROR MESSAGE (SQLERRM) to the
return message
4) The "END" tag is used to close the BEGIN - Also
notice the usage of ";" after the END tag also notice
that we finished the string.
copyright 200
17

5) Use an existing SQL connection to create the callable
statement a prepared statement, used to bind input variables

and output variables.
Notice the index variable declared and assigned value of 1.
6) Bind the SSNO string to the first occurance of a "?" in the
PLSQL. (what will happen if SSNO string's length is greater than

32?).
Next few lines we register the output variables. (NOTICE how
they match up with the ?).
One is of type INTEGER the other VARCHAR.
copyright 200
18

7) We executed the SQL statement, and now check
for errors.
If we find an error Message (ie not null), then we will
report it, and return immediately. ELSE we'll continue
and report the new userid (created from a sequence
number in Oracle).
copyright 200
19
Issues from Example 4

Constructing
SQL on the client side.
Need to recompile in order to change sql

Not very portable.
Not modular
Trade
offs?
Plain SQL insert and catch duplicate key

exception need to make sure your Primary
KEY or unique indices are setup properly.
copyright 200
20
FUNCTION/PROCEDURE CALL Example 5

Call
a function, that processes some

data and returns a result set.
KEY THINGS TO NOTE:
Minimize SQL code on client side.
Ready for portability
Within Oracle yes
Using other database perhaps
SQL 92 syntax?
copyright 200
21
Example 5 Syntax consideration
JDBC SYNTAX For portability

{?= call <procedurename>[<arg1>,<arg2>, ...]}
{call <procedure-name>[<arg1>,<arg2>, ...]}
ORACLE SYNTAX (not portable)

BEGIN ?:=<procedurename>[<arg1>,<arg2>, ...]; END;
copyright 200
22
Example 5 PL/SQL server code

Load
the following into oracle once at

install time
Declare function in a package (not
necessary but good to do).
CREATE OR REPLACE PACKAGE api_person_pkg AS
TYPE T_REFCURSOR IS REF CURSOR;
-- Define a cursor reference.
FUNCTION getPeople (VARCHAR2) RETURN T_REFCURSOR;
END api_person_pkg;
/
copyright 200
23
Example 5 PL/SQL server code
Create the body of the package/function
CREATE OR REPLACE PACKAGE BODY api_person_pkg AS

FUNCTION function getPeople(p_filter varchar2:=null) RETURN T_REFCURSOR
IS
v_ret_cursor API_PERSON_PKG.T_REFCURSOR;
BEGIN
if (p_filter is null) then
OPEN v_ret_cursor FOR
'select * from person';
else
OPEN v_ret_cursor FOR
'select * from person where username like ''%' || p_filter || '%''';
end if;
RETURN v_ret_cursor;
END;
END api_person_pkg;
/
Consider using global temporary table. Create the table at install

time, then use the table to insert records into from different tables the
records are only visible to current session. On commit records go away.
copyright 200
24
Example 5 PLSQL
Test code to test the PL/SQL

SQL> set serveroutput on;
SQL> declare
v_ret_cursor API_PERSON_PKG.T_REFCURSOR;
v_id number; --PERSON ID
v_name varchar2(120); -- the username
begin
v_ret_cursor:=API_PERSON_PKG.getPeople('jthomps');
LOOP
FETCH v_ret_cursor into v_id ,v_name;
EXIT WHEN v_ret_cursor%NOTFOUND;
dbms_output.put_line('values retrieved =' || v_id || ',' ||
v_name);
END LOOP;
END;
/
copyright 200
25
Example 5 java client code
Call a function to return a result set.

try
{
String sql=new String("{ ? = call api_person_pkg.getPeople('joelt') }");
// could have used "BEGIN ?:= api_person_pkg.getPeople('joelt'); END;"
final int cursorRefType=oracle.jdbc.driver.OracleTypes.CURSOR;
cs=m_Conn.prepareCall(sql);
cs.registerOutParameter(1,cursorRefType);
cs.execute();
rs=(ResultSet)cs.getObject(1);
int ColNameIndex=1;
ResultSetMetaData rsmd=rs.getMetaData();
String ColName1=rsmd.getColumnName(ColNameIndex++);
String ColName2=rsmd.getColumnName(ColNameIndex);
System.out.println(ColName1 + "," + ColName2);
while(rs.next())
{
l_sPersonID=rs.getString(1);
l_sUsername=rs.getString(2);
System.out.println(l_sPersonID + "," + l_sUsername);
}
}catch(SQLException sqle)
{ // ...
System.out.println("Error: " + sqle.getMessage());
}finally
{
rs.close();
cs.close();
try{ m_Conn.close(); } catch(Exception e){}
}
copyright 200
26
Example 6 objective
Java to insert/update record with stored

procedure, showing how to create primary
key & foreign key records using Oracle's
sequences.
copyright 200
27
Example 6 create the STR_VARRAY type
You need to create a type to hold the

array. This type will be referenced in
the PL/SQL procedure as a
parameter type, and in the Java
Code.
create or replace TYPE str_varray AS VARRAY(10) OF VARCHAR2(5)

/
copyright 200
28
Example 6 stored procedure
Create the stored procedure

PROCEDURE storePersonCar(p_username varchar2,p_Cars str_varray)
AS
cnt number:=0;
id person.id%TYPE;
indx integer;
BEGIN
select count(*) into cnt from person where username=p_username;

BEGIN
if ( cnt = 0 ) then
insert into person(id,username) values(seq_person_id.nextval,p_username) returning id
else
select id into id from person where username=p_username;
delete from cars where person_id=id;
end if;
indx:=p_Cars.FIRST;
while indx <= p_Cars.LAST LOOP
insert into cars values(id,p_Cars(indx));
indx:=p_Cars.next(indx);
END LOOP;
into id;
commit;
EXCEPTION WHEN OTHERS then
rollback;
END;
END;
/
copyright 200
29
Example 6 Java Code

import java.sql.*;
import oracle.sql.*;
PreparedStatement ps =null;
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("JOEL.STR_VARRAY", m_Conn);
String sCar="";
String cars[]=new String[10];
for(int i=0;i<10;i++)
{
sCar="carX"+i;
// CONTRIVE A NAME of a CAR
cars[i]=new String(sCar);
}
ARRAY array3 = new ARRAY (desc, m_Conn, cars);
String sql=new String("{call api_person_pkg.storePersonCar('joelt',?) }");
ps= m_Conn.prepareStatement(sql);
// Set the values to insert
((oracle.jdbc.driver.OraclePreparedStatement)ps).setARRAY(1, array3);
// Insert the new row
ps.executeUpdate();
m_Conn.commit();
copyright 200
30
Example 6 Java Notes

Make
sure to include classes12.jar and

nls_charset12.jar both found in
$ORACLE_HOME/jdbc/lib directory.
This example works with Oracle
9.2.0.1.0 on Windows XP.
Java version 1.4.2_03
copyright 200
31
Portability & Compatibility issues

SQL
Standards
JDBC standards
Other issues (dates, functionsetc.)
Portability from schema-to-schema
abstracting calls to database with PL/SQL
layer
copyright 200
32
WRAP-UP
QUESTIONS
and Answers
MORE INFORMATION:
Contact: [email protected]
Or call: 530-888-6248 x205
copyright 200
33

Java & PL/SQL: For Oracle RDBMS

Uploaded by

Copyright:

Available Formats

Java & PL/SQL: For Oracle RDBMS

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Java & PL/SQL: For Oracle RDBMS

Uploaded by

Copyright:

Available Formats

JAVA & PL/SQL

For Oracle RDBMS (8i & 9i)

all rights reserved www.rhinosys

all rights reserved w

all rights reserved w

Getting Started with Java

install a JDK & IDE

JDK (Java Developers Kit) from Sun

all rights reserved w

Getting Started with Oracle

Server Install Oracle (Steps)

all rights reserved w

Client Server Java/PLSQL Architecture

all rights reserved w

all rights reserved w

Some Good Reasons to use

Basically you'd like to do some logic on the server side in one

all rights reserved w

Ways to call PL/SQL

all rights reserved w

ADHOC: Java Client Example 1

embedded in call from Java Client

// EXAMPLE OF SIMPLE SELECT STATEMENT

all rights reserved w

Issues Regarding Example 1

from entire table is generally a

all rights reserved w

ADHOC: Java Client Example 2

Prepared Statement/Bind from Java Client

all rights reserved w

Issues Regarding Example 2

Good Created the preparedStatement once

all rights reserved w

ADHOC: Java Client Example 3

Query based on Prepared Statement

all rights reserved w

ADHOC: PL/SQL Example 4

all rights reserved w

Refer to labels in red from example.

all rights reserved w

Refer to labels in red from example.

into the table. (notice the RETURNING clause on the

all rights reserved w

Refer to labels in red from example.

statement a prepared statement, used to bind input variables

6) Bind the SSNO string to the first occurance of a "?" in the

PLSQL. (what will happen if SSNO string's length is greater than

all rights reserved w

Refer to labels in red from example.

all rights reserved w

Issues from Example 4

SQL on the client side.

Need to recompile in order to change sql

Plain SQL insert and catch duplicate key

all rights reserved w

FUNCTION/PROCEDURE CALL Example 5

a function, that processes some

all rights reserved w

Example 5 Syntax consideration