一次数据转换错误处理过程简记

0
分享 2015-12-03
客户的环境
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 个评论

要回复文章请先登录注册