Learning The "J"

Home » JAVA Learning » DATABASE

Category Archives: DATABASE

How to call Java stored procedures/Classes from PL/SQL

There is lot of requirements where we need to use Java like zipping the files stored in database, managing files on server.
Oracle provides the way to access Java stored Procedure and classes from PL/SQL, we can use according to our need
Following steps are required:

  1. Create the Java code elements. You can do this in Netbeans, or in any other Java Integrated Development Environment. Load the Java class(es) into Oracle using the loadjava command-line utility or the CREATE JAVA statement.
  2. Publish the Java class methods inside PL/SQL by writing wrapper programs in PL/SQL around the Java code.
  3. Grant privileges as required on the PL/SQL wrapper programs and the Java class referenced by the PL/SQL wrapper.
  4. Call the PL/SQL programs

Simple Demonstration:

1. Write Java class.

You may write the below code as a simple JAVA program and load it into database. But here we are using the 11g feature, which enables us to write java code directly on sql prompt.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Hello" AS
public class Hello
{
 public static String world()
 {
  return "Hello world";
 }
};

2. Load java class into database. We can also load a compiled .class file or .jar file using loadjava utility. In this case first step will be ignored.

C:\oracle\bin>loadjava -user scott/tiger -resolveĀ Hello.class

LoadJava utility: The loadjava utility loads Java source and class files into the database. When class files are created in a conventional manner, outside the database; loadjava is used to get them into the database.

3. Write PL/SQL Wrapper Program:

CREATE OR REPLACE
FUNCTION helloworld RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'Hello.world () return java.lang.String';
/

4. Call PL/SQL Wrapper Program:

DECLARE
my_string VARCHAR2(400 CHAR);
BEGIN
my_string := helloworld();
dbms_output.put_line('The value of the string is ' || my_string);
END;
/

Here is the syntax for loadjava utility:

loadjava {-user | -u} username/password[@database]
[-option_name [-option_name] ...] filename [filename ]...
where option_name stands for the following syntax:
{ {andresolve | a}
| debug
| {definer | d}
| {encoding | e} encoding_scheme_name
| {force | f}
| {grant | g} {username | role_name}[,{username | role_name}]...
| {oci8 | o}
| oracleresolver
| {resolve | r}
| {resolver | R} "resolver_spec"
| {schema | S} schema_name
| {synonym | s}
| {thin | t}
| {verbose | v} }

loadjava requires two database privileges to load java objects into your own schema: CREATE PROCEDURE and CREATE TABLE. To load Java objects into a schema other than the currently connected user, CREATE ANY PROCEDURE and CREATE ANY TABLE privileges are required.

If loading many Java class files at one time, it is advisable to put them in a JAR file and load them into the database at one time, since the loadjava program will also load JAR files.
Oracle has created two new roles to support Java security. For many Java-based operations within the database, you will not have to work with these roles. If, on the other hand, you want to interact with the operating system (to access or modify operating system files, for example), you need to be granted one of the following roles:

JAVASYSPRIV
JAVAUSERPRIV.

You grant these roles as you would any other database role. For example, if I want to allow SCOTT to perform any kind of Java-related operation, I would issue this command from a SYSDBA account:

GRANT JAVASYSPRIV TO SCOTT;
GRANT EXECUTE ON SYS.DBMS_JAVA TO SCOTT

 

Here is an another example that is acting as a TCP/IP client socket and sends data to a TCP/IP server.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "CounterDisplay" AS

import java.io.*;
import java.io.*;
import java.net.*;

       public class CounterDisplay
       {
              public static String display(String ip,int port,String counternumber,String counter)
              {
                     String errmsg="Working Fine";
                     try{
                            Socket clientSocket = new Socket(ip, port);
                            DataOutputStream outToServer = new DataOutputStream(clientSocket.getOutputStream());
                            String dataToDisplay = counter+counternumber;
                            outToServer.write(dataToDisplay.getBytes());

                            clientSocket.close();
                            outToServer.close();                            
                     }
                     catch(Exception e){
                         errmsg=e.getMessage();            
                         return errmsg;
                     }
                     return errmsg;
              }
       }
  
--******************************************************************************
CREATE OR REPLACE 
FUNCTION COUNTERDISPLAY_FUNC(IP IN VARCHAR2,PORT IN NUMBER,COUNTERNUM IN VARCHAR2,COUNTER IN VARCHAR2) RETURN VARCHAR2 AS
         LANGUAGE JAVA NAME 'CounterDisplay.display(java.lang.String,int,java.lang.String,java.lang.String) return java.lang.String';
--******************************************************************************
DECLARE  on_completion VARCHAR2(400);
    BEGIN
       on_completion :=COUNTERDISPLAY_FUNC('192.192.21.42',5100,'5','503');
       dbms_output.put_line('' || on_completion);
    END;
--******************************************************************************
Advertisements