精华内容
下载资源
问答
  • Oracle接收长度大于4000的字符串 Oracle接收长度大于4000的字符串
  • com.oracle</groupId> <artifactId>ojdbc</artifactId> <version>11.02</version> </dependency> <dependency> <groupId>cn.hutool</groupId> <...

    1.pom.xml

    <dependency>
    	<groupId>com.oracle</groupId>
    	<artifactId>ojdbc</artifactId>
    	<version>11.02</version>
    </dependency>
    <dependency>
    	<groupId>cn.hutool</groupId>
    	<artifactId>hutool-all</artifactId>
    	<version>4.6.10</version>
    </dependency>
    

    2.数据库配置文件,db.setting,放在maven项目的src/main/resources下

    url = jdbc:oracle:thin:@127.0.0.1:1521:orcl
    user = xxx
    pass = xxx
    ## 可选配置
    # 是否在日志中显示执行的SQL
    showSql = true
    # 是否格式化显示的SQL
    formatSql = false
    # 是否显示SQL参数
    showParams = true
    

    3.java代码

    import java.io.File;
    import java.sql.Connection;
    import java.sql.Date;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.Time;
    import java.sql.Timestamp;
    import java.sql.Types;
    import java.util.Arrays;
    import java.util.stream.Collectors;
    
    import cn.hutool.core.date.DateUtil;
    import cn.hutool.core.io.FileUtil;
    import cn.hutool.core.text.StrSpliter;
    import cn.hutool.db.Db;
    import oracle.sql.CLOB;
    
    public class OracleBigDataToSql {
    	public static void main(String[] args) {
    		Connection connection = null;
    		PreparedStatement ps = null;
    		ResultSet rs = null;
    		String tableName = "你的表名";
    		try {
    			File sqlFile = null;
    			connection = Db.use().getConnection();
    			ps = connection.prepareStatement("select * from " + tableName);
    			rs = ps.executeQuery();
    			ResultSetMetaData metaData = ps.getMetaData();
    			int columnCount = metaData.getColumnCount();
    
    			sqlFile = new File("C:\\"+tableName+"_CLOB.sql");
    			while (rs.next()) {
    				StringBuffer declare = new StringBuffer();
    				declare.append("declare\r\n");
    				StringBuffer sb = new StringBuffer();
    				sb.append("insert into " + tableName + "(");
    
    				StringBuffer k = new StringBuffer();
    				StringBuffer v = new StringBuffer();
    				for (int i = 0; i < columnCount; i++) {
    					k.append(metaData.getColumnName(i + 1)).append(",");
    					int type = metaData.getColumnType(i + 1);
    					Object value = rs.getObject(i + 1);
    					if (value != null) {
    						if (Arrays.asList(Types.VARCHAR, Types.CHAR, Types.LONGVARCHAR, Types.CLOB).contains(type)) {
    							if (Types.CLOB == type) {
    								CLOB clob = (CLOB) value;
    								String string = clob.stringValue();
    								declare.append("clob_" + i + " clob:=" + splitBigText(string) + ";\r\n");
    								v.append("clob_" + i);
    							} else {
    								v.append("'" + value + "'");
    							}
    						} else if (Arrays.asList(Types.DATE, Types.TIME, Types.TIMESTAMP).contains(type)) {
    							if (Types.DATE == type) {
    								v.append("to_date('" + DateUtil.formatDate((Date) value) + "','yyyy-mm-dd')");
    							} else if (Types.TIME == type) {
    								v.append("to_date('" + DateUtil.formatTime((Time) value) + "','hh24:mi:ss')");
    							} else if (Types.TIMESTAMP == type) {
    								v.append("to_date('" + DateUtil.formatDateTime((Timestamp) value)
    										+ "','yyyy-mm-dd hh24:mi:ss')");
    							}
    						} else {
    							v.append(value);
    						}
    					} else {
    						v.append(value);
    					}
    					v.append(",");
    				}
    				k.deleteCharAt(k.length() - 1);
    				v.deleteCharAt(v.length() - 1);
    				sb.append(k).append(") values(").append(v).append(");");
    
    				declare.append("begin\r\n").append(sb).append("\r\n").append("end;\r\n/");
    
    				System.out.println(declare.toString());
    				FileUtil.appendUtf8String(declare.toString() + "\r\n", sqlFile);
    			}
    		} catch (Exception e) {
    		} finally {
    			if (rs != null) {
    				try {
    					rs.close();
    				} catch (Exception e) {
    					e.printStackTrace();
    				}
    			}
    			if (ps != null) {
    				try {
    					ps.close();
    				} catch (Exception e) {
    					e.printStackTrace();
    				}
    			}
    			Db.use().closeConnection(connection);
    		}
    	}
    
    	public static String splitBigText(String text) {
    		text = text.replaceAll("\'", "\'\'");
    		String[] splitByLength = StrSpliter.splitByLength(text, 3000);
    		return Arrays.asList(splitByLength).stream().map(o -> "'" + o + "'").collect(Collectors.joining("||"));
    	}
    }
    
    

    4.原理

    declare 
    a_clob clob:='1111'||'22222';
    begin
    insert into aa(a1) values (a_clob);
    end;
    
    展开全文
  • 重点 先用empty_clob()代替要插入的clob数据 用真正的数据去替换原先插进去的空clob 创建表 create table test( id varchar2(200), ... 插入Clob字段长度超过4000测试 NO.1 先用empty_c...

    重点

    1. 先用empty_clob()代替要插入的clob数据
    2. 用真正的数据去替换原先插进去的空clob
    3. 一定要加上对clob字段的非空判断,不然当clob为空时会报错

    创建表

    create table test(
          id varchar2(200),
          clob_filed clob
    )

    mybatis对应sql

    <!-- 
        插入Clob字段长度超过4000测试
            NO.1 先用empty_clob()代替要插入的clob数据
            No.2 用真正的数据去替换原先插进去的空clob
            No.3 一定要加上对clob字段的非空判断,不然当clob为空时会报错
     -->
    <update id="insertClobTest">
        <foreach collection="items" item="item" index="index" open="begin" close="end;">
            merge into test a using (
                select #{item.id} as id,empty_clob() as clob_filed from dual
            ) temp on (temp.id = a.id)
            when matched THEN update set a.clob_filed = temp.clob_filed
            when not matched then insert(id,clob_filed) values(temp.id, temp.clob_filed);
    
          <if test="item.clob_filed != null">
                update test set clob_filed = #{item.clob_filed} where id = #{item.id};
          </if> 
        </foreach>
    </update>

    后台调用

    List<Map<String,Object>> newsList = new ArrayList<Map<String,Object>>();
    Map<String,Object> map = new HashMap<String, Object>();
    String str = "124aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa123";
    map.put("id","a");
    map.put("clob_filed",str);
    newsList.add(map);
    
    Map<String,Object> map1 = new HashMap<String, Object>();
    map1.put("id","b");
    map1.put("clob_filed",str);
    newsList.add(map1);
    
    HashMap<String, Object> param = new HashMap<String, Object>();
    param.put("items", newsList);
    
    //执行插入语句
    insertSql("insertClobTest", param);
    展开全文
  • oracle查询字段大于指定长度的数据

    千次阅读 2020-02-20 15:13:39
    举例说明: 比如查询字段长度大于18的数据,sql如下: select * from user t where length(t.sfhm)>18

    举例说明:

        比如查询字段长度大于18的数据,sql如下:

         select * from user t where length(t.sfhm)>18

    展开全文
  • Oracle插入长度超过4000的字符串

    千次阅读 2020-04-04 23:12:30
    最近开发中向oracle中存储长度超过4000的字符串时报错,本来字符设置的是varchar2(4000),后改为clob类型,发现还是报错,百度了下发现因为oracle在插入CLOB的数据默认会以varchar2的类型插入,所以此法行不通。...

    最近开发中向oracle中存储长度超过4000的字符串时报错,本来字符设置的是varchar2(4000),后改为clob类型,发现还是报错,百度了下发现因为oracle在插入CLOB的数据默认会以varchar2的类型插入,所以此法行不通。
    解决办法,通过存储过程实现。

    DECLARE  
      clobValue 表名.字段名%TYPE;  
    BEGIN  
      clobValue := '值'; --字段内容  
      INSERT INTO 表名(...,字段) VALUES(...,clobValue);
      COMMIT;  
    END; 
    

    修改同理

    DECLARE  
      clobValue 表名.字段名%TYPE;  
    BEGIN  
      clobValue := '值'; --字段内容  
      UPDATE 表名 T SET T.字段名 = clobValue WHERE 条件;  
      COMMIT;  
    END; 
    
    展开全文
  • 把表DT_CORP_ENTERPRISE中ASSetsinvestmen字段(varchar2,长度4000)改为clob类型 --增加大字段项 alter table DT_CORP_ENTERPRISE add hehe clob; --将需要改成大字段的项内容copy到大字段中 update DT_CORP_...
  • oracle clob 存储大于4000字符的字符串

    千次阅读 2015-11-17 15:10:00
    oracle会把插入的字符串作为 “字符串类型”处理,由于oracle有最大字符串限制(不超过4000个字符),所以会报错。 解决思路: 指定待插入字符串类型为clob ,将长字符串存到clob变量中,可以使用过程或存储过程 ...
  • 实际值的长度大于了数据库里某个字段允许的最大长度导致的插入失败问题。 常见的场景是oracle sequence自动增长,系统用到一定的时候,sequence的值不断的增大,系统开始报错。 解决方法:更改字段的长度解决 您...
  • Oracle12c之前数据库varchar2最大值位4000字节,12c提供了varchar2字段扩展,支持4000字节以上。
  • 解决方法: 1.优化sql,使用代码进行拼接 2.将需要拼接的字段类型改成clob 3.将查询的字段转成clob即可,需要全部转换 select to_clob(col1)||to_clob(col12) from TAB where ROWNUM=1;
  • 最近项目上有个问题是需要获取oracle数据库中表定义的字段长度,然后进行验证将要输入到该数据库表的数据是否超出字段定义的长度。所以对oracle存储字符串的类型进行了解。 1、首先了解varchar、varchar2、nvarchar...
  • mysql 查询字段长度

    万次阅读 2017-06-24 13:03:38
    查询字段长度,不同数据库,方法不同: MySQL: LENGTH( ) Oracle: LENGTH( ) SQL Server: LEN( )对于MySQL,使用length函数 例如: 查询messge长度大于56k的记录SELECT * FROM apple where length(message)>...
  • 0racle9i中 VARCHAR2(4000) 其实只能存在2000个字节,大于2000个字节报错 Oracle11g VARCHAR2(4000) 能存在4000个字节
  • oracle 实际值超过数据库某个字段指定长度报错解决ORA-01438: value larger than specified precision allowed for this column实际值的长度大于了数据库里某个字段允许的最大长度导致的插入失败问题。常见的场景是...
  • // UTF8字符集实际长度计算 function getStrLeng(str){ var realLength = 0; var len = str.length; var charCode = -1; for(var i = 0; i ; i++){ charCode = str.charCodeAt(i); if (charCode >= 0 && char...
  • oracle数据库当插入的字符串长度大于4000时,会报错长度过长。 百度的解决方法都是定义变量去解决,如: 这种方法确实能够解决一部分问题,为什么这么说呢? 因为当长度很长,达到3、4W的时候还是会报长度过长的...
  • 12.2支持创建的表名长度大于30,最大128个字符[oracle@rac1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 13 17:18:17 2018Copyright (c) 1982, 2013, Oracle. All rights reserved....
  • 在SQLServer数据库: 修改字段类型和长度: alter table student|表名 alter column stucard|字段名 varchar(18)|含...在Oracle数据库: 修改字段类型和长度: alter table student/表名 modify(stuid/字段名 ...
  • 题记:我们知道Oracle存储的字段长度是有限制,常见的varchar2的保存长度最大为4000,对一些业务字段值可能超过4000的情况,可以使用clob类型进行存储,但是如果直接将大字符串保存到clob的列种,会提示错误:ORA-...
  • 今天在java中调用oracle10g存储过程(存储过程返回游标)时提示:类型长度大于最大值 原来问题是oracle的驱动程序包所引起的   解决方法: 把以前的那个oracle12.class的jar文件移除,下载下来下面附近中的...
  • 如果scale大于零,表示数字精确到小数点右边的位数;scale默认设置为0;如果scale小于零,Oracle将把该数字取舍到小数点左边的指定位数。 Precision的取值范围为【1---38】;Scale的取值范围为【-84---127】。 ...
  • 工作中经常要遇到这种字段的处理情况,当然以前也见过大虾总结过。但是看了之后,又还给人家。是人家的终究还是人家的,需要自己亲自测试并总结才能变成自己的。 相关知识回顾: 有效数位:从左边第一个不为0...
  • 我们可以通过创建单独的OracleCommand来进行指定的插入,即可获得成功,这里仅介绍插入clob类型的数据,blob与此类似,这里就不介绍了,下面介绍两种办法
  • Oracle字段保留2两位小数点number类型

    万次阅读 2017-10-25 15:11:16
    由于用的oracle,我查了下字段是否能像mysql一样设置保留小数点呢。查了网上很多文章,发现实现得很麻烦,就是用number类型进行实现,用起来比较绕,下面对number类型做下简单的介绍 number ( prec

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 27,207
精华内容 10,882
关键字:

oracle字段长度大于4000