Error--ORA-01704: string literal too long

遇到一个 Servlet + Velocity 模板引擎的项目。

需要本地测试，向Oracle执行导入SQL ：

　　[ERR] INSERT INTO "CDGZC"."SYS_RESOURCE" VALUES ('0001', '系统管理', NULL, '236', '45353535544545', '0', '0', NULL, NULL, NULL, NULL, 'hitspro', HEXTORAW('89504E470D0A1A0A0000000D4948...')[ERR] ORA-01704: string literal too long

 

原因分析及解决：

　一：插入或更新的值大于给定字段的值，致使错误。对于 Oracle 数据库，默认情况下，字符串文字值的最大长度是 4000 字节（对于 VARCHAR2 类型的列）。此种情况更改字段数据类型即可解决。

 

　二：插入或更新的值、字符串长度超过了SQL解析器能够支持的最大长度。也就是说，数据库字段长度可以容纳，但SQL解析器无法解析这么长的值。

　　如我发生的错误：字段类型为BLOB，BLOB类型可以存储二进制数据，但无法直接将过大的字符串文字插入到改列内。

　　针对这种错误，可以使用预编译语句解决：（使用三个字段演示...）

public class JdbcImport {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.3.2:1521:orcl", "userlll", "pwdlll");
        String sql = "INSERT INTO SYS_RESOURCE (id, name, UP_ICON) VALUES (?, ?, ?)";
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, "0001");
        stmt.setString(2, "系统管理");
        stmt.setBytes(3, hexStringToByteArray("89504E470D0A1A0A0000000D4948445200000018000000180806000000E0773DF8000000097048597300000B1300000B1301009A9C1800000A4D6943435050686F746F73686F70204943432070726F66696C65000078DA9D53775893F7163EDFF7650F5642D8F0B1976C81002223AC08C81059A21092006184101240C585880A561415119C4855C482D50A489D88E2A028B867418A885A8B555C38EE1FDCA7B57D7AEFEDEDFBD7FBBCE79CE7FCCE79CF0F8011122691E6A26A003952853C3AD81F8F4F48C4C9BD80021548E0042010E6CBC26705C50000F00379787E74B03FFC01AF6F00020070D52E2412C7E1FF83BA50265700209100E02212E70B01905200C82E54C81400C81800B053B3640A009400006C797C422200AA0D00ECF4493E0500D8A993DC1700D8A21CA908008D0100992847240240BB00605581522C02C0C200A0AC40222E04C0AE018059B632470280BD0500768E58900F4060008099422CCC0020380200431E13CD03204C03A030D2BFE0A95F7085B8480100C0CB95CD974BD23314B895D01A77F2F0E0E221E2C26CB142611729106609E4229C979B231348E7034CCE0C00001AF9D1C1FE383F90E7E6E4E1E666E76CEFF4C5A2FE6BF06F223E21F1DFFEBC8C020400104ECFEFDA5FE5E5D60370C701B075BF6BA95B00DA560068DFF95D33DB09A05A0AD07AF98B7938FC401E9EA150C83C1D1C0A0B0BED2562A1BD30E38B3EFF33E16FE08B7EF6FC401EFEDB7AF000719A4099ADC0A383FD71616E76AE528EE7CB0442316EF7E723FEC7857FFD8E29D1E234B15C2C158AF15889B850224DC779B952914421C995E212E97F32F11F96FD0993770D00AC864FC04EB607B5CB6CC07EEE01028B0E58D27600407EF32D8C1A0B91001067343279F7000093BFF98F402B0100CD97A4E30000BCE8185CA894174CC608000044A0812AB041070CC114ACC00E9CC11DBCC01702610644400C24C03C104206E4801C0AA11896411954C03AD804B5B0031AA0119AE110B4C131380DE7E0125C81EB70170660189EC218BC86090441C8081361213A8811628ED822CE0817998E04226148349280A420E988145122C5C872A402A9426A915D4823F22D7214398D5C40FA90DBC820328AFC8ABC47319481B25103D4027540B9A81F1A8AC6A073D174340F5D8096A26BD11AB41E3D80B6A2A7D14BE87574007D8A8E6380D1310E668CD9615C8C87456089581A26C71663E55835568F35631D583776151BC09E61EF0824028B8013EC085E8410C26C82909047584C5843A825EC23B412BA085709838431C2272293A84FB4257A12F9C478623AB1905846AC26EE211E219E255E270E135F9348240EC992E44E0A21259032490B496B48DB482DA453A43ED210699C4C26EB906DC9DEE408B280AC209791B7900F904F92FBC9C3E4B7143AC588E24C09A22452A494124A35653FE504A59F324299A0AA51CDA99ED408AA883A9F5A496DA076502F5387A91334759A25CD9B1643CBA42DA3D5D09A696769F7682FE974BA09DD831E4597D097D26BE807E9E7E983F4770C0D860D83C7486228196B197B19A718B7192F994CA605D39799C85430D7321B9967980F986F55582AF62A7C1591CA12953A9556957E95E7AA545573553FD579AA0B54AB550FAB5E567DA64655B350E3A909D416ABD5A91D55BBA936AECE5277528F50CF515FA3BE5FFD82FA630DB2868546A08648A35463B7C6198D2116C63265F15842D6725603EB2C6B984D625BB2F9EC4C7605FB1B762F7B4C534373AA66AC6691669DE671CD010EC6B1E0F039D99C4ACE21CE0DCE7B2D032D3F2DB1D66AAD66AD7EAD37DA7ADABEDA62ED72ED16EDEBDAEF75709D409D2C9DF53A6D3AF77509BA36BA51BA85BADB75CFEA3ED363EB79E909F5CAF50EE9DDD147F56DF4A3F517EAEFD6EFD11F373034083690196C313863F0CC9063E86B9869B8D1F084E1A811CB68BA91C468A3D149A327B826EE8767E33578173E66AC6F1C62AC34DE65DC6B3C61626932DBA4C4A4C5E4BE29CD946B9A66BAD1B4D374CCCCC82CDCACD8ACC9EC8E39D59C6B9E61BED9BCDBFC8D85A5459CC54A8B368BC796DA967CCB05964D96F7AC98563E567956F556D7AC49D65CEB2CEB6DD6576C501B579B0C9B3A9BCBB6A8AD9BADC4769B6DDF14E2148F29D229F5536EDA31ECFCEC0AEC9AEC06ED39F661F625F66DF6CF1DCC1C121DD63B743B7C727475CC766C70BCEBA4E134C3A9C4A9C3E957671B67A1739DF33517A64B90CB1297769717536DA78AA76E9F7ACB95E51AEEBAD2B5D3F5A39BBB9BDCADD96DD4DDCC3DC57DABFB4D2E9B1BC95DC33DEF41F4F0F758E271CCE39DA79BA7C2F390E72F5E765E595EFBBD1E4FB39C269ED6306DC8DBC45BE0BDCB7B603A3E3D65FACEE9033EC63E029F7A9F87BEA6BE22DF3DBE237ED67E997E07FC9EFB3BFACBFD8FF8BFE179F216F14E056001C101E501BD811A81B3036B031F049904A50735058D05BB062F0C3E15420C090D591F72936FC017F21BF96333DC672C9AD115CA089D155A1BFA30CC264C1ED6118E86CF08DF107E6FA6F94CE9CCB60888E0476C88B81F69199917F97D14292A32AA2EEA51B453747174F72CD6ACE459FB67BD8EF18FA98CB93BDB6AB6727667AC6A6C526C63EC9BB880B8AAB8817887F845F1971274132409ED89E4C4D8C43D89E37302E76C9A339CE49A54967463AEE5DCA2B917E6E9CECB9E773C593559907C3885981297B23FE5832042502F184FE5A76E4D1D13F2849B854F45BEA28DA251B1B7B84A3C92E69D5695F638DD3B7D43FA68864F4675C633094F522B79911992B923F34D5644D6DEACCFD971D92D39949C949CA3520D6996B42BD730B728B74F662B2B930DE479E66DCA1B9387CAF7E423F973F3DB156C854CD1A3B452AE500E164C2FA82B785B185B78B848BD485AD433DF66FEEAF9230B82167CBD90B050B8B0B3D8B87859F1E022BF45BB16238B5317772E315D52BA647869F0D27DCB68CBB296FD50E2585255F26A79DCF28E5283D2A5A5432B82573495A994C9CB6EAEF45AB9631561956455EF6A97D55B567F2A17955FAC70ACA8AEF8B046B8E6E2574E5FD57CF5796DDADADE4AB7CAEDEB48EBA4EB6EACF759BFAF4ABD6A41D5D086F00DAD1BF18DE51B5F6D4ADE74A17A6AF58ECDB4CDCACD03356135ED5BCCB6ACDBF2A136A3F67A9D7F5DCB56FDADABB7BED926DAD6BFDD777BF30E831D153BDEEF94ECBCB52B78576BBD457DF56ED2EE82DD8F1A621BBABFE67EDDB847774FC59E8F7BA57B07F645EFEB6A746F6CDCAFBFBFB2096D52368D1E483A70E59B806FDA9BED9A77B5705A2A0EC241E5C127DFA67C7BE350E8A1CEC3DCC3CDDF997FB7F508EB48792BD23ABF75AC2DA36DA03DA1BDEFE88CA39D1D5E1D47BEB7FF7EEF31E36375C7358F579EA09D283DF1F9E48293E3A764A79E9D4E3F3DD499DC79F74CFC996B5D515DBD6743CF9E3F1774EE4CB75FF7C9F3DEE78F5DF0BC70F422F762DB25B74BAD3DAE3D477E70FDE148AF5B6FEB65F7CBED573CAE74F44DEB3BD1EFD37FFA6AC0D573D7F8D72E5D9F79BDEFC6EC1BB76E26DD1CB825BAF5F876F6ED17770AEE4CDC5D7A8F78AFFCBEDAFDEA07FA0FEA7FB4FEB165C06DE0F860C060CFC3590FEF0E09879EFE94FFD387E1D247CC47D52346238D8F9D1F1F1B0D1ABDF264CE93E1A7B2A713CFCA7E56FF79EB73ABE7DFFDE2FB4BCF58FCD8F00BF98BCFBFAE79A9F372EFABA9AF3AC723C71FBCCE793DF1A6FCADCEDB7DEFB8EFBADFC7BD1F9928FC40FE50F3D1FA63C7A7D04FF73EE77CFEFC2FF784F3FB25D29F330000000467414D410000B18E7CFB5193000000206348524D00007A25000080830000F9FF000080E9000075300000EA6000003A980000176F925FC546000006864944415478DA84955B6C5C571985BFBDCF993973F78C3D8EEDB15DC78E4BDA402EC425E4D20445422548698210291455E5221008F250D49622CA0B1701454508059050A854780154521E12D1A012946BD304A58D12C7711263671C8F1BDBE3197BC6733933FBC283132904A9ACC7FDF0AF7FADA57F6D317E6314255CFC483B8972964A662DB14621669AB57E5BCE6F8DCFBCB9CD2B5EDC5DF9E0FE036593FA5534D91B8B7B4CD46B8B84C2314E9F3A4DD0F3001042702F76EEDC898BB55837800A2502B619DD18CFFDEB91C8F4DBBBC2F9B31B5C51EF318914AA6308A79CDDDD7AFBAF9F083EBCF7644E6EFD76A459599D6809DC02AABC0F5C1D4E3C1A9A1BDBD2FAEE9F3E1699BEF891A028ADD0994174660B753784284F22A7AF1297E7369AD63554FC8A971C7FE9CF8964B47BBCFCF1C741568134B060AD55F7AB104B079FC97B23E7DBCCEA0FA13EB019E33AC84216777E041D08A33B7A316DFD884699606118B77C0D196EA13CF8D999F3F9FE176AA5B9ED917068A7170A1F94D2FD99C59ABBC3B76DDD8A685EF8BBADC6DAB1574E10193E05318BEA59876ACB80D578339709562651510F3FB38D7CDB8799937DD4974A3622CAC4A261D1D00194E1B0903C01D4EF12AC5FB71E576F7C8C66B1405429EA3B9E44D5EA846F9C25FEEF772091A43AB096FCBAA799F3BA682841C8564807AAD8564F4C953D0E5FA83332B1C073BBE395DEA46B9ACAFE7706C2F71116D8F71C956A9DE46B3F27905E45E1B12F53883FC052C312B475329E21E0406E21C4B11B75CE4FD4995AD01C7B77913EAF81E72644382090FF13B231186DB0F53AA2B488FBE4B3CC372C7F3BFA261F5D9BE0C1EE34B5BAE4DCD81227474A5CCA5529D721120A909DADE117ABC457051180D60263EE27D01A6534D61A0C06DC20A63847697A82F4F6218E5FF5F9F5D1498A5583E30852B1003D7197B1D91AB9B242B445119E000BD65AACBDCF22A335D618B4BEA3446B841044C2518C8177C64BE41615031D61A26187AA128C2D6816AC477F6F8862CD1289D4B0D662AC411B17632360F53D161983D11A6DCCF216C620040809A1A024EE49A22197D98AE5BDAA259A4831D81DC2938AF925452B126D9A34551029B224E347C16B075E44EA3B83B5D11863507A99D9715D402285251474B9B5A8982C5B3A7BBA09CE9C62FEED5F125ED146476F07AD2D1EDA04B0CD8A59617E4F2C729C58CB5900A4D116630CD65830CBDB5B6B918E440A90523296AF33B968C874A508863D92538739B0F927EC59F90A2D0392663483B5A29659F89D75DDAA840498F03281360AA3144A29945668AD315AE34A07D795E40B0DB2730D52498F54324C226E29D734DE187CFEDA7E9ED15F64E3E004213B39206E5FFA1193D9DFD0309FC471EE846C0CC62EAB30C670371329250268688DEB4A42411789A1B333C2EDC1AD1CF01EE09BAB06183AF12DD6749DA1D98C3CCACDD11D5480D9D43EF6A69EC5E355A9B506AC35465B630C46298C3548570272B9828500044669303030B41967D3E738BE7E3FBF7DE835A646C3842F8DCAAC5A75C46F5FFB03CE155B79EBCC2F00A485D7CBE5A583CDA632DA2C076EB4C191EEF25C6B304D8D520AAD0DA5852AB6759040B497EA5B39025DBBF0B77DCFAF2F04CDE542C7E946FBEA9F121C3CC599621240369B8DCF5CB93272524AA728A5734969A5B4B5CB164910064CB34961C967ECBD2AE3E305D2B53ABB630D9E5E519D7DBCA7F2C7F483EBBFEAF8ADB978530FCD2F7548F2E53AB93B8736323C62C3E1D0954824F2E972B9BC522BFD0761AD715DD70A291DA12D4C57988979ECDAE0F0D43A87CDFD2E0BF919AE8DE70FE9D9B66FF4D52669EBE8DBB461F8F257ECD2CC2172333B7824390EE0DEBA758BBE957D17B3D99BD46AB5AEB6B6D643BE5FFF87B0E2796DC480559A2D0FC7F8EED706D9BE264AB354E4F67C81E16B138C0C5F79229AEE584CA4FC1FDA3543DF6F292C6598BCF929FA3A8F33B4E13B006E2010C05A8B10108FC7DE701CE72FD9C9C93EBFD9785E69F8FABE55BCF08587A81573BC71E4041D5D19EA359F66A341BA3D1D75024EE69FF341AF3BECE557AEDFF414ADED6B70E7C709784500F76E294929715DB75C2C2E505A2CE9783CDE904210139552F6FA5462F8EA0D66676648A65A29CCCF53ABD5CEA4D36D2F219C2333D373D61A0BD81A425CC05AEED6AA7B6FF319638844C2F4F7F74FF9BEFF622814EA3E72ECD8C899D3A77FBC67CFDE4DBD3D1972D3D359371838900AA65E6D34FC792B0DE2FD3EFDFB1FACB528A58C10E275D775C15AAE5FBFF1A5D1D191973B3B3BF375BFF6723A96BE64B5C5F7EBFC3FFC6700D4026D488A290F740000000049454E44AE426082")); // 这里填入你的 HEX 数据

        stmt.executeUpdate();
        stmt.close();
        conn.close();
        System.out.println("BLOB 插入成功！");
    }

    // 转换为字节数组 如：(BLOB) 732 bytes
    public static byte[] hexStringToByteArray(String s) {
        int len = s.length();
        byte[] data = new byte[len / 2];
        for (int i = 0; i < len; i += 2) {
            data[i / 2] = (byte) ((Character.digit(s.charAt(i), 16) << 4)
                    + Character.digit(s.charAt(i+1), 16));
        }
        return data;
    }
}

　可以不转换为字节数组，也可以转换为json等。

 

 

---