使用java from CLOB OUT参数从oracle中的存储过程中获取值

Question

我有plsql/sql存储过程，比如myPackage.myProcedure.

我在sqldeveloper中得到了它的价值。

但无论我做什么，当用JAVA调用该过程时，我都无法获得clob值。

这是我的JAVA代码

Connectionmanager conn = new Connectionmanager();
  OracleConnection conn1=null;
    System.out.println("conn obj "+conn);
    String docId = "xxxxxx";
    String disptype ="xxxx";
    String returnvalue="";
    Clob myClob = conn.returnClob();
   // CLOB clob = conn1.CreateClob();
    String doctype = "xxxx";
   // dbms_lob.createtemporary(OUTPUT_VALUE, true);
    //CLOB myClob = null;
    Connection con = conn.connectToDB();
    //Connection cox = DriverManager.getConnection(docId)
    try{
        String stmt="BEGIN " + "dbms_lob.createtemporary(OUTPUT_VALUE, true); MYPACKAGE.MYPROCEDURE(?,?,?,?);" + "END;";
    CallableStatement statement =con.prepareCall(stmt);
    ResultSet rs=null;
    statement.setString(1, docId);
    statement.setString(2, disptype);
    //call.setNull(1, Types.CLOB);
     statement.registerOutParameter(3, Types.CLOB);
     // here, the CLOB will be parsed into a string*
/*StringBuilder strOut = new StringBuilder();
String aux;
BufferedReader br = new BufferedReader(statement.getClob(3).getCharacterStream());
while ((aux=br.readLine())!=null)
strOut.append(aux);
// get xml text from function, one has been executed* 
returnvalue = strOut.toString();*/
   // statement.getClob(3);
    //statement.setNull(3, Types.CLOB);
   // statement.registerOutParameter(3, Types.ARRAY, typeTableName);
   // statement.registerOutParameter(3,java.sql.Types.CLOB);
    statement.setString(4, doctype);
   rs= statement.executeQuery();
     //   System.out.println("out put is "+statement.getString(2));
    }catch(Exception e)
    {
        System.out.println("e is "+e);
    }

我已经评论了我试图获得clob数据的所有方法。

我得到的错误是

java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275

有人能帮我吗？我甚至试过这个查询

String stmt="DECLARE OUTPUT_VALUE CLOB; BEGIN " + "dbms_lob.createtemporary(OUTPUT_VALUE, true); PO_WF_PO_NOTIFICATION.GET_PO_LINES_DETAILS(?,?,?,?);" + "END;";

请帮帮我，我真的需要这个

Answer 1

dbms_lob.createtemporary应该在包过程中。

public static void main(String[] args) throws SQLException, Exception {
    Connection con = ConnectionDefinition.getOracleConnection(); //my oracle connection
    CallableStatement cs = con.prepareCall("declare n"
            + " procedure MYPROCEDURE(p1 varchar2,p2 varchar2,p3  out clob,p4 varchar2) is n"
            + " begin n"
            + "       dbms_lob.createtemporary(p3, true); n"
            + "       dbms_lob.append(p3,p1||' - '||p2||' - '||p4);n"
            + " end; n"
            + "BEGINn"
            + "  MYPROCEDURE(?,?,?,?);n"
            + "END; n"
            + "    n"
            + "");
    cs.setString(1, "first param");
    cs.setString(2, "secound param");
    cs.registerOutParameter(3, Types.CLOB);
    cs.setString(4, "four param");
    cs.execute();
    Clob cl = cs.getClob(3);
    BufferedReader br = null;
    StringBuilder sb = new StringBuilder();
    String line;
    try {
        br = new BufferedReader(new InputStreamReader(cl.getAsciiStream()));
        while ((line = br.readLine()) != null) {
            sb.append(line);
        }
    } catch (IOException e) {
    } finally {
        if (br != null) {
            try {
                br.close();
            } catch (IOException e) {
            }
        }
    }
    System.err.println(sb.toString());
}