一个关于sql语法的问题

[zyylily]

String sql="Select SUBSTR(SUBSTR(TO_CHAR(BUSOBJ_XML),
INSTR(TO_CHAR(BUSOBJ_XML),'<name>dbLeaveType</name><value>') +
LENGTH('<name>dbLeaveType</name><value>')),1,(INSTR(SUBSTR(TO_CHAR(BUSOBJ_XML),
INSTR(TO_CHAR(BUSOBJ_XML),'<name>dbLeaveType</name><value>') +
LENGTH('<name>dbLeaveType</name><value>')), '</value>'))- 1) AS LEAVE_TYPE,
SUBSTR(SUBSTR(TO_CHAR(BUSOBJ_XML),
INSTR(TO_CHAR(BUSOBJ_XML),'<name>Start_Date</name><value>')+
LENGTH('<name>Start_Date</name><value>')),1,(INSTR(SUBSTR(TO_CHAR(BUSOBJ_XML),
INSTR(TO_CHAR(BUSOBJ_XML),'<name>Start_Date</name><value>') +
LENGTH('<name>Start_Date</name><value>')),'</value>'))-1) AS START_DATE
from business_object, business_object_status,business_object_type
where business_object.busobjstat_id = business_object_status.busobjstat_id
and busobjstat_name in ('ACCEPTED','PENDING','RESENT','RESEND REQUESTED')
and business_object.busobjtyp_id = business_object_type.busobjtyp_id
and busobjtyp_name='Vacation Request'"
以上sql语句在oracle下可以直接运行
但是作为字符串传入pstmt = conn.prepareStatement(sql);后
sql就会变成
Select
business_object.wbu_id_origin,business_object.WBU_NAME_ACTUAL,SUBSTR(SUBSTR(TO_CHAR(BUSOBJ_XML),
INSTR(TO_CHAR(BUSOBJ_XML),'dbLeaveType') + LENGTH('dbLeaveType')),1,(
INSTR(SUBSTR(TO_CHAR(BUSOBJ_XML),
INSTR(TO_CHAR(BUSOBJ_XML),'dbLeaveType') + LENGTH('dbLeaveType')), ''))- 1) AS
LEAVE_TYPE,
to_date(SUBSTR(SUBSTR(TO_CHAR(BUSOBJ_XML),
INSTR(TO_CHAR(BUSOBJ_XML),'Start_Date')+ LENGTH('Start_Date')),1,(
INSTR(SUBSTR(TO_CHAR(BUSOBJ_XML),
INSTR(TO_CHAR(BUSOBJ_XML),'Start_Date') +
LENGTH('Start_Date')),''))-1),'yyyymmdd hh24:mi:ss') AS START_DATE,
to_date(SUBSTR(SUBSTR(TO_CHAR(BUSOBJ_XML),
INSTR(TO_CHAR(BUSOBJ_XML),'End_Date')+ LENGTH('End_Date')),1,(
INSTR(SUBSTR(TO_CHAR(BUSOBJ_XML),
INSTR(TO_CHAR(BUSOBJ_XML),'End_Date') + LENGTH('End_Date')),''))-1),'yyyymmdd
hh24:mi:ss') AS END_DATE
from business_object, business_object_type
where business_object.CLIENT_ID=1 AND
business_object.busobjstat_id = 0 and
business_object.busobjtyp_id = business_object_type.busobjtyp_id and
business_object_type.busobjtyp_name='Vacation Request'
即所有xml的tag标记都会被过滤掉（如<name>，</name><value>），不知何故？请教大家!~
难道是字符转义？如果是，正确的语法应该怎么写？谢谢！~

[海阔天空1977]

这方面的没有去测试过，我会继续跟踪这个问题。

[小李木耳]

应该是楼主分析的那样，好像是.net的语法吧？！可以把字符串定义成二进制字符/转变成UI的格式使用试试。

[zxbyhcsdn]

眼睛看花了！！