Oracle9i的1467错误解决方法

会计考友 · 发表于 2012-8-4 14:06:19

在使用自定义聚集函数时出现了一个ORA-1467错误。

根据Oracle文档上的描述，1467错误是由于排序的键值超过了DB_BLOCK_SIZE。
但是出现错误的SQL似乎并不满足这个条件。下面简单构造这个错误：
SQL> CREATE OR REPLACE TYPE T_LINK AS OBJECT (
2 STR VARCHAR2(30000),
3 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER,
4 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
5 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
6 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER
7 )
8 /
Type created.
SQL> CREATE OR REPLACE TYPE BODY T_LINK IS
2 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER IS
3 BEGIN
4 SCTX := T_LINK(NULL);
5 RETURN ODCICONST.SUCCESS;
6 END;
7
8 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
9 BEGIN
10 SELF.STR := SELF.STR || VALUE || ’,’;
11 RETURN ODCICONST.SUCCESS;
12 END;
13
14 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
15 BEGIN
16 RETURNVALUE := SUBSTR(SELF.STR, 1, LENGTH(SELF.STR) - 1);
17 RETURN ODCICONST.SUCCESS;
18 END;
19
20 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS
21 BEGIN
22 NULL;
23 RETURN ODCICONST.SUCCESS;

会计考友 · 发表于 2012-8-4 14:06:20

24 END;
25 END;
26 /
Type body created.
SQL> CREATE OR REPLACE FUNCTION F_LINK(P_STR VARCHAR2) RETURN VARCHAR2
2 AGGREGATE USING T_LINK;
3 /
Function created.
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> SELECT OWNER, F_LINK(OBJECT_NAME), F_LINK(SUBOBJECT_NAME), F_LINK(OBJECT_ID)
2 FROM T
3 GROUP BY OWNER;
SELECT OWNER, F_LINK(OBJECT_NAME), F_LINK(SUBOBJECT_NAME), F_LINK(OBJECT_ID)
*
ERROR at line 1:
ORA-01467: sort key too long
SQL> DESC T
Name Null? Type
--------------------------------- -------- --------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
OWNER列的长度只有30，而且即使换成长度为1的列也没有作用。
SQL> SELECT TEMPORARY, F_LINK(OBJECT_NAME), F_LINK(SUBOBJECT_NAME), F_LINK(OBJECT_ID)
2 FROM T
3 GROUP BY TEMPORARY;
SELECT TEMPORARY, F_LINK(OBJECT_NAME), F_LINK(SUBOBJECT_NAME), F_LINK(OBJECT_ID)
*
ERROR at line 1:
ORA-01467: sort key too long
既然和GROUP BY列没有太大的关系，那么是否与F_LINK输入列的长度有关：
SQL> SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3)
2 FROM T
3 GROUP BY OWNER;
SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3)
*
ERROR at line 1:
ORA-01467: sort key too long
即使将F_LINK的输入参数变成常数1、2、3，问题仍然会出现，看来和F_LINK输入参数的长度也没有关系。
虽然与F_LINK输入参数长度没有关系，但是和F_LINK函数调用次数有关，将F_LINK三次调用变为两次调用，就可以得到结果：
SQL> SELECT OWNER, F_LINK(1), F_LINK(2)
2 FROM T
3 WHERE ROWNUM < 10
4 GROUP BY OWNER;
OWNER F_LINK(1) F_LINK(2)
------------------------------ ------------------------------ -----------------
SYS 1,1,1,1,1,1,1,1,1 2,2,2,2,2,2,2,2,2
这个问题在9i上就会出现，而且与DB_BLOCK_SIZE的大小没有关系。在DB_BLOCK_SIZE为8K和16K的环境下测试，得到的结果完全一样。
SQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;
Table created.
SQL> SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3)
2 FROM T
3 GROUP BY OWNER;
SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3)
*
ERROR at line 1:
ORA-01467: sort key too long

SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_NUMBER NUMBER;
3 V_STR VARCHAR2(4000);

会计考友 · 发表于 2012-8-4 14:06:21

4 V_RES NUMBER;
5 BEGIN
6 V_RES := DBMS_UTILITY.GET_PARAMETER_VALUE(’db_block_size’, V_NUMBER, V_STR);
7 DBMS_OUTPUT.PUT_LINE(V_NUMBER);
8 END;
9 /
8192
PL/SQL procedure successfully completed.
SQL> CONN TEST@GPODB
Enter password:
Connected.
SQL> SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3)
2 FROM T
3 GROUP BY OWNER;
SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3)
*
ERROR at line 1:
ORA-01467: sort key too long

SQL> SHOW PARAMETER DB_BLOCK_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 16384
而这个问题在10g已经得到了解决，测试发现即使有30多个F_LINK的调用，也没有出现ORA-1467错误。
SQL> CONN YANGTK/YANGTK@YTK已连接。
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> COL F_LINK(1) FORMAT A20
SQL> COL F_LINK(2) FORMAT A20
SQL> COL F_LINK(3) FORMAT A20
SQL> COL F_LINK(4) FORMAT A20
SQL> SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3), F_LINK(4)
2 FROM T
3 WHERE ROWNUM < 5
4 GROUP BY OWNER;
OWNER F_LINK(1) F_LINK(2) F_LINK(3) F_LINK(4)
-------------------- -------------------- -------------------- -------------------- ------
SYS 1,1,1,1 2,2,2,2 3,3,3,3 4,4,4,4
在9i上如果碰到这个问题，可以考虑使用变通的方法解决。比如使用CONNECT BY语句来代替自定义聚集函数。

		自动登录	找回密码
密码			立即注册

[考试辅导] Oracle9i的1467错误解决方法

Oracle9i的1467错误解决方法

Oracle9i的1467错误解决方法