今天用PL/SQL Developer往一个字段(VARCHAR(3000))里更新值时出错,报“ORA-01480: STR 赋值变量缺少空后缀”,查了一下英文定义为:
在网上逛了半天儿也没找什么有价值的资料,最后写了一个SQL语句,直接把这个字段的值更新了(由于“'”是Oracle的关键字,结果手工加了半天的单引号)
数据就这么添进去了,但为什么用PL/SQL Developer直接操作时会报错呢,在此大胆猜测一下,估计是该软件在进行OCI编程时的一个BUG,SQL语句过长就会出现这样的问题。
ORA-01480: trailing null missing from STR bind value
A bind variable of type 5 (null-terminated string) does not contain the terminating null in its buffer.
Terminate the string with a null character
SELECT C.mc AS 管线名称,
A.ZYQDMC AS 区队,
A.ZDZMC AS 井号,
A.QDZMC AS 站号,
'φ' || A.GXWJ || ' X ' || A.GXBH AS 管线尺寸,
ROUND(SDO_GEOM.SDO_LENGTH(C.GEOMETRY1, M.diminfo), 2) AS "管线长度(米)",
ROUND(MONTHS_BETWEEN(ROUND(SYSDATE, 'MONTH'), ROUND(A.TYRQ, 'MONTH')) / 12,
0) AS 使用年限,
ROUND(power(B.num, 0.5)) as 维修次数,
B.gxlx as 管线类型,
to_char(sysdate,'yyyy-mm') as 年度,
B.yxjs as 影响井数,
B.yxyl as 影响液量
FROM GISLRUSER.DHA019 A,
(select v.gxlx,v.szgxmc,v.yxjs,v.yxyl,count(v.szgxmc) as num
from CY2GISUSER.管线维修地点 v, CY2GISUSER.管线维修地点 x
where v.szgxmc = x.szgxmc
group by v.szgxmc,v.gxlx,v.yxjs,v.yxyl) B,
CY2GISUSER.油集输管线分段 C,
MDSYS.ALL_SDO_GEOM_METADATA M
WHERE A.YGXBM = C.TYBH
AND MONTHS_BETWEEN(SYSDATE, A.TYRQ) / 12 >= 15
AND C.zyqdmc like '%' || substr('全厂', 4, 1) || '%'
AND to_date(to_char(tyrq, 'yyyy-mm'), 'yyyy-mm') >=
to_date('1980-01', 'yyyy-mm')
AND B.szgxmc = C.mc
AND B.num >= power(2, 2)
AND M.table_name = '油集输管线分段'
AND M.COLUMN_NAME = 'GEOMETRY1'
AND M.OWNER = 'CY2GISUSER'
union
SELECT C.mc AS 管线名称,
A.ZYQDMC AS 区队,
A.ZDZMC AS 站号,
A.QDZMC AS 井号,
'φ' || A.GXWJ || ' X ' || A.GXBH AS 管线尺寸,
ROUND(SDO_GEOM.SDO_LENGTH(C.GEOMETRY1, M.diminfo), 2) AS "管线长度(米)",
ROUND(MONTHS_BETWEEN(ROUND(SYSDATE, 'MONTH'), ROUND(A.TYRQ, 'MONTH')) / 12,
0) AS 使用年限,
ROUND(power(B.num, 0.5)) as 维修次数,
to_char(sysdate,'yyyy-mm') as 年度,
B.gxlx as 管线类型,
B.yxjs as 影响井数,
B.yxyl as 影响液量
FROM GISLRUSER.DHA053 A,
(select v.gxlx,v.szgxmc,v.yxjs,v.yxyl,count(v.szgxmc) as num
from CY2GISUSER.管线维修地点 v, CY2GISUSER.管线维修地点 x
where v.szgxmc = x.szgxmc
group by v.szgxmc,v.gxlx,v.yxjs,v.yxyl) B,
CY2GISUSER.注入管线分段 C,
MDSYS.ALL_SDO_GEOM_METADATA M
WHERE A.ZSGXBM = C.TYBH
AND MONTHS_BETWEEN(SYSDATE, A.TYRQ) / 12 >= 15
AND C.zyqdmc like '%' || substr('全厂', 4, 1) || '%'
AND to_date(to_char(tyrq, 'yyyy-mm'), 'yyyy-mm') >=
to_date('1980-01', 'yyyy-mm')
AND B.szgxmc = C.mc
AND B.num >= power(2, 2)
AND M.table_name = '注入管线分段'
AND M.COLUMN_NAME = 'GEOMETRY1'
AND M.OWNER = 'CY2GISUSER'
A.ZYQDMC AS 区队,
A.ZDZMC AS 井号,
A.QDZMC AS 站号,
'φ' || A.GXWJ || ' X ' || A.GXBH AS 管线尺寸,
ROUND(SDO_GEOM.SDO_LENGTH(C.GEOMETRY1, M.diminfo), 2) AS "管线长度(米)",
ROUND(MONTHS_BETWEEN(ROUND(SYSDATE, 'MONTH'), ROUND(A.TYRQ, 'MONTH')) / 12,
0) AS 使用年限,
ROUND(power(B.num, 0.5)) as 维修次数,
B.gxlx as 管线类型,
to_char(sysdate,'yyyy-mm') as 年度,
B.yxjs as 影响井数,
B.yxyl as 影响液量
FROM GISLRUSER.DHA019 A,
(select v.gxlx,v.szgxmc,v.yxjs,v.yxyl,count(v.szgxmc) as num
from CY2GISUSER.管线维修地点 v, CY2GISUSER.管线维修地点 x
where v.szgxmc = x.szgxmc
group by v.szgxmc,v.gxlx,v.yxjs,v.yxyl) B,
CY2GISUSER.油集输管线分段 C,
MDSYS.ALL_SDO_GEOM_METADATA M
WHERE A.YGXBM = C.TYBH
AND MONTHS_BETWEEN(SYSDATE, A.TYRQ) / 12 >= 15
AND C.zyqdmc like '%' || substr('全厂', 4, 1) || '%'
AND to_date(to_char(tyrq, 'yyyy-mm'), 'yyyy-mm') >=
to_date('1980-01', 'yyyy-mm')
AND B.szgxmc = C.mc
AND B.num >= power(2, 2)
AND M.table_name = '油集输管线分段'
AND M.COLUMN_NAME = 'GEOMETRY1'
AND M.OWNER = 'CY2GISUSER'
union
SELECT C.mc AS 管线名称,
A.ZYQDMC AS 区队,
A.ZDZMC AS 站号,
A.QDZMC AS 井号,
'φ' || A.GXWJ || ' X ' || A.GXBH AS 管线尺寸,
ROUND(SDO_GEOM.SDO_LENGTH(C.GEOMETRY1, M.diminfo), 2) AS "管线长度(米)",
ROUND(MONTHS_BETWEEN(ROUND(SYSDATE, 'MONTH'), ROUND(A.TYRQ, 'MONTH')) / 12,
0) AS 使用年限,
ROUND(power(B.num, 0.5)) as 维修次数,
to_char(sysdate,'yyyy-mm') as 年度,
B.gxlx as 管线类型,
B.yxjs as 影响井数,
B.yxyl as 影响液量
FROM GISLRUSER.DHA053 A,
(select v.gxlx,v.szgxmc,v.yxjs,v.yxyl,count(v.szgxmc) as num
from CY2GISUSER.管线维修地点 v, CY2GISUSER.管线维修地点 x
where v.szgxmc = x.szgxmc
group by v.szgxmc,v.gxlx,v.yxjs,v.yxyl) B,
CY2GISUSER.注入管线分段 C,
MDSYS.ALL_SDO_GEOM_METADATA M
WHERE A.ZSGXBM = C.TYBH
AND MONTHS_BETWEEN(SYSDATE, A.TYRQ) / 12 >= 15
AND C.zyqdmc like '%' || substr('全厂', 4, 1) || '%'
AND to_date(to_char(tyrq, 'yyyy-mm'), 'yyyy-mm') >=
to_date('1980-01', 'yyyy-mm')
AND B.szgxmc = C.mc
AND B.num >= power(2, 2)
AND M.table_name = '注入管线分段'
AND M.COLUMN_NAME = 'GEOMETRY1'
AND M.OWNER = 'CY2GISUSER'
在网上逛了半天儿也没找什么有价值的资料,最后写了一个SQL语句,直接把这个字段的值更新了(由于“'”是Oracle的关键字,结果手工加了半天的单引号)
update sdpgwreport
set value = 'SELECT C.mc AS 管线名称,
A.ZYQDMC AS 区队,
A.ZDZMC AS 井号,
A.QDZMC AS 站号,
''φ'' || A.GXWJ || '' X '' || A.GXBH AS 管线尺寸,
ROUND(SDO_GEOM.SDO_LENGTH(C.GEOMETRY1, M.diminfo), 2) AS "管线长度(米)",
ROUND(MONTHS_BETWEEN(ROUND(SYSDATE, ''MONTH''), ROUND(A.TYRQ, ''MONTH'')) / 12,
0) AS 使用年限,
ROUND(power(B.num, 0.5)) as 维修次数,
B.gxlx as 管线类型,
to_char(sysdate,''yyyy-mm'') as 年度,
B.yxjs as 影响井数,
B.yxyl as 影响液量
FROM GISLRUSER.DHA019 A,
(select v.gxlx,v.szgxmc,v.yxjs,v.yxyl,count(v.szgxmc) as num
from CY2GISUSER.管线维修地点 v, CY2GISUSER.管线维修地点 x
where v.szgxmc = x.szgxmc
group by v.szgxmc,v.gxlx,v.yxjs,v.yxyl) B,
CY2GISUSER.油集输管线分段 C,
MDSYS.ALL_SDO_GEOM_METADATA M
WHERE A.YGXBM = C.TYBH
AND MONTHS_BETWEEN(SYSDATE, A.TYRQ) / 12 >= SDPWCA2
AND C.zyqdmc like ''%'' || substr(''SDPWCA1'', 4, 1) || ''%''
AND to_date(to_char(tyrq, ''yyyy-mm''), ''yyyy-mm'') >=
to_date(''1980-01'', ''yyyy-mm'')
AND B.szgxmc = C.mc
AND B.num >= power(SDPWCA15, 2)
AND M.table_name = ''油集输管线分段''
AND M.COLUMN_NAME = ''GEOMETRY1''
AND M.OWNER = ''CY2GISUSER''
union
SELECT C.mc AS 管线名称,
A.ZYQDMC AS 区队,
A.ZDZMC AS 站号,
A.QDZMC AS 井号,
''φ'' || A.GXWJ || '' X '' || A.GXBH AS 管线尺寸,
ROUND(SDO_GEOM.SDO_LENGTH(C.GEOMETRY1, M.diminfo), 2) AS "管线长度(米)",
ROUND(MONTHS_BETWEEN(ROUND(SYSDATE, ''MONTH''), ROUND(A.TYRQ, ''MONTH'')) / 12,
0) AS 使用年限,
ROUND(power(B.num, 0.5)) as 维修次数,
to_char(sysdate,''yyyy-mm'') as 年度,
B.gxlx as 管线类型,
B.yxjs as 影响井数,
B.yxyl as 影响液量
FROM GISLRUSER.DHA053 A,
(select v.gxlx,v.szgxmc,v.yxjs,v.yxyl,count(v.szgxmc) as num
from CY2GISUSER.管线维修地点 v, CY2GISUSER.管线维修地点 x
where v.szgxmc = x.szgxmc
group by v.szgxmc,v.gxlx,v.yxjs,v.yxyl) B,
CY2GISUSER.注入管线分段 C,
MDSYS.ALL_SDO_GEOM_METADATA M
WHERE A.ZSGXBM = C.TYBH
AND MONTHS_BETWEEN(SYSDATE, A.TYRQ) / 12 >= SDPWCA2
AND C.zyqdmc like ''%'' || substr(''SDPWCA1'', 4, 1) || ''%''
AND to_date(to_char(tyrq, ''yyyy-mm''), ''yyyy-mm'') >=
to_date(''1980-01'', ''yyyy-mm'')
AND B.szgxmc = C.mc
AND B.num >= power(SDPWCA15, 2)
AND M.table_name = ''注入管线分段''
AND M.COLUMN_NAME = ''GEOMETRY1''
AND M.OWNER = ''CY2GISUSER'''
where id = 318
set value = 'SELECT C.mc AS 管线名称,
A.ZYQDMC AS 区队,
A.ZDZMC AS 井号,
A.QDZMC AS 站号,
''φ'' || A.GXWJ || '' X '' || A.GXBH AS 管线尺寸,
ROUND(SDO_GEOM.SDO_LENGTH(C.GEOMETRY1, M.diminfo), 2) AS "管线长度(米)",
ROUND(MONTHS_BETWEEN(ROUND(SYSDATE, ''MONTH''), ROUND(A.TYRQ, ''MONTH'')) / 12,
0) AS 使用年限,
ROUND(power(B.num, 0.5)) as 维修次数,
B.gxlx as 管线类型,
to_char(sysdate,''yyyy-mm'') as 年度,
B.yxjs as 影响井数,
B.yxyl as 影响液量
FROM GISLRUSER.DHA019 A,
(select v.gxlx,v.szgxmc,v.yxjs,v.yxyl,count(v.szgxmc) as num
from CY2GISUSER.管线维修地点 v, CY2GISUSER.管线维修地点 x
where v.szgxmc = x.szgxmc
group by v.szgxmc,v.gxlx,v.yxjs,v.yxyl) B,
CY2GISUSER.油集输管线分段 C,
MDSYS.ALL_SDO_GEOM_METADATA M
WHERE A.YGXBM = C.TYBH
AND MONTHS_BETWEEN(SYSDATE, A.TYRQ) / 12 >= SDPWCA2
AND C.zyqdmc like ''%'' || substr(''SDPWCA1'', 4, 1) || ''%''
AND to_date(to_char(tyrq, ''yyyy-mm''), ''yyyy-mm'') >=
to_date(''1980-01'', ''yyyy-mm'')
AND B.szgxmc = C.mc
AND B.num >= power(SDPWCA15, 2)
AND M.table_name = ''油集输管线分段''
AND M.COLUMN_NAME = ''GEOMETRY1''
AND M.OWNER = ''CY2GISUSER''
union
SELECT C.mc AS 管线名称,
A.ZYQDMC AS 区队,
A.ZDZMC AS 站号,
A.QDZMC AS 井号,
''φ'' || A.GXWJ || '' X '' || A.GXBH AS 管线尺寸,
ROUND(SDO_GEOM.SDO_LENGTH(C.GEOMETRY1, M.diminfo), 2) AS "管线长度(米)",
ROUND(MONTHS_BETWEEN(ROUND(SYSDATE, ''MONTH''), ROUND(A.TYRQ, ''MONTH'')) / 12,
0) AS 使用年限,
ROUND(power(B.num, 0.5)) as 维修次数,
to_char(sysdate,''yyyy-mm'') as 年度,
B.gxlx as 管线类型,
B.yxjs as 影响井数,
B.yxyl as 影响液量
FROM GISLRUSER.DHA053 A,
(select v.gxlx,v.szgxmc,v.yxjs,v.yxyl,count(v.szgxmc) as num
from CY2GISUSER.管线维修地点 v, CY2GISUSER.管线维修地点 x
where v.szgxmc = x.szgxmc
group by v.szgxmc,v.gxlx,v.yxjs,v.yxyl) B,
CY2GISUSER.注入管线分段 C,
MDSYS.ALL_SDO_GEOM_METADATA M
WHERE A.ZSGXBM = C.TYBH
AND MONTHS_BETWEEN(SYSDATE, A.TYRQ) / 12 >= SDPWCA2
AND C.zyqdmc like ''%'' || substr(''SDPWCA1'', 4, 1) || ''%''
AND to_date(to_char(tyrq, ''yyyy-mm''), ''yyyy-mm'') >=
to_date(''1980-01'', ''yyyy-mm'')
AND B.szgxmc = C.mc
AND B.num >= power(SDPWCA15, 2)
AND M.table_name = ''注入管线分段''
AND M.COLUMN_NAME = ''GEOMETRY1''
AND M.OWNER = ''CY2GISUSER'''
where id = 318
数据就这么添进去了,但为什么用PL/SQL Developer直接操作时会报错呢,在此大胆猜测一下,估计是该软件在进行OCI编程时的一个BUG,SQL语句过长就会出现这样的问题。