闲来无事,想到备份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
-
- 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;
-
分享到:
相关推荐
JDBC读写Oracle的CLOB字段
向Oracle数据库插入Clob大段文本解决方法
oracle中使用jdbc读写clob字段,很多细节介绍,内容全面。
oracle Blob转换Clob
把oracle数据库中字段类型为clob的字段值以字符串的形式读取出来
ORACLE中CLOB字段转String类型
Oracle如何插入CLOB字段值,附件为简单的示例代码。Oracle如何插入CLOB字段值,附件为简单的示例代码。
Oracle导出Clob,Blob工具 ,支持导出CLob工具版本2,解决上个版本导出时间有问题的bug
java操作oracle clob,基础教程,教你clob在java中的用法,简单易学。
NULL 博文链接:https://shihuan830619.iteye.com/blog/1662937
运用Java如何存取Oracle中的CLOB类型字段
Oracle导出Clob,Blob等大字段工具,自己写的工具,方便大家下载使用
本文讲解Oracle如何对CLOB行字段来执行全文检索。
java操作oracle clob,基础教程,教你clob在java中的用法,简单易学。
weblogic下转换oracle 的clob类型经常报转型错误,本例子通过反射解决weblogic.jdbc.wrapper.Clob_oracle_sql_CLOB转型成oracle.sql.CLOB问题
oracle Blob Clob 大数处理 代码 文件上传 下载
UTL_RAW.CAST_TO_VARCHAR2
简单写的一个小工具,把图片存入oracle中,按clob和blob两种方式存储,并读取图片