CallableStatement with PL/JSON and CLOBs

[Jonas Krogsboell]

Example on how to access PL/JSON functions from JDBC with CallableStatement.
In order to run the example you will need ojdbc14.jar (or newer) in your classpath.

package tests;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.sql.BLOB;
public class PLJSONtest {
    public static void main(String[] args) throws Exception {
        Connection conn = ...
        int m = 2000; //ORA-06502: stringbuffer too small
        /* plsql json function:
         * 
         * function cstest(m number) return json_list as
         *   ret json_list := json_list();
         * begin
         *   for x in 1 .. m loop
         *     ret.add_elem(x);
         *   end loop;
         *   return ret;
         * end;
         * */
        /* Example 1 */
        CallableStatement cs1 = 
            conn.prepareCall("{? = call cstest(?).to_char(false)}");
        cs1.registerOutParameter(1, Types.VARCHAR);
        cs1.setInt(2, m);
        cs1.execute();
        System.out.println(cs1.getString(1).length());
        cs1.close();
        /* Example 2 */
        CallableStatement cs2 = 
            conn.prepareCall("{? = call cstest(?).to_char(false)}");
        cs2.registerOutParameter(1, Types.CLOB);
        cs2.setInt(2, m);
        cs2.execute();
        System.out.println(cs2.getString(1).length());
        cs2.close();
        m = 8000;
        /* Example 3*/  //avoid stringbuffer too small issue 
        OracleCallableStatement cs3 = 
            (OracleCallableStatement)conn.prepareCall(
                "{call wii.cstest(?).to_clob(?, false)}"
            );
        cs3.setInt(1, m);
        cs3.registerOutParameter(2, OracleTypes.CLOB);
        oracle.sql.CLOB json = oracle.sql.CLOB.createTemporary(conn, true, BLOB.DURATION_SESSION );  
        cs3.setCLOB(2, json);
        cs3.executeQuery();
        json = cs3.getCLOB(2);
        String result = stream2String(json.asciiStreamValue());
        json.freeTemporary();
        System.out.println(result.length());
        cs3.close();
    }
    public static String stream2String(InputStream is) throws Exception {
        BufferedReader reader = new BufferedReader(new InputStreamReader(is));
        StringBuilder sb = new StringBuilder();
        String line = null;
        while ((line = reader.readLine()) != null) {
            if(sb.length() != 0) sb.append('\n');
            sb.append(line);
        }
        is.close();
        return sb.toString();
    }
}