Type Matching between PL/SQL and Java

PL/SQL is great for some things, but built for a somewhat specific environment. Lately, I've needed to get some extra functionality and have turned to Java for things.

Problem:
Call a Java method from PL/SQL that will in turn make a call to a web service and return large amount of data that can then be used in PL/SQL.

Initial Problem Analysis:
-The amount of data that will be returned from Java is greater than a PL/SQL VARCAHR2, so we must use a clob.
-The java.sql.clob constructor is protected in Java because it was meant to be used in conjunction with a java.sql.ResultSet. This stumped me for a while, but I ended up passing an initialized clob from PL/SQL that I could then write to and pass back. In the end however, I ended up needing to pass so much data to the web service that I used the clob is input as well as output.

Getting started.
First off, however PL/SQL is an object-oriented programming language, you will not be initializing a Java class in your PL/SQL code, this was odd to me at first due to my experience being mostly with Java and C++. Without access to the constructor, variables you want to use in multiple methods must be passed in the single method you call from PL/SQL and assigned to static variables that are in the class scope. This is a small inconvenience here but could be huge in other cases.

Basic Structure:

create or replace and compile java source named CLOB_TEST as

import java.sql.Clob;

public class clobTest {

  public static Clob getResponse(Clob p_clob) throws Exception {
    // pull the string from the clob
    String stringAsClob = p_clob.getSubString(1, (int) p_clob.length());

    // make the string clean so you can write back to it.
    p_clob.truncate(0);
    
    // ...
    //mess with the string
    // ...
    
    //stuff the string back into the clob
    p_clob.setString(1, stringAsClob);
    return p_clob;
  }
}

You will need a function like this in Oracle that you will be calling in you PL/SQl code.

CREATE OR REPLACE FUNCTION JAVA_CLOB_TEST(P_CLOB IN CLOB) RETURN CLOB AS
              LANGUAGE JAVA NAME 'clobTest.getResponse( java.sql.Clob ) 
              return java.sql.Clob';

And finally, your PL/SQL code will look something like this.

DECLARE
              TEMP_CLOB CLOB;
BEGIN
              --Even if you dont need to pass data in the clob, you must initialize it.
              TEMP_CLOB := 'input data';
              TEMP_CLOB := JAVA_CLOB_TEST(P_CLOB => TEMP_CLOB);
END;