--以逗号分隔 -- Call the EXTRACT_BLOB procedure -- p_gfm_id是fnd_lobs主ID，P_FILE_CS是GB，CHR(P_DELIMITER) --CHR(9) extract_blob(p_gfm_id, p_file_cs, chr(p_delimiter), p_working_id); DELETE FROM fnd_lobs WHERE file_id = p_gfm_id; ins_interface; DELETE cux_ce_stmt_temp WHERE working_id = p_working_id; COMMIT; END IF; --END IF; IF retcode = 2 THEN log(\'retcode = 2\'); v_b_return := fnd_concurrent.set_completion_status(\'ERROR\', \'ERROR\'); ELSIF retcode = 1 THEN log(\'retcode = 1\'); v_b_return := fnd_concurrent.set_completion_status(\'WARNING\', \'WARNING\'); END IF; -- EXCEPTION WHEN OTHERS THEN DELETE cux_ce_stmt_temp WHERE working_id = p_working_id; log(\'exception occurs during program running.\'); log(\'SQLCODE:\' || SQLCODE); log(\'SQLERRM:\' || SQLERRM); retcode := 2; v_b_return := fnd_concurrent.set_completion_status(\'ERROR\', \'ERROR\'); RAISE; END main; END cux_ce_stmt_upload; /
Java代码：convertlobcs，需要编译到数据库中。 create or replace and compile java source named convertlobcs as import java.lang.* ; import java.io.*; import java.sql.* ; import oracle.jdbc.* ; import oracle.sql.* ; public class ConvertLOBCS { public static BLOB convertBlob ( BLOB inBlob, String sourcecs, String destcs ) throws SQLException, IOException { Reader in = new InputStreamReader( inBlob.getBinaryStream() , sourcecs ); Connection conn = DriverManager.getConnection( ) ; BLOB outBlob = BLOB.createTemporary( conn, true , BLOB.DURATION_CALL) ; Writer out = new OutputStreamWriter( outBlob.getBinaryOutputStream() , destcs ); int c; while ((c = in.read()) != -1) out.write(c); out.close(); in.close(); return outBlob ; } public static CLOB convertClob ( BLOB inBlob, String sourcecs ) throws SQLException, IOException { Reader in = new InputStreamReader( inBlob.getBinaryStream() , sourcecs ); Connection conn = DriverManager.getConnection( ) ; CLOB outClob = CLOB.createTemporary( conn, true , CLOB.DURATION_CALL) ; Writer out = outClob.getCharacterOutputStream() ; // Writer out = outClob.getAsciiOutputStream() ; int c; while ((c = in.read()) != -1) out.write(c); out.close(); in.close(); return outClob ; } public static String convertStr ( BLOB inBlob, String sourcecs ) throws SQLException, IOException { Reader in = new InputStreamReader( inBlob.getBinaryStream() , sourcecs ); StringBuffer outStr = new StringBuffer() ; int i, c ; i = 0 ; while ( ( (c = in.read()) != -1 ) && i < 1000 ) { outStr.append((char)c); i++ ; } in.close(); return outStr.toString() ; } }