Chapter 29
Using Oracle8 Objects in PL/SQL and SQL

by Advanced Information Systems, Inc.

In This Chapter

•  The Advantages of Using PL/SQL Within Java

•  Different Ways That Java Can Package PL/SQL

The Advantages of Using PL/SQL Within Java

There is nothing fundamentally different about embedding PL/SQL in a traditional language than doing it in Java. The only difference is that Java addresses the new development standards for the Internet and the World Wide Web. Java is the code that allows you to write applications using the HTTP protocol that will run on any machine with a Java Virtual Machine (JVM).

For instance, this would allow the ability to write an applet over the Web that sold airplane tickets. Users on Apple Macs, PCs, UNIX Machines, and DEC VMS software could all run browsers, surf the Web, and buy airplane tickets from your company.

With many typical approaches, the logic for searching through the database, booking an airline flight, and handling credit card payments would exist in the Java applet. This would create a larger amount of Java code, in the form of compiled .CLASS files. A longer download time would be necessary because the user’s browser would need to download your applet before running it.

Instead you might consider moving a great deal of the applet logic into our business database. This database is already storing transactions made over the Web, storing customer information, and querying flight schedules. The business database will still need to be accessed regardless of where your business and process logic is placed. By storing much of the logic in your database, you reduce the size of the applet and speed up your Web application by reducing network traffic, as shown in Figure 29.1.

Figure 29.1.  Database logic inside a Java applet creates a network bottleneck, whereas Java with remote calls to PL/SQL results in a thinner applet.

In Oracle, the ideal way to store application logic is by using PL/SQL. Because PL/SQL is a rich, quasi-object-oriented and highly structured language, complex logical algorithms can be written using it. Furthermore, much of what we call application logic is simply different SQL calls that represent different views or operations on data. By storing this logic using PL/SQL, you have a seamless SQL interface and thus save processing speed and simplify the maintenance of source code.

With these advantages, most Java/Oracle application efforts would benefit from database logic stored separately from the Java Applet and instead packaged in PL/SQL.

Consider the following small package skeleton of PL/SQL logic:

package sell_airline_tickets

is



function get_next_flight_no(departure_date in date, origin in

varchar2, destination varchar2)

return varchar2;



function book_flight(flight_no in number, passenger_name in varchar2)

return varchar2;



function get_price(flight_no in number, class_of_travel in varchar2)

return

number;



procedure update_frequent_flier_miles(card_id in number, flight_no 

in number);

end;

Here you see three activities that might contain a great deal of code; activities that find airline flights, book them, and get their price. Instead of placing all of the conditional logic, looping, and database logic in a Java application, you can code these modules in PL/SQL and then simply access them in the Java code.

This simplifies your Java code and moves the database and business logic into stored procedures within the database. By doing this, you gain the following advantages:

1.  The SQL code in PL/SQL is already parsed in a stored procedure. This type of SQL code is easier to manage, and is much faster than Java code using SQL run through JDBC.

2.  You centralize database code in your database, thus hiding the methods of these business functions from the Java applets. By doing this, you eliminate sifting through Java source files whenever you want to fix database code.

3.  If your database changes, you only need to change the PL/SQL code. In many cases the Java source files need not be changed.

Even with PL/SQL, the Java applet needs JDBC to access a package, but the amount of code needed is far less than would be the case if Java was preparing and issuing SQL through JDBC. Most JDBC interfaces will allow an interface to access Oracle.

In the sample database interface, the Java applet would only need to log in to use the business logic in the PL/SQL packages. To do this, Java uses a class called DriverManager and a method called getConnection. The method has three forms:

getConnection(String url);

getConnection(String url, Properties props);

getConnection(Sring url, String userid, String password); 

Let’s populate the second form and the all-important Properties class. Using an Oracle driver, you would write Java code like this:

java.util.Properties props = new java.util.Properties();



props.put(“host”, “ORACL”);

props.put(“user”, “airline”);

props.put(“password”, “echo”);   /* our password is echo */

java.sql Connection DbConn =

DriverManager.getConnection(“jdbc.Oracle8, props);

To call procedures or functions from Oracle does not require some of the complex JDBC commands needed just to execute one SQL statement. A Java applet can execute hundreds of SQL statements within a PL/SQL package using only the simple call method.

The method has this general form:

call procedure_name(arg1, arg2...argn);

Or to return a value from a function:

var = call function_name(arg1, arg2...argn);

This Java class allows us to use the PL/SQL function

‘update_frequent_flier_miles’ even within HTML text once we have defined

its existence using the above methods of the DriverManager.  For

instance, to display the number of frequent flier miles that a  customer

has on an HTML page we would make the following call:



<FONT  COLOR=”#000000” SIZE=3>String SQL =

“{call update_frequent_flier_miles( “+ card_id + “,” + flight_no + “)}”;

After this step, you prepare your new buffer using JDBC:

java.sql.CallableStatement my_statement = DbConn.prepareCall(SQL);

If you want, you can specify the name of the procedure more directly. Instead of calling update_frequent_flier_miles, you could use the schema and/or package name, like this:

call airline_db.sell_airline_tickets.update_frequent_flier_miles,

This enables Java code to access logic from many different schemas and packages, not just ones that the developer wrote.