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); 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语句来代替自定义聚集函数。
