闲来无事,想到备份clob数据的问题,就写了两个文件来备份clob, 表结构为:
sql 代码
- create table RULESETENTITY
- (
- ACTIVE NUMBER,
- DESCRIPTION VARCHAR2(255),
- JDOCLASS VARCHAR2(255),
- JDOID NUMBER not null,
- JDOVERSION NUMBER,
- LASTMODIFIEDDATE DATE,
- LASTMODIFIEDUSER VARCHAR2(255),
- NAME VARCHAR2(255),
- TYPE VARCHAR2(35),
- XML CLOB
- )
JAVA程序为:
java 代码
- package test;
- import java.io.BufferedReader;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.io.PrintStream;
- import java.io.Reader;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import oracle.sql.CLOB;
- public class DbAccess {
- @SuppressWarnings("deprecation")
- public static void main(String args[]) throws SQLException, IOException {
- DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
- Connection conn = DriverManager.getConnection(
- "jdbc:oracle:thin:@10.6.244.5:1521:devdb", "servinv1",
- "servinv1");
- Statement stmt = conn.createStatement();
- try {
- ResultSet rs = stmt.executeQuery("select name from rulesetentity");
- while (rs.next()) {
- String name = rs.getString("name");
- Statement stmt1 = conn.createStatement();
- ResultSet rset = stmt1
- .executeQuery("select rownum,name,xml from rulesetentity where name='"
- + name + "'");
- while (rset.next()) {
- oracle.sql.CLOB clob = (CLOB) ((oracle.jdbc.OracleResultSet) rset)
- .getClob("xml");
- Reader is = clob.getCharacterStream();
- BufferedReader br = new BufferedReader(is);
- String s = br.readLine();
- FileOutputStream fo = new FileOutputStream(name + ".dri");
- PrintStream so = new PrintStream(fo);
- while (s != null) {
- so.println(s);
- s = br.readLine();
- }
- so.close();
- }
- rset.close();
- stmt1.close();
- System.out.println(name);
- }
- rs.close();
- } catch (Exception e) {
- System.out.println("Error");
- } finally {
- stmt.close();
- conn.close();
- }
- }
- }
oracle的存储过程为:
sql 代码
- create or replace procedure test(rule_name varchar2) is
- bak_file utl_file.file_type;
- rule_xml clob;
- filename varchar2(255);
- l_buffer varchar2(32767);
- l_pos INTEGER := 1;
- l_amount BINARY_INTEGER := 1000;
- xml_length integer;
- begin
- --rule_name :='ADDRESS RANGE VALIDATION';
- select xml into rule_xml from rulesetentity where name = rule_name;
- filename := replace(rule_name||'.dri',' ','_');
- bak_file := utl_file.fopen('MYTEMP',filename,'w');
- xml_length := dbms_lob.getlength(rule_xml);
- dbms_output.put_line(rule_name);
- dbms_output.put_line(filename);
- dbms_output.put_line(TO_CHAR(xml_length));
- WHILE l_pos < xml_length LOOP
- DBMS_LOB.read(rule_xml,l_amount,l_pos,l_buffer);
- UTL_FILE.put_raw(bak_file, utl_raw.cast_to_raw(l_buffer),true);
- l_pos := l_pos + l_amount;
- END LOOP;
- utl_file.fclose(bak_file);
- end;