JAVA读取文本文件,然后执行该SQL语句,出现java.sql.SQLException: ORA-00911: invalid character报错

该SQL在SQLPLUS\WORKSHEET里执行没问题 
文本文件里的SQL语句如下: 

truncate table TRS.tb_affair_appendix; 
insert into TRS.tb_affair_appendix(GscApproveDatumSetGuid,GscApproveDatumName,GscApproveDatumFormatCodeId,GscApproveDatumCopies,GscApproveProjectCodeId,flag,dealFlag,files) 
select datum.F2,datum.F5,datum.F6,datum.F7,datum.F4,0,0,to_lob(doc.F9) 
from GxGscManage."ApproveDatumSet" datum left join GxGscManage.APPDOCFILE_TEMP doc on datum.F8=doc.F3 and doc.F10=0 where doc.AREACODE='1' 
order by datum.F1; 


truncate table TRS.tb_affair_evidence; 
insert into TRS.tb_affair_evidence(GscApproveWarrantySetGuid,GscApproveWarrantyGovDocTitle,GscApproveWarrantyGovDocItem,GscApproveWarrantyGovDocTime, 
GscApproveWarrantyGovDocCodeId,GscApproveProjectCodeId,flag,dealFlag) 
select F2,F5,F6,F11, 
F8,F4,0,0 from GxGscManage."ApproveWarrantySet" where F9='GscObjectStatus_OK' and AREACODE='1' 
order by F1; 


truncate table TRS.tb_affair_condition; 
insert into TRS.tb_affair_condition(GscApproveConditionSetGuid,GscApproveCondition,GscApproveProjectCodeId,flag,dealFlag) 
select F2,F5,F4,0,0 from GxGscManage.ApproveConditionSet where 
F6='GscObjectStatus_OK' and AREACODE='1' 
order by F1; 

7 个解决方案

#1

sql中存在双引号，java读取的时候再拼字符串自然会出现问题！

引用楼主 tyfun 的帖子:

该SQL在SQLPLUS\WORKSHEET里执行没问题 
文本文件里的SQL语句如下: 

truncate table TRS.tb_affair_appendix; 
insert into TRS.tb_affair_appendix(GscApproveDatumSetGuid,GscApproveDatumName,GscApproveDatumFormatCodeId,GscApproveDatumCopies,GscApproveProjectCodeId,flag,dealFlag,files) 
select datum.F2,datum.F5,datum.F6,datum.F7,datum.F4,0,0,to_lob(doc.F9) 
from GxGscManage."ApproveDatumSet" datum …

#2

truncate table TRS.tb_dept_affair;
insert into TRS.tb_dept_affair(approveAffairId,affairDepartment,affairName,approveObj,isCharge,acceptMethod,approveType,
approveMethod,legalTime,promiseTime,prejudicationTime,approvePlace,memoInfo,belongDepartment,status,
flag,dealFlag) 
select app.F2,app.F25,app.F3,app.F11,
app.F15,app.F19,app.F9,app.F10,app.F21,app.F20,app.F22,
app.F27,app.F29,org.F3,app.F28,0,0 from (GxGscManage.\"ApproveProject\" app left join GxGscManage.APPDOCFILE_TEMP doc on app.F18=doc.F3 and doc.F10=0) left join GxGscManage.\"GscOrgan\" org on app.F8=org.F2 where app.AREACODE='1' 
order by app.F1;
update TRS.tb_dept_affair set approveDocFile = (select to_lob(doc.F9) from GxGscManage.\"ApproveProject\" app,GxGscManage.APPDOCFILE_TEMP doc where app.F18=doc.F3 and doc.F10=0);



truncate table TRS.tb_affair_appendix;
insert into TRS.tb_affair_appendix(GscApproveDatumSetGuid,GscApproveDatumName,GscApproveDatumFormatCodeId,GscApproveDatumCopies,GscApproveProjectCodeId,flag,dealFlag,files) 
select datum.F2,datum.F5,datum.F6,datum.F7,datum.F4,0,0,to_lob(doc.F9) 
from GxGscManage.\"ApproveDatumSet\" datum left join GxGscManage.APPDOCFILE_TEMP doc on datum.F8=doc.F3 and doc.F10=0 where doc.AREACODE='1' 
order by datum.F1;



truncate table TRS.tb_affair_evidence;
insert into TRS.tb_affair_evidence(GscApproveWarrantySetGuid,GscApproveWarrantyGovDocTitle,GscApproveWarrantyGovDocItem,GscApproveWarrantyGovDocTime,
GscApproveWarrantyGovDocCodeId,GscApproveProjectCodeId,flag,dealFlag) 
select F2,F5,F6,F11,
F8,F4,0,0 from GxGscManage.\"ApproveWarrantySet\" where F9='GscObjectStatus_OK' and AREACODE='1' 
order by F1;



truncate table TRS.tb_affair_condition;
insert into TRS.tb_affair_condition(GscApproveConditionSetGuid,GscApproveCondition,GscApproveProjectCodeId,flag,dealFlag)
select F2,F5,F4,0,0 from GxGscManage.ApproveConditionSet where
F6='GscObjectStatus_OK' and AREACODE='1' 
order by F1;


我这样些了
还是要报错呢????

#3

自己顶           

#4

自己顶           

#5

你在文本文件里写成"\""这样不太合适，你的文本文件内容保持不变，只需要在你的java程序里头，遇到"，就变成\"即可。

引用 2 楼 tyfun 的回复:

truncate table TRS.tb_dept_affair; 
insert into TRS.tb_dept_affair(approveAffairId,affairDepartment,affairName,approveObj,isCharge,acceptMethod,approveType, 
approveMethod,legalTime,promiseTime,prejudicationTime,approvePlace,memoInfo,belongDepartment,status, 
flag,dealFlag) 
select app.F2,app.F25,app.F3,app.F11, 
app.F15,app.F19,app.F9,app.F10,app.F21,app.F20,app.F22, 
app.F27,app.F29,org.…

#6

用JAVA程序去调用这个语句的时候后面不要加 “;”！

#7

引用 6 楼 cuilike 的回复:

用JAVA程序去调用这个语句的时候后面不要加 “;”！

如果一个文本里有多个语句,怎么样做?JAVA程序会自动识别是多个语句吗?