24 END;
3 u1 X' o2 b' x, u25 END;
% t0 {6 n) P$ f. `* F K% _5 {6 n5 `26 / 3 a A* m! U" i$ a+ Q$ P
Type body created.
4 n( L. l, O+ X, `2 I9 N- p4 h) gSQL> CREATE OR REPLACE FUNCTION F_LINK(P_STR VARCHAR2) RETURN VARCHAR2
6 h$ Z; R. C# V6 D5 z7 P2 AGGREGATE USING T_LINK;
' ~$ s$ Y3 ?9 x' H, O3 / 2 P! ?- J5 o5 F B, Q8 x0 x. w% Q; x
Function created.
8 ~& F4 y. \0 E* H: r( V/ ~1 tSQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
. {, E( J/ n q/ z: b* u4 Z- h3 `Table created.
; Y! M8 H$ A; b$ ySQL> SELECT OWNER, F_LINK(OBJECT_NAME), F_LINK(SUBOBJECT_NAME), F_LINK(OBJECT_ID) 4 b% @( X0 M) a- K& X4 Y
2 FROM T j7 p5 p* _+ ? b, M
3 GROUP BY OWNER;
3 _) |' K J- y* }SELECT OWNER, F_LINK(OBJECT_NAME), F_LINK(SUBOBJECT_NAME), F_LINK(OBJECT_ID) 4 q5 C: W; a# m9 p
* 3 ^5 U" ]' u8 p# P) W
ERROR at line 1:
& |, V: y l( G; q4 MORA-01467: sort key too long ; W' p V& f0 m' e
SQL> DESC T
% T2 u) e3 ]$ n* \6 `Name Null? Type
( J c' D) w8 X& t--------------------------------- -------- -------------- 4 |6 B7 T+ _' E: G* N
OWNER VARCHAR2(30) 0 w0 W5 `( |" |7 g" l
OBJECT_NAME VARCHAR2(128) 2 s4 {8 e6 i5 A, ]: e
SUBOBJECT_NAME VARCHAR2(30) # p; S+ |& C# |5 A" ]! T2 \ X
OBJECT_ID NUMBER ; u" T3 C& S0 ^/ l6 ` z8 [
DATA_OBJECT_ID NUMBER ; ~& |- I% s. x8 o; X( B; L
OBJECT_TYPE VARCHAR2(18)
4 A8 X' w3 ]" g# }CREATED DATE
, H2 C, z8 s7 `1 CLAST_DDL_TIME DATE
# G4 @, _9 o oTIMESTAMP VARCHAR2(19) 1 n) J* o% D3 z
STATUS VARCHAR2(7)
; a3 x* E6 R; X. a1 B! WTEMPORARY VARCHAR2(1) " i8 q2 ^$ k' J% M3 R) H) h! B; X
GENERATED VARCHAR2(1)
" u+ s& ^, m' a/ G! m$ ? CSECONDARY VARCHAR2(1) 1 Y9 J& S2 S9 S4 ^$ h
OWNER列的长度只有30,而且即使换成长度为1的列也没有作用。
d1 O5 B* J! {SQL> SELECT TEMPORARY, F_LINK(OBJECT_NAME), F_LINK(SUBOBJECT_NAME), F_LINK(OBJECT_ID) ' |, H" Z* P" F2 i" c% \: O: |) S
2 FROM T
6 V8 P6 k7 A) s3 Y; [, o" b3 GROUP BY TEMPORARY; ; | ~, l! V: @4 y
SELECT TEMPORARY, F_LINK(OBJECT_NAME), F_LINK(SUBOBJECT_NAME), F_LINK(OBJECT_ID) ( y4 d- F3 ~" J7 L
*
; M/ D/ a+ a" Q& U7 CERROR at line 1:
* y, f6 I4 O2 zORA-01467: sort key too long
2 r4 H0 e. F$ o既然和GROUP BY列没有太大的关系,那么是否与F_LINK输入列的长度有关: 7 j% T7 I. D1 [7 ]7 |* V, A1 x8 W- A
SQL> SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3) : Z3 o! M6 { I- I U4 Y" R. r% V& g
2 FROM T Y( ` p6 i9 U1 @& Z1 A/ k
3 GROUP BY OWNER; + Q8 s: a) s- ~+ n( ^
SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3) p: N9 e4 ]4 D/ l" E
* " g9 n8 C% ?; s/ R' V+ g
ERROR at line 1:
5 B& r# l8 H' B9 v3 dORA-01467: sort key too long
6 ~) |! @ T4 i) e# r6 l2 |( M即使将F_LINK的输入参数变成常数1、2、3,问题仍然会出现,看来和F_LINK输入参数的长度也没有关系。
/ Q+ ^% C( a1 ?2 V+ B3 g S; v$ H虽然与F_LINK输入参数长度没有关系,但是和F_LINK函数调用次数有关,将F_LINK三次调用变为两次调用,就可以得到结果:
- H% b1 Z6 i& }7 ~# M# HSQL> SELECT OWNER, F_LINK(1), F_LINK(2) # Z% A- p6 g" F- y+ O+ F
2 FROM T ) v6 T* g0 Q/ U
3 WHERE ROWNUM < 10
' D2 m5 ?' ] E/ ?4 ~4 GROUP BY OWNER;
, u- x2 K! j# ~' |, {/ V# V9 WOWNER F_LINK(1) F_LINK(2) * N p1 t" n+ v* {2 x% u% }
------------------------------ ------------------------------ -----------------
1 p P& J& j5 b0 v, j, F Y# ZSYS 1,1,1,1,1,1,1,1,1 2,2,2,2,2,2,2,2,2
p" H' m. D4 c9 @; O$ M4 g( m这个问题在9i上就会出现,而且与DB_BLOCK_SIZE的大小没有关系。在DB_BLOCK_SIZE为8K和16K的环境下测试,得到的结果完全一样。
3 m$ b4 `( Y3 g1 }$ {5 R) M! n2 YSQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS; : i6 j/ _ c7 b
Table created. 1 q- T8 Y$ b" b8 M" q
SQL> SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3)
% V+ Z9 ]4 `# X: \$ u3 [4 n: T% H2 FROM T
8 K4 B3 G y& p5 n% j6 p; N3 GROUP BY OWNER;
6 ?: C# @: ?/ bSELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3) 6 C2 v6 ], x6 w5 T6 \: M
*
8 O" R2 J% t2 i5 S0 ~/ W: ?) qERROR at line 1:
4 m9 l! B# U8 b2 QORA-01467: sort key too long
! ~& m W) l5 C
( [ p9 B) |8 F8 i+ ^6 B! p5 FSQL> SET SERVEROUT ON
- l! g9 ~7 C- y4 Z- gSQL> DECLARE ! m( t- {* ]" n& D/ ^# b* v
2 V_NUMBER NUMBER; 1 n1 F* W' x0 {# M
3 V_STR VARCHAR2(4000); |