Calling Java procedures from ORACLE PL/SQL

Why we need to call JAVA procedures in PL/SQL?  

JAVA one of the oldest and type safe language ever seen.It was first introduced into the IT world when C++ was not able to call remote procedures.

Almost there are thousands and thousands of logic from Hello world to complex logic fo aircrafts written in JAVA.So it is important to reuse the logic than rewriting from scratch in PL/SQL.

I have summarized the steps below to call a JAVA method from ORACLE 

Note

   Only STATIC methods can only be called  from PL/SQL as it as Modular language

Steps

1. Create a JAVA class to be loaded into Oracle database.

     Iam using our very old hello world program.Instead of printing Hello World to console, it would return the value to the user.

public class Hello 
       {   
         public static String world()   
         {     
           return "Hello world";   
          } 
        }

2. Compile the JAVA file using JAVAC

     Note

           Kindly add ORACLE_HOME/JDK/BIN in your PATH variable

Microsoft Windows XP [Version 5.1.2600] 
(C) Copyright 1985-2001 Microsoft Corp.  
C:\Documents and Settings\Administrator>javac H:\JDEV\Hello.java  

     

3. Load the JAVA files into Oracle database          

Note

   You should have added the path of ORACLE_HOME\JDK in the classpath or

it would show the driver not found error is thrown

C:\Documents and Settings\Administrator>loadjava -user store/store@shanthaguru H:\jdev\Hello.class  
C:\Documents and Settings\Administrator>  

Note

     After loading it into database , you can verify it using SQL*plus

   or SQL Developer

  

SQL * PLUS

SQL*Plus: Release 10.1.0.2.0 - Production on Sun May 17 13:07:14 2009  
Copyright (c) 1982, 2004, Oracle.  All rights reserved.   
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 
Production With the Partitioning, OLAP and Data Mining options  
SQL>  select object_name from user_objects where object_type = 'JAVA CLASS';  
OBJECT_NAME 
-------------------------------------------------------------------------------- 
Hello 

SQL DEVELOPER

    Open the JAVA node under the corresponding schema and see the loaded

JAVA Classes

   

4. Creating a PL/SQL procedure for calling the loaded the JAVA method   

SQL>  CREATE OR REPLACE FUNCTION helloworld RETURN VARCHAR2 AS   
2   LANGUAGE JAVA NAME 'Hello.world () return java.lang.String';   
3   /  Function created.

Note

   The return type should be JAVA specfic , note this in the above code.

Now try call the PL/SQL function to call the JAVA method

SQL> select helloworld from dual;  
HELLOWORLD 
-------------------------------------------------------------------- 
Hello world

       

Reading and Writing CLOB objects

Hi,

   In this post , we can look into how to read and write CLOB objects.

Why we need to use CLOB objects?

 The only downside of using VARCHAR2 is that it is limited to 4000 characters, 

so if we are creating large queries or if we you store resume or text files in database, then

we can use this CLOb, where we can store upto 2GB .

Steps

1. Creating a table containing a CLOB column

    

SQL> create table lob_contain
2  (id number,
3   lob_ptr clob default empty_clob());
Table created.

    

2. Create a procedcure to write a text to the CLOB column

Declare

lob_locator CLOB;

text varchar2(200) := 'Resume NAme - G.Subramanian KAushik City - Chennai
Location - triplicane Occupation - Software engineer Hobbies - Reading astrology
books,nemerology, Playing video games';

amount number;

offset integer;

Begin

-- Setting the amount to be written
amount := length(text);

-- Lock the record in the table lob_contain to get a expicit lock over the record
Select lob_ptr into lob_locator from lob_contain where id = 1 FOR UPDATE;

--Setting the Offset
offset := DBMS_LOB.GETLENGTH(lob_locator)+2;

--Write the text to the CLOB table
DBMS_LOB.WRITE(lob_locator,amount,offset,text);
COMMIT;
END;    
 

3. Select the inserted value as a VARCHAR2 text.

    

   SQL>  select * from lob_contain;

   ID                                 LOB_PTR

-------      ---------------------------------------------------------

   1            Resume NAme - G.Subramanian KAushik City - Chennai 

                 Location - triplicane