oracle中long类型数据写入大数据

在往oracle数据库的一个long字段插入字符的时候，用insert,结果由于插入的字符串长度操作了4000，出现标题所列的错误：

ORA-01704: string literal too long tips

$ oerr ora 1704
01704, 00000, "string literal too long"
// *Cause:  The string literal is longer than 4000 characters.
// *Action:  Use a string literal of at most 4000 characters.
//          Longer values may only be entered using bind variables.

 

oracle规定文本串的长度是4000，所以用普通的insert ...values...插入语句会报错，必须用绑定变量的方式插入，将String转换成字符流，Java可以用PrepareStatement.

 

Java 代码：

 

package com.jy.dao;

import java.io.ByteArrayInputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.jy.util.OracleConnect;

public class RWLDao {
    public final static Log LOG = LogFactory.getLog(TExtFormDao.class);
    private OracleConnect oracleConnect;
    
    public RWLDao() {
        oracleConnect = new OracleConnect();
    }
    
    public static void main(String[] args) {
        RWLDao rwlDao = new RWLDao();
        String fsHtml =
            "4fsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtml" +
            "fsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtmlfsHtml";
        fsHtml = fsHtml + fsHtml;
        fsHtml = fsHtml + fsHtml;
        fsHtml = fsHtml + fsHtml;
        fsHtml = fsHtml + fsHtml;
        fsHtml = fsHtml + fsHtml;
        fsHtml = fsHtml + fsHtml;
        fsHtml = fsHtml + fsHtml;
        fsHtml = fsHtml + fsHtml;
        rwlDao.modHtmlByFSOuterCaseId("CE5A0271002C7906CD8E03414DD30942", fsHtml);
    }
    
    public void modHtmlByFSOuterCaseId(String fsOuterCaseId, String fsHtml) {
        String sql;
        Connection conn = null;
        PreparedStatement ps = null;

        byte[] htmlByte = fsHtml.getBytes();
        ByteArrayInputStream htmlBAI = new ByteArrayInputStream(htmlByte);
        InputStreamReader htmlISR = null;
        System.out.println(fsHtml.length());
        try {
            htmlISR = new InputStreamReader(htmlBAI, "GBK");
        } catch (UnsupportedEncodingException e) {
            LOG.error("unsupported encoding exception...");
            LOG.error(e.getMessage());
            LOG.error(e.getStackTrace());
            e.printStackTrace();
        }
        try{
            conn = OracleConnect.getConnect();
            sql = "UPDATE HZSW_RWL SET FSHTML = ? WHERE fsOuterCaseId = ?";
            ps = conn.prepareStatement(sql);
            ps.setCharacterStream(1, htmlISR, htmlByte.length);
            ps.setString(2, fsOuterCaseId);
            if(ps.executeUpdate() > 0)
                LOG.info("update hzsw_rwl table fsOuterCaseId by fsHtml ok...");
        }catch(Exception e){
            LOG.error("update RWL fail...");
            LOG.error(e.getMessage());
            LOG.error(e.getStackTrace());
            e.printStackTrace();
        } finally {
            oracleConnect.close(null, null, ps);
        }
    }

}

PL/SQL:

declare
longliteral clob:=rpad('啊',30000,'啊');
begin
execute immediate 'insert into co_web_edit(rggt_id,content) values (''123123123123'',:1)' using longliteral;
end;

/

 

SQL> select dbms_lob.getlength(content)  from co_web_edit;

DBMS_LOB.GETLENGTH(CONTENT)
---------------------------
                      30000

 

官方解析：

ORA-01704: string literal too long tips

Oracle Error Tips by Burleson Consulting (S. Karam)

 

The Oracle docs note this on the ora-01704 error:

 

ORA-01704: string literal too long

Cause: The string literal is longer than 4000 characters.

 

Action: Use a string literal of at most 4000 characters. Longer values may only be entered using bind variables.

On , many questions have been raised concerning ORA-01704. In this case, 'isaac' askes why he is still receiving ORA001704 "when doing an SQL update statement in SQL*Plus and also executing an SQL query in ColdFusion." using this SQL statement:

Here is the sql statement being executed:
 

update mkt_page_links
set longdescription = ' {some html text > 4000 char} '
where menuidno = 310;

The replier informed 'isaac' that ORA-01704 was occurring as he executed the SQL statement:

In SQL the max of varchar2 is 4000 bytes and hence the error. Modify your code snippet to something like the following and it should work. (it becomes a pl/sql program).

UPDATE MKT_Page_Links
SET LongDescription =
WHERE MenuIDNO = #form.ID#

Furthermore, when running ColdFusion, a query should be run the following to avoid ORA-01704:

UPDATE MKT_Page_Links
SET LongDescription =
WHERE MenuIDNO = #form.ID#

In this example, 'isaac' resolution lay in the parameter that ColdFusion has which can convert variables to CLOB datatype.

相关资料：