c oracle clob 读写,oracle里读写clob

闲来无事，想到备份clob数据的问题，就写了两个文件来备份clob,

表结构为：

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程序为：

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的存储过程为：

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;