一次数据转换错误处理过程简记
分享
客户的环境 :
ArcDesktop 10.0+Sp1
ArcSDE:9.3+no anp sp for Oracle10G
Oracle10.2.0.4
数据以OracleSpatial的SDO_GEOMETRY存储
症状:
客户的数据使用Desktop在从OracleSpatial的存储方式转换成ESRI 的ST_GEOMETRY存储方式的时候,在进行转换的时候Desktop报了一个错误,如下图
解决过程:
从上图中可以看出来,错误应该是在导完数据后,然后创建空间索引的时候出的错误,除了ORA-06502这个Oracle的错误别的信息再也得不到了。现在首要任务是先确定是创建哪个索引出的错误,因此跟踪了一下Oracle后台所执行的SQL,从trace文件中找到了以下的创建空间索引的SQL语句,
1:
=====================
2:
PARSING IN CURSOR #77 len=195 dep=1 uid=56 oct=9 lid=56 tim=1261349604022053 hv=1458844053 ad='8b6afbd0'
3:
CREATE INDEX SDE.A748_IX1 ON SDE.GEOGRAPHY_GEO_PD_POLE(SHAPE) INDEXTYPE IS SDE.ST_SPATIAL_INDEX PARAMETERS('ST_GRIDS = .644253356887482 ST_SRID = 3 ST_COMMIT_ROWS = 10000 PCTFREE 0 INITRANS 4'
)
4:
END OF STMT
5:
PARSE #77:c=14997,e=14533,p=0,cr=391,cu=0,mis=1,r=0,dep=1,og=1,tim=1261349604022050
6:
BINDS #77:
使用sqlplus手动执行一下该sql语句,也报错误,见下图:
在sqlplus中发现了更详细的错误信息,错误发生在SDE.SPX_UTIL这个包中的第125行(红色标注的那行),到数据库查看一下这行的内容如下:
1:
pos := instr(buffer,'ST_GRIDS'
);
2:
if
(pos > 0) then
3:
4:
spx_info_r.grid := SDE.sp_grid_info(-1,-1,-1);
5:
equal_pos := instr(buffer,'='
,pos);
6:
buf2 := substr(buffer,equal_pos+1,tot_length - pos);
7:
buf2 := ltrim(buf2);
8:
9:
comma_pos := instr(buf2,','
);
10:
if
(comma_pos > 0) then
11:
12:
strval := substr(buf2,1,comma_pos - 1);
13:
spx_info_r.grid.grid1 := to_number(strval,'999999999999999d999999999' ,' NLS_NUMERIC_CHARACTERS = ' '. ' ' ' );
14:
comma_pos := comma_pos + 1;
15:
else
16:
space_pos := instr(buf2,' '
,1);
17:
if
(space_pos > 0) then
18:
strval := substr(buf2,1,space_pos - 1);
19:
else
20:
strval := substr(buf2,1,tot_length);
21:
end
if
;
22:
spx_info_r.grid.grid1 := to_number(strval,'999999999999999d999999999'
,' NLS_NUMERIC_CHARACTERS = '
'. '
' '
);
23:
spx_info_r.grid.grid2 := 0;
24:
spx_info_r.grid.grid3 := 0;
25:
26:
end
if
;
其中红色所标出来的行为出错的一行,出错的函数应该是to_number 函数,strval为网格大小的字符串表示,创建索引的时候执行的是:
spx_info_r.grid.grid1 := to_number(’.644253356887482’ ,'999999999999999d999999999' ,' NLS_NUMERIC_CHARACTERS = ' '. ' ' ' );
在sqlplus中执行这个sql语句同样是不正确的,错误的结果如下所示:
1:
SQL
> select
to_number(’.644253356887482’ ,'999999999999999d999999999'
,' NLS_NUMERIC_CHARACTERS = '
'. '
' '
) from
dual;
2:
select
to_number(’.644253356887482’ ,'999999999999999d999999999'
,' NLS_NUMERIC_CHARACTERS = '
'. '
' '
) from
dual
3:
*
4:
ERROR at
line 1:
5:
ORA-00911: invalid character
原因是字符小数点后面为15个数字,而格式化字符d999999999只有9个字符,因此会报这个错误,增加格式化字符的数量就可以了,如下所示:
1:
SQL
> select
to_number('.644253356887482'
,'999999999999999d9999999999999999999999999'
,' NLS_NUMERIC_CHARACTERS = '
'. '
' ') from dual;
2:
3:
TO_NUMBER('.644253356887482'
,'999999999999999D9999999999999999999999999'
,'NLS_NU
4:
--------------------------------------------------------------------------------
5:
.644253357
将代码改成
1:
spx_info_r.grid.grid1 := to_number(strval,'999999999999999d9999999999999999999999999'
,' NLS_NUMERIC_CHARACTERS = '
'. '
' '
); 后重新编译该包后问题得以临时解决。
原因分析 :
严格来说,应该是ArcSDE的bug,但是也有可能是因为前段软件使用的是高版本的原因,像此类问题估计只能通过打补丁进行修改了,后经过查看9.3.1的此部分的代码,发现的确是发生了变化。摘录一部分:
1:
--Parse strings
2:
--ST_GRIDS
3:
--ST_SRID
4:
--ST_COMMIT_ROWS
5:
6:
buffer := upper
(params);
7:
buffer := REPLACE(buffer, chr(10), ' '
);
8:
9:
buffer := ltrim(buffer);
10:
11:
TEXT
12:
--------------------------------------------------------------------------------
13:
buffer := rtrim(buffer);
14:
tot_length := length(buffer);
15:
16:
pos := instr(buffer,'ST_GRIDS'
);
17:
if
(pos > 0) then
18:
19:
spx_info_r.grid := SDE.sp_grid_info(-1,-1,-1);
20:
equal_pos := instr(buffer,'='
,pos);
21:
buf2 := substr(buffer,equal_pos+1,tot_length - pos);
22:
buf2 := ltrim(buf2);
23:
24:
25:
TEXT
26:
--------------------------------------------------------------------------------
27:
SELECT
value
into
num_charset FROM
NLS_SESSION_PARAMETERS where
parameter
='N
28:
LS_NUMERIC_CHARACTERS';
29:
30:
31:
IF
num_charset = ',.'
THEN
32:
nls_type := 2;
33:
Elsif num_charset = '.,'
Then
34:
nls_type := 1;
35:
Elsif num_charset = ', '
Then
36:
nls_type := 3;
37:
Else
38:
39:
TEXT
40:
--------------------------------------------------------------------------------
41:
nls_type := 4;
42:
nls_dec := substr(num_charset,1,1);
43:
nls_sep := substr(num_charset,2,1);
44:
End
If
;
45:
46:
nls_dec := substr(num_charset,1,1);
47:
48:
If
nls_type = 1 Then
49:
buf2 := REPLACE(buf2,':'
,','
);
50:
Elsif nls_type = 2 Then
51:
colon_pos := instr(buf2,':'
);
52:
53:
TEXT
54:
--------------------------------------------------------------------------------
55:
if
colon_pos = 0 Then
56:
comma_pos := instr(buf2,','
);
57:
if
comma_pos > 0 Then
58:
comma2_pos := instr(buf2,','
,comma_pos+1);
59:
if
(comma2_pos > 0) then
60:
raise_application_error (SDE.st_type_util.spx_invalid_grid_format,'I
61:
nvalid grid format. Use '':'
' grid delimiter.'
);
62:
63:
End
If
;
64:
End
If
;
65:
End
If
;
文章来源:http://blog.csdn.net/liufeng1980423/article/details/6072375
ArcDesktop 10.0+Sp1
ArcSDE:9.3+no anp sp for Oracle10G
Oracle10.2.0.4
数据以OracleSpatial的SDO_GEOMETRY存储
症状:
客户的数据使用Desktop在从OracleSpatial的存储方式转换成ESRI 的ST_GEOMETRY存储方式的时候,在进行转换的时候Desktop报了一个错误,如下图
解决过程:
从上图中可以看出来,错误应该是在导完数据后,然后创建空间索引的时候出的错误,除了ORA-06502这个Oracle的错误别的信息再也得不到了。现在首要任务是先确定是创建哪个索引出的错误,因此跟踪了一下Oracle后台所执行的SQL,从trace文件中找到了以下的创建空间索引的SQL语句,
1:
=====================
2:
PARSING IN CURSOR #77 len=195 dep=1 uid=56 oct=9 lid=56 tim=1261349604022053 hv=1458844053 ad='8b6afbd0'
3:
CREATE INDEX SDE.A748_IX1 ON SDE.GEOGRAPHY_GEO_PD_POLE(SHAPE) INDEXTYPE IS SDE.ST_SPATIAL_INDEX PARAMETERS('ST_GRIDS = .644253356887482 ST_SRID = 3 ST_COMMIT_ROWS = 10000 PCTFREE 0 INITRANS 4'
)
4:
END OF STMT
5:
PARSE #77:c=14997,e=14533,p=0,cr=391,cu=0,mis=1,r=0,dep=1,og=1,tim=1261349604022050
6:
BINDS #77:
使用sqlplus手动执行一下该sql语句,也报错误,见下图:
在sqlplus中发现了更详细的错误信息,错误发生在SDE.SPX_UTIL这个包中的第125行(红色标注的那行),到数据库查看一下这行的内容如下:
1:
pos := instr(buffer,'ST_GRIDS'
);
2:
if
(pos > 0) then
3:
4:
spx_info_r.grid := SDE.sp_grid_info(-1,-1,-1);
5:
equal_pos := instr(buffer,'='
,pos);
6:
buf2 := substr(buffer,equal_pos+1,tot_length - pos);
7:
buf2 := ltrim(buf2);
8:
9:
comma_pos := instr(buf2,','
);
10:
if
(comma_pos > 0) then
11:
12:
strval := substr(buf2,1,comma_pos - 1);
13:
spx_info_r.grid.grid1 := to_number(strval,'999999999999999d999999999' ,' NLS_NUMERIC_CHARACTERS = ' '. ' ' ' );
14:
comma_pos := comma_pos + 1;
15:
else
16:
space_pos := instr(buf2,' '
,1);
17:
if
(space_pos > 0) then
18:
strval := substr(buf2,1,space_pos - 1);
19:
else
20:
strval := substr(buf2,1,tot_length);
21:
end
if
;
22:
spx_info_r.grid.grid1 := to_number(strval,'999999999999999d999999999'
,' NLS_NUMERIC_CHARACTERS = '
'. '
' '
);
23:
spx_info_r.grid.grid2 := 0;
24:
spx_info_r.grid.grid3 := 0;
25:
26:
end
if
;
其中红色所标出来的行为出错的一行,出错的函数应该是to_number 函数,strval为网格大小的字符串表示,创建索引的时候执行的是:
spx_info_r.grid.grid1 := to_number(’.644253356887482’ ,'999999999999999d999999999' ,' NLS_NUMERIC_CHARACTERS = ' '. ' ' ' );
在sqlplus中执行这个sql语句同样是不正确的,错误的结果如下所示:
1:
SQL
> select
to_number(’.644253356887482’ ,'999999999999999d999999999'
,' NLS_NUMERIC_CHARACTERS = '
'. '
' '
) from
dual;
2:
select
to_number(’.644253356887482’ ,'999999999999999d999999999'
,' NLS_NUMERIC_CHARACTERS = '
'. '
' '
) from
dual
3:
*
4:
ERROR at
line 1:
5:
ORA-00911: invalid character
原因是字符小数点后面为15个数字,而格式化字符d999999999只有9个字符,因此会报这个错误,增加格式化字符的数量就可以了,如下所示:
1:
SQL
> select
to_number('.644253356887482'
,'999999999999999d9999999999999999999999999'
,' NLS_NUMERIC_CHARACTERS = '
'. '
' ') from dual;
2:
3:
TO_NUMBER('.644253356887482'
,'999999999999999D9999999999999999999999999'
,'NLS_NU
4:
--------------------------------------------------------------------------------
5:
.644253357
将代码改成
1:
spx_info_r.grid.grid1 := to_number(strval,'999999999999999d9999999999999999999999999'
,' NLS_NUMERIC_CHARACTERS = '
'. '
' '
); 后重新编译该包后问题得以临时解决。
原因分析 :
严格来说,应该是ArcSDE的bug,但是也有可能是因为前段软件使用的是高版本的原因,像此类问题估计只能通过打补丁进行修改了,后经过查看9.3.1的此部分的代码,发现的确是发生了变化。摘录一部分:
1:
--Parse strings
2:
--ST_GRIDS
3:
--ST_SRID
4:
--ST_COMMIT_ROWS
5:
6:
buffer := upper
(params);
7:
buffer := REPLACE(buffer, chr(10), ' '
);
8:
9:
buffer := ltrim(buffer);
10:
11:
TEXT
12:
--------------------------------------------------------------------------------
13:
buffer := rtrim(buffer);
14:
tot_length := length(buffer);
15:
16:
pos := instr(buffer,'ST_GRIDS'
);
17:
if
(pos > 0) then
18:
19:
spx_info_r.grid := SDE.sp_grid_info(-1,-1,-1);
20:
equal_pos := instr(buffer,'='
,pos);
21:
buf2 := substr(buffer,equal_pos+1,tot_length - pos);
22:
buf2 := ltrim(buf2);
23:
24:
25:
TEXT
26:
--------------------------------------------------------------------------------
27:
SELECT
value
into
num_charset FROM
NLS_SESSION_PARAMETERS where
parameter
='N
28:
LS_NUMERIC_CHARACTERS';
29:
30:
31:
IF
num_charset = ',.'
THEN
32:
nls_type := 2;
33:
Elsif num_charset = '.,'
Then
34:
nls_type := 1;
35:
Elsif num_charset = ', '
Then
36:
nls_type := 3;
37:
Else
38:
39:
TEXT
40:
--------------------------------------------------------------------------------
41:
nls_type := 4;
42:
nls_dec := substr(num_charset,1,1);
43:
nls_sep := substr(num_charset,2,1);
44:
End
If
;
45:
46:
nls_dec := substr(num_charset,1,1);
47:
48:
If
nls_type = 1 Then
49:
buf2 := REPLACE(buf2,':'
,','
);
50:
Elsif nls_type = 2 Then
51:
colon_pos := instr(buf2,':'
);
52:
53:
TEXT
54:
--------------------------------------------------------------------------------
55:
if
colon_pos = 0 Then
56:
comma_pos := instr(buf2,','
);
57:
if
comma_pos > 0 Then
58:
comma2_pos := instr(buf2,','
,comma_pos+1);
59:
if
(comma2_pos > 0) then
60:
raise_application_error (SDE.st_type_util.spx_invalid_grid_format,'I
61:
nvalid grid format. Use '':'
' grid delimiter.'
);
62:
63:
End
If
;
64:
End
If
;
65:
End
If
;
文章来源:http://blog.csdn.net/liufeng1980423/article/details/6072375
0 个评论
相关问题
- 作为Desktop用户对数据的获取比较乏力,可能缺了哪些知识?
- 有谁知道哪里可以免费获取各省市地图数据呢,包括详细的shape文件数据
- 夜间灯光影像处理的案例教程
- Drone2Map所能处理的无人机影像具体要求?
- 有栅格数据a(大数据),小数据b,怎么从a中扣掉b,得到a数据剩下的部分?栅格能裁剪取反吗?
- 用ArcMap输入7参数,从wgs84坐标系转西安80坐标系,转后误差较大,4米左右,请问这是正常的吗?如何能提高转换精度?
- 两个栅格文件分辨率一致,但像元位置不一致,如何处理?
- 在windows server2008 R2 64位中安装了portal 10.5 设置好托管arcgis server站点,并用data store为托管数据库,发布数据出错
- ArcGIS数据库数据逐图幅裁剪
- ArcGIS model builder 输出文件名处理
- cityengine怎么导入arcgis的shp数据,对shp数据有什么要求