ORA01704: string literal too long错误的解决方法

一、Java解决方案

采用预编译Preparestatmen的方式可以解决

http://blog.sina.com.cn/s/blog_9098e0010102vyki.html

 二、C#解决方案

　　其中parData的长度超过4000，博主同时也测试了一个3万多字符的xml文件，同样是可以成功插入的CLOB字段中。具体的原理应该是plsql或者jdbc只能执行长度为32K左右的SQL字符串，超过之后Oracle语法分析报错，但是预编译SQL之后，就跳过了语法分析这一步，再向数据库传递的长字符串就已经明确是参数了，而不再被认为是整条SQL的一部分了参与SQL的语法分析了。

DatabaseServer server = config.DatabaseServerList["OldDb"];
            string connectString = new DatabaseConnection(DatabaseType.OracleDB, server.Sid, "username", "password", server.Address, 1521).ConnectionString;
            string cmdString = "INSERT INTO SUPERMARKET_DATA(SMARKET_ID,IDENTIFIER,IDENTIFIER_TYPE,PART_DATA,PARENT_IDENTIFIER,PARENT_TYPE,DATE_CHANGED,TIME_STAMP) VALUES(HEXTORAW(:supermarketId)," +
                       ":identifier,:identifierType,:partData,:parentIdentifier,:parentIdentifierType,SYSTIMESTAMP,SYSTIMESTAMP)";
            string partData = "<?xml version='1.0' encoding='utf-16'?><PartInformation xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' version='1.0'><Part identifier='5666032021120309221951' identifierType='0' typeNo='F005V20248' typeVar='0001' resFileName='empty' batchID='' lastProcessingStep='61127786-b5b9-4301-ac04-5a94aba077a8' lastProcessNo='292080' lastProcessName='Unloader_Side2' nextProcessingStep='fbf4624b-3038-4eb6-bd7f-2a4b4ddd475d' nextProcessNo='291110' nextProcessName='ICT' routeListID='8375181c-3d08-4b74-92b1-3718c188e598' routeListName='SMT9_Routelist' lotID='' created='2021-12-07 10:11:31.7176215 +08:00' modified='2021-12-07 10:11:31.7196258 +08:00'><Pos>0</Pos><WorkingCode>0</WorkingCode><PartState>4</PartState><Result>1</Result><NioBits>0</NioBits><TargetIdx>2</TargetIdx><ParentIdentifierType>0</ParentIdentifierType></Part><AddData><AddDataItem addDataID='7fd91592-3fde-4f49-87e4-f61db431ae53' entryName='ccsDutDataPara2' partIdentifier='5666032021120309221951' identifierType='1' created='2021-12-07 10:11:31.7326290 +08:00'><Value xsi:type='xsd:string'></Value></AddDataItem><AddDataItem addDataID='0e6d069c-73b3-4ca6-bcc8-345def8c85a8' entryName='ccsDutDataPara3' partIdentifier='5666032021120309221951' identifierType='1' created='2021-12-07 10:11:31.7336289 +08:00'><Value xsi:type='xsd:string'></Value></AddDataItem><AddDataItem addDataID='a404c6a1-d405-4484-a2f7-9855e7fd26ea' entryName='ccsDutDataPara4' partIdentifier='5666032021120309221951' identifierType='1' created='2021-12-07 10:11:31.7336289 +08:00'><Value xsi:type='xsd:string'></Value></AddDataItem><AddDataItem addDataID='80298554-0a71-490d-83fc-730f93d393c5' entryName='EcuSerialNumber' partIdentifier='5666032021120309221951' identifierType='1' created='2021-12-07 10:11:31.7336289 +08:00'><Value xsi:type='xsd:string'>9920700468669001211206F005V03435</Value></AddDataItem><AddDataItem addDataID='b106f3c6-11f5-4596-8794-820afe37db2c' entryName='ICTFixtureID' partIdentifier='5666032021120309221951' identifierType='1' created='2021-12-07 10:11:31.7336289 +08:00'><Value xsi:type='xsd:string'>F501004105001</Value></AddDataItem><AddDataItem addDataID='712e9bfd-5065-4c10-99f6-ac5a421dfa51' entryName='ICTTime' partIdentifier='5666032021120309221951' identifierType='1' created='2021-12-07 10:11:31.7346289 +08:00'><Value xsi:type='xsd:string'>2021-12-04T13:56:19.0000000+08:00</Value></AddDataItem><AddDataItem addDataID='9619117e-bcaa-4233-980e-74195d0510e8' entryName='PCBType' partIdentifier='5666032021120309221951' identifierType='1' created='2021-12-07 10:11:31.7346289 +08:00'><Value xsi:type='xsd:string'>F005V20459</Value></AddDataItem><AddDataItem addDataID='35e10c2f-64f8-4e55-ae0b-a8f300142830' entryName='PCBUnpackageTime' partIdentifier='5666032021120309221951' identifierType='1' created='2021-12-07 10:11:31.7346289 +08:00'><Value xsi:type='xsd:string'>2021-12-03 16:12:18</Value></AddDataItem><AddDataItem addDataID='08888b57-ca2d-435a-aade-b5755792b3ef' entryName='Pressure' partIdentifier='5666032021120309221951' identifierType='1' created='2021-12-07 10:11:31.7346289 +08:00'><Value xsi:type='xsd:string'>9.9930924162001</Value></AddDataItem><AddDataItem addDataID='7580d66f-b1a9-44cc-96b9-38fb25e74cd0' entryName='printtime1' partIdentifier='5666032021120309221951' identifierType='1' created='2021-12-07 10:11:31.7346289 +08:00'><Value xsi:type='xsd:string'>2021-12-04T06:57:00.141+08:00</Value></AddDataItem><AddDataItem addDataID='77906b02-2065-44f0-825d-2d85459fb01e' entryName='printtime2' partIdentifier='5666032021120309221951' identifierType='1' created='2021-12-07 10:11:31.7346289 +08:00'><Value xsi:type='xsd:string'>2021-12-04T08:30:27.144+08:00</Value></AddDataItem><AddDataItem addDataID='af8b1b59-d1df-4666-a4f9-aaaadba8c8d1' entryName='UnloadTime01' partIdentifier='5666032021120309221951' identifierType='1' created='2021-12-07 10:11:31.7346289 +08:00'><Value xsi:type='xsd:string'>2021-12-04T07:38:57.5527344+08:00</Value></AddDataItem><AddDataItem addDataID='07cac832-250b-487b-9048-fbe90f3f7597' entryName='UnloadTime02' partIdentifier='5666032021120309221951' identifierType='1' created='2021-12-07 10:11:31.7356298 +08:00'><Value xsi:type='xsd:string'>2021-12-04T09:06:52.7500000+08:00</Value></AddDataItem></AddData><Blocks /><Counters /><Timers /></PartInformation>";
            //string partData = "test";
            OracleConnection conn = new OracleConnection(connectString);
            OracleCommand comm = new OracleCommand(cmdString, conn);
            comm.Parameters.Add("supermarketId", "D1E272094037864DA4B7061B4C3C3618");
            comm.Parameters.Add("identifier", "test20211207");
            comm.Parameters.Add("identifierType", "0");
            comm.Parameters.Add("partData", partData);
            comm.Parameters.Add("parentIdentifier", "");
            comm.Parameters.Add("parentIdentifierType", "0");
            try { 
                    conn.Open();
                    OracleTransaction transaction = conn.BeginTransaction();
                    comm.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch (Exception error)
                {
                    //StoreLogData("CreatPartVmdt", info.partList[0].identifier, "", error.Message);
                    return error.Message;
                }
                finally
                {
                    conn.Close();
                }
            return "success";