CallableStatement with PL/JSON and CLOBs

2010-10-20
2013-05-28
  • 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();
        }
    }