字符串缓冲区
自定义聚合函数以及字符串连接超长的解决 博客分类: 数据库
oracle数据库自定义聚合函数字符缓冲区太小 .
Oracle的定制功能十分强大。前几天在碰到一个问题的时候,查询文档发现,oracle不但允许用户定制自己的函数,还可以定制自己的聚集函数和分析函数。
下面是我使用自定义聚集函数建立一个字符串“sum”的小例子。
类型声明:
CREATE OR REPLACE TYPE SUM_LINK AS OBJECT (
STR VARCHAR2(30000),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT SUM_LINK) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT SUM_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN SUM_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT SUM_LINK, CTX2 IN SUM_LINK) RETURN NUMBER
)
类型主体:
CREATE OR REPLACE TYPE BODY SUM_LINK IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT SUM_LINK) RETURN NUMBER IS
BEGIN
SCTX := SUM_LINK(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT SUM_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
SELF.STR := SELF.STR || VALUE||';';
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN SUM_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := SELF.STR;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT SUM_LINK, CTX2 IN SUM_LINK) RETURN NUMBER IS
BEGIN
NULL;
RETURN ODCICONST.SUCCESS;
END;
END;
方法创建:
CREATE OR REPLACE FUNCTION SUM_LINK(P_STR VARCHAR2) RETURN VARCHAR2
AGGREGATE USING SUM_LINK;
建表、插入数据,用来测试:
CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(20));
INSERT INTO TEST VALUES (1, 'AAA');
INSERT INTO TEST VALUES (2, 'BBB');
INSERT INTO TEST VALUES (1, 'ABC');
INSERT INTO TEST VALUES (3, 'CCC');
INSERT INTO TEST VALUES (2, 'DDD');
COMMIT;
测试自定义函数和类型:
SQL> SELECT ID, SUM_LINK(NAME) NAME FROM TEST GROUP BY ID;
ID NAME
---------- ----------------------------
1 AAA;ABC;
2 BBB;DDD;
3 CCC;
这里介绍一下通过SQL的方法来解决同样的问题。
对于下面这个利用自定义聚集函数的例子,将其改写为直接用SQL实现:
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------ -------- ------------------------------------ ------- ----------
SY_NAME SYNONYM
T TABLE
TEST TABLE
TEST1 TABLE
SUM_BLOB TABLE
SUM_CLOB TABLE
V_T VIEW
V_TEST VIEW
已选择8行。
SQL> SELECT TABTYPE, SUM_LINK(TNAME) TNAME FROM TAB GROUP BY TABTYPE;
TABTYPE TNAME
------- --------------------------------------------------
SYNONYM SY_NAME
TABLE T,TEST1,SUM_CLOB,SUM_BLOB,TEST
VIEW V_T,V_TEST
除了利用自定义聚集函数外,SQL函数中能将多个字符串合并在一起的只有SYS_CONNECSUM_BY_PATH了。
而这个函数只能应用在树型查询中,为了能使用这个函数,必须人为的构造出树来。
也就是说,必须可以构造出一个CONNECT BY列使得相同的TABTYPE的TNAME可以用SYS_CONNECSUM_BY_PATH连接起来。
连接列可以使用ROW_NUMBER() OVER()来构造,在CONNECT BY的时候指定当前列的等于父列的值加1。
并在START WITH时指定起始值为1。
最后对TABTYPE进行分组,取得最大值就是最终需要的结果:
SQL> SELECT TABTYPE, MAX(LTRIM(SYS_CONNECSUM_BY_PATH(TNAME, ','), ',')) TNAME
2 FROM
3 (
4 SELECT TABTYPE, TNAME, ROW_NUMBER() OVER(PARTITION BY TABTYPE ORDER BY TNAME) RN
5 FROM TAB
6 )
7 START WITH RN = 1
8 CONNECT BY PRIOR RN + 1 = RN
9 AND PRIOR TABTYPE = TABTYPE
10 GROUP BY TABTYPE;
TABTYPE TNAME
------- --------------------------------------------------
SYNONYM SY_NAME
TABLE T,TEST,TEST1,SUM_BLOB,SUM_CLOB
VIEW V_T,V_TEST
不过上面两种方法都会面临一个问题,就是如果聚集连接的字符串长度如果超过了VARCHAR2类型所允许的最大长度,就会导致字符串超长的错误。
自定义聚集函数这里就不重复了,可以参考上面的介绍:
SQL> SELECT SUM_LINK(TNAME) FROM TAB;
SUM_LINK(TNAME)
-------------------------------------------------------------------------------------------
BAK_SHGOV_ORDER,BAK_SHGOV_ORDER_BAK,PLAN_TABLE,SHGOV_ORDER,SHGOV_ORDER_BAK,T,T1,TEST,SUM_SQL
SQL> SELECT SUM_LINK(SEQUENCE_NAME) FROM ALL_SEQUENCES;
SELECT SUM_LINK(SEQUENCE_NAME) FROM ALL_SEQUENCES
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在"TEST.SUM_LINK", line 16
ORA-06512: 在line 1
SQL> SELECT MAX(LTRIM(SYS_CONNECSUM_BY_PATH(SEQUENCE_NAME, ','), ',')) NAME
2 FROM
3 (
4 SELECT SEQUENCE_NAME, ROW_NUMBER() OVER(ORDER BY SEQUENCE_NAME) RN
5 FROM ALL_SEQUENCES
6 )
7 START WITH RN = 1
8 CONNECT BY PRIOR RN + 1 = RN
9 ;
FROM ALL_SEQUENCES
*
ERROR 位于第 5 行:
ORA-01489: 字符串连接的结果过长
显然是由于要连接的字符串太长了,导致Oracle的字符串处理过程中出现了错误。上面的两种方法都没有办法避免这个问题。
但是ALL_SEQUENCES中的记录只有几百个,每个名称的长度不会超过30,因此最终的长度不会超过32767。
根据Oracle给出的错误信息,显然是在处理输出参数RETURNVALUE的时候是安装SQL类型的VARCHAR2长度4000做的限制,那么只需要修改输出参数和聚集函数的返回值类型为CLOB类型即可:
SQL> CREATE OR REPLACE TYPE SUM_LINK AS OBJECT (
2 STR VARCHAR2(32767),
3 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT SUM_LINK) RETURN NUMBER,
4 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT SUM_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
5 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN SUM_LINK, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER,
6 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT SUM_LINK, CTX2 IN SUM_LINK) RETURN NUMBER
7 )
8 /
类型已创建。
SQL> CREATE OR REPLACE TYPE BODY SUM_LINK IS
2 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT SUM_LINK) RETURN NUMBER IS
3 BEGIN
4 SCTX := SUM_LINK(NULL);
5 RETURN ODCICONST.SUCCESS;
6 END;
7
8 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT SUM_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 SUM_LINK, RETURNVALUE OUT CLOB, 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 SUM_LINK, CTX2 IN SUM_LINK) RETURN NUMBER IS
21 BEGIN
22 NULL;
23 RETURN ODCICONST.SUCCESS;
24 END;
25 END;
26 /
类型主体已创建。
SQL> CREATE OR REPLACE FUNCTION SUM_LINK(P_STR VARCHAR2) RETURN CLOB
2 AGGREGATE USING SUM_LINK;
3 /
函数已创建。
SQL> SELECT SUM_LINK(SEQUENCE_NAME) FROM ALL_SEQUENCES;
这个检索结果截图我就不粘了 ,我的库里SEQUENCE太多了.......
总结一下:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
引起这种问题的根源就是存放数据的缓冲区长度比要存的数据本身的长度小,造成的,那么知道问题的原因,解决起来也就容易多了。
可以参看一下http://www.blogjava.net/wangbing/archive/2010/03/15/315482.html这篇文章中关于关于oracle中varchar2的最大长度的介绍。
观点是:varchar2有两个最大长度:一个是在字段类型4000;一个是在PL/SQL中变量类型32767。
也就是说schema级varchar2的长度限制都是4000,而在PL/SQL代码级的长度限制是32767。这是一个比较容易出错的地方,也很容易被忽略。
因为在函数中我可以声明长度超过4000的字符串变量,并且将它作为返回值,这里是不会提示编译错误的。
这个函数平时都可以正常执行,而且网络上的聚合函数的例子也大多都是上面那么写的,是由于作者没有考虑大数据量的问题。一旦用到项目上,遭遇大数据量,这个字符串长度超过4000,函数执行就会出错。证明这一点极容易被忽略。
再列举一个例子:
-- 准备自定义类型 strcat_type
create type strcat_type as object (
cat_string varchar2(4000),
static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number,
member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return number,
member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) return number,
member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out varchar2,flags in number) return number
)
-- 准备自定义类型体
create type body strcat_type is
static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number
is
begin
cs_ctx := strcat_type( null );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT strcat_type,
value IN varchar2 )
return number
is
begin
self.cat_string := self.cat_string || value || ';';
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN Out strcat_type,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := ltrim(rtrim(self.cat_string,','),',');
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT strcat_type,
ctx2 IN Out strcat_type)
return number
is
begin
self.cat_string := self.cat_string || ',' || ctx2.cat_string;
return ODCIConst.Success;
end;
end;
-- 创建字符串求和自定义函数
CREATE or replace FUNCTION sum_str(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING strcat_type;
-- 表和数据准备我就不详细介绍了.....
-- 测试一下自己的方法
SQL> select sum_str(decode(id, 1, name, null)) a,
2 sum_str(decode(id, 2, name, null)) b,
3 sum_str(decode(id, 3, name, null)) c
4 from test
5 ;
A B C
-------- ------------- --------------
AAA;ABC; BBB;DDD; CCC;
这是数据少的情况下。不会报错,一但数据量过大,就会报
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
修改一下数据类型;如下:
--修改函数返回类型为CLOB
CREATE OR REPLACE FUNCTION sum_str(input varchar2 ) RETURN
CLOB PARALLEL_ENABLE AGGREGATE USING strcat_type;
--将类型声明中的varchar2(4000)改为varchar2(32767)
CREATE OR REPLACE TYPE "STRCAT_TYPE" as object ( cat_string
varchar2(32767),
static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number,
member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return number,
member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) return number,
member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out clob,flags in number) return number
)
--修改类型体的输出参数
create or replace type body strcat_type is static function
ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return
number is begin cs_ctx := strcat_type( null ); return
ODCIConst.Success; end;
member function ODCIAggregateIterate(self IN OUT
strcat_type, value IN varchar2) return number is begin
self.cat_string := self.cat_string || value || ';'; return
ODCIConst.Success; end;
member function ODCIAggregateTerminate(self IN Out
strcat_type, returnValue OUT clob, flags IN number)
return number is begin returnValue :=
ltrim(rtrim(self.cat_string,','),','); return
ODCIConst.Success; end;
member function ODCIAggregateMerge(self IN OUT strcat_type,
ctx2 IN Out strcat_type) return number is begin
self.cat_string := self.cat_string || ',' ||
ctx2.cat_string; return ODCIConst.Success; end;
end;
再次使用sum_str方法,执行sql语句的时候不会再有任何问题了.......
oracle数据库自定义聚合函数字符缓冲区太小 .
Oracle的定制功能十分强大。前几天在碰到一个问题的时候,查询文档发现,oracle不但允许用户定制自己的函数,还可以定制自己的聚集函数和分析函数。
下面是我使用自定义聚集函数建立一个字符串“sum”的小例子。
类型声明:
CREATE OR REPLACE TYPE SUM_LINK AS OBJECT (
STR VARCHAR2(30000),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT SUM_LINK) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT SUM_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN SUM_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT SUM_LINK, CTX2 IN SUM_LINK) RETURN NUMBER
)
类型主体:
CREATE OR REPLACE TYPE BODY SUM_LINK IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT SUM_LINK) RETURN NUMBER IS
BEGIN
SCTX := SUM_LINK(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT SUM_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
SELF.STR := SELF.STR || VALUE||';';
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN SUM_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := SELF.STR;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT SUM_LINK, CTX2 IN SUM_LINK) RETURN NUMBER IS
BEGIN
NULL;
RETURN ODCICONST.SUCCESS;
END;
END;
方法创建:
CREATE OR REPLACE FUNCTION SUM_LINK(P_STR VARCHAR2) RETURN VARCHAR2
AGGREGATE USING SUM_LINK;
建表、插入数据,用来测试:
CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(20));
INSERT INTO TEST VALUES (1, 'AAA');
INSERT INTO TEST VALUES (2, 'BBB');
INSERT INTO TEST VALUES (1, 'ABC');
INSERT INTO TEST VALUES (3, 'CCC');
INSERT INTO TEST VALUES (2, 'DDD');
COMMIT;
测试自定义函数和类型:
SQL> SELECT ID, SUM_LINK(NAME) NAME FROM TEST GROUP BY ID;
ID NAME
---------- ----------------------------
1 AAA;ABC;
2 BBB;DDD;
3 CCC;
这里介绍一下通过SQL的方法来解决同样的问题。
对于下面这个利用自定义聚集函数的例子,将其改写为直接用SQL实现:
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------ -------- ------------------------------------ ------- ----------
SY_NAME SYNONYM
T TABLE
TEST TABLE
TEST1 TABLE
SUM_BLOB TABLE
SUM_CLOB TABLE
V_T VIEW
V_TEST VIEW
已选择8行。
SQL> SELECT TABTYPE, SUM_LINK(TNAME) TNAME FROM TAB GROUP BY TABTYPE;
TABTYPE TNAME
------- --------------------------------------------------
SYNONYM SY_NAME
TABLE T,TEST1,SUM_CLOB,SUM_BLOB,TEST
VIEW V_T,V_TEST
除了利用自定义聚集函数外,SQL函数中能将多个字符串合并在一起的只有SYS_CONNECSUM_BY_PATH了。
而这个函数只能应用在树型查询中,为了能使用这个函数,必须人为的构造出树来。
也就是说,必须可以构造出一个CONNECT BY列使得相同的TABTYPE的TNAME可以用SYS_CONNECSUM_BY_PATH连接起来。
连接列可以使用ROW_NUMBER() OVER()来构造,在CONNECT BY的时候指定当前列的等于父列的值加1。
并在START WITH时指定起始值为1。
最后对TABTYPE进行分组,取得最大值就是最终需要的结果:
SQL> SELECT TABTYPE, MAX(LTRIM(SYS_CONNECSUM_BY_PATH(TNAME, ','), ',')) TNAME
2 FROM
3 (
4 SELECT TABTYPE, TNAME, ROW_NUMBER() OVER(PARTITION BY TABTYPE ORDER BY TNAME) RN
5 FROM TAB
6 )
7 START WITH RN = 1
8 CONNECT BY PRIOR RN + 1 = RN
9 AND PRIOR TABTYPE = TABTYPE
10 GROUP BY TABTYPE;
TABTYPE TNAME
------- --------------------------------------------------
SYNONYM SY_NAME
TABLE T,TEST,TEST1,SUM_BLOB,SUM_CLOB
VIEW V_T,V_TEST
不过上面两种方法都会面临一个问题,就是如果聚集连接的字符串长度如果超过了VARCHAR2类型所允许的最大长度,就会导致字符串超长的错误。
自定义聚集函数这里就不重复了,可以参考上面的介绍:
SQL> SELECT SUM_LINK(TNAME) FROM TAB;
SUM_LINK(TNAME)
-------------------------------------------------------------------------------------------
BAK_SHGOV_ORDER,BAK_SHGOV_ORDER_BAK,PLAN_TABLE,SHGOV_ORDER,SHGOV_ORDER_BAK,T,T1,TEST,SUM_SQL
SQL> SELECT SUM_LINK(SEQUENCE_NAME) FROM ALL_SEQUENCES;
SELECT SUM_LINK(SEQUENCE_NAME) FROM ALL_SEQUENCES
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在"TEST.SUM_LINK", line 16
ORA-06512: 在line 1
SQL> SELECT MAX(LTRIM(SYS_CONNECSUM_BY_PATH(SEQUENCE_NAME, ','), ',')) NAME
2 FROM
3 (
4 SELECT SEQUENCE_NAME, ROW_NUMBER() OVER(ORDER BY SEQUENCE_NAME) RN
5 FROM ALL_SEQUENCES
6 )
7 START WITH RN = 1
8 CONNECT BY PRIOR RN + 1 = RN
9 ;
FROM ALL_SEQUENCES
*
ERROR 位于第 5 行:
ORA-01489: 字符串连接的结果过长
显然是由于要连接的字符串太长了,导致Oracle的字符串处理过程中出现了错误。上面的两种方法都没有办法避免这个问题。
但是ALL_SEQUENCES中的记录只有几百个,每个名称的长度不会超过30,因此最终的长度不会超过32767。
根据Oracle给出的错误信息,显然是在处理输出参数RETURNVALUE的时候是安装SQL类型的VARCHAR2长度4000做的限制,那么只需要修改输出参数和聚集函数的返回值类型为CLOB类型即可:
SQL> CREATE OR REPLACE TYPE SUM_LINK AS OBJECT (
2 STR VARCHAR2(32767),
3 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT SUM_LINK) RETURN NUMBER,
4 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT SUM_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
5 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN SUM_LINK, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER,
6 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT SUM_LINK, CTX2 IN SUM_LINK) RETURN NUMBER
7 )
8 /
类型已创建。
SQL> CREATE OR REPLACE TYPE BODY SUM_LINK IS
2 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT SUM_LINK) RETURN NUMBER IS
3 BEGIN
4 SCTX := SUM_LINK(NULL);
5 RETURN ODCICONST.SUCCESS;
6 END;
7
8 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT SUM_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 SUM_LINK, RETURNVALUE OUT CLOB, 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 SUM_LINK, CTX2 IN SUM_LINK) RETURN NUMBER IS
21 BEGIN
22 NULL;
23 RETURN ODCICONST.SUCCESS;
24 END;
25 END;
26 /
类型主体已创建。
SQL> CREATE OR REPLACE FUNCTION SUM_LINK(P_STR VARCHAR2) RETURN CLOB
2 AGGREGATE USING SUM_LINK;
3 /
函数已创建。
SQL> SELECT SUM_LINK(SEQUENCE_NAME) FROM ALL_SEQUENCES;
这个检索结果截图我就不粘了 ,我的库里SEQUENCE太多了.......
总结一下:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
引起这种问题的根源就是存放数据的缓冲区长度比要存的数据本身的长度小,造成的,那么知道问题的原因,解决起来也就容易多了。
可以参看一下http://www.blogjava.net/wangbing/archive/2010/03/15/315482.html这篇文章中关于关于oracle中varchar2的最大长度的介绍。
观点是:varchar2有两个最大长度:一个是在字段类型4000;一个是在PL/SQL中变量类型32767。
也就是说schema级varchar2的长度限制都是4000,而在PL/SQL代码级的长度限制是32767。这是一个比较容易出错的地方,也很容易被忽略。
因为在函数中我可以声明长度超过4000的字符串变量,并且将它作为返回值,这里是不会提示编译错误的。
这个函数平时都可以正常执行,而且网络上的聚合函数的例子也大多都是上面那么写的,是由于作者没有考虑大数据量的问题。一旦用到项目上,遭遇大数据量,这个字符串长度超过4000,函数执行就会出错。证明这一点极容易被忽略。
再列举一个例子:
-- 准备自定义类型 strcat_type
create type strcat_type as object (
cat_string varchar2(4000),
static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number,
member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return number,
member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) return number,
member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out varchar2,flags in number) return number
)
-- 准备自定义类型体
create type body strcat_type is
static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number
is
begin
cs_ctx := strcat_type( null );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT strcat_type,
value IN varchar2 )
return number
is
begin
self.cat_string := self.cat_string || value || ';';
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN Out strcat_type,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := ltrim(rtrim(self.cat_string,','),',');
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT strcat_type,
ctx2 IN Out strcat_type)
return number
is
begin
self.cat_string := self.cat_string || ',' || ctx2.cat_string;
return ODCIConst.Success;
end;
end;
-- 创建字符串求和自定义函数
CREATE or replace FUNCTION sum_str(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING strcat_type;
-- 表和数据准备我就不详细介绍了.....
-- 测试一下自己的方法
SQL> select sum_str(decode(id, 1, name, null)) a,
2 sum_str(decode(id, 2, name, null)) b,
3 sum_str(decode(id, 3, name, null)) c
4 from test
5 ;
A B C
-------- ------------- --------------
AAA;ABC; BBB;DDD; CCC;
这是数据少的情况下。不会报错,一但数据量过大,就会报
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
修改一下数据类型;如下:
--修改函数返回类型为CLOB
CREATE OR REPLACE FUNCTION sum_str(input varchar2 ) RETURN
CLOB PARALLEL_ENABLE AGGREGATE USING strcat_type;
--将类型声明中的varchar2(4000)改为varchar2(32767)
CREATE OR REPLACE TYPE "STRCAT_TYPE" as object ( cat_string
varchar2(32767),
static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number,
member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return number,
member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) return number,
member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out clob,flags in number) return number
)
--修改类型体的输出参数
create or replace type body strcat_type is static function
ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return
number is begin cs_ctx := strcat_type( null ); return
ODCIConst.Success; end;
member function ODCIAggregateIterate(self IN OUT
strcat_type, value IN varchar2) return number is begin
self.cat_string := self.cat_string || value || ';'; return
ODCIConst.Success; end;
member function ODCIAggregateTerminate(self IN Out
strcat_type, returnValue OUT clob, flags IN number)
return number is begin returnValue :=
ltrim(rtrim(self.cat_string,','),','); return
ODCIConst.Success; end;
member function ODCIAggregateMerge(self IN OUT strcat_type,
ctx2 IN Out strcat_type) return number is begin
self.cat_string := self.cat_string || ',' ||
ctx2.cat_string; return ODCIConst.Success; end;
end;
再次使用sum_str方法,执行sql语句的时候不会再有任何问题了.......
- 新建_Microsoft_Word_文档.rar (26.7 KB)
- 下载次数: 2
相关推荐
大家用oracle数据库里面的wmsys.wm_concat函数有没有遇见过字符串缓冲区太小的情况? 针对这种情况我写了个自定义聚合函数 是clob版的。
ORA-06512 数字或值错误,字符缓冲区太小的解决方法。 有图有真相,希望对你有帮助。
get-stream - 将流作为字符串,缓冲区,或数组
C的简单字符串缓冲区。 概述 该库提供了一个简单的字符串缓冲区,该缓冲区允许添加不同的基本数据类型。 它可用于生成大字符串,而无需担心内存分配或类型转换。 用法 # include " string_buffer.h " # include ...
字符串缓冲区 C 字符串缓冲区 例子 StringBuffer *sb = string_buffer_new(); string_buffer_append (sb, " hello " ); string_buffer_append_n (sb, " 123456 " , 3 ); string_buffer_appendf (sb, " d " , 10 ); ...
实验字符串缓冲区库。 为什么 ? 因为C字符串很痛苦,不安全且缓慢。 假设您正在制作论坛引擎, 页面是固定大小的缓冲区,您可以在其中填充帖子。 您如何安全地构建页面而不截断帖子? 艰难的道路 char page[PAGE...
这是一个 C 的动态字符串缓冲区库。它最初是在 2000-2002 年由 Musician's Friend 开发的,并于 2005 年 3 月开源发布。我相信其意图是在 GNU 许可下发布它,尽管当时我从来没有时间更新许可信息。 建造 它可能有点...
rss_buffer 真正简单的字符串缓冲区
JavaBootCamp 周1 数组、循环、字符串缓冲区
StringBuffer与String类似,只不过StringBuffer在进行字符串处理时不生成新的对象,下面我们就来详解Java中字符串缓冲区StringBuffer类的使用:
Java天11 通用2. I / O 3.字符串缓冲区4.文件I / O
dest参数指定了保存目的串的缓冲区位置 (2)widecharlentostring function widecharlentostring(source :pwidechar;sourcelen :integer) :string; 将sourcelen长度的unicode编码字符串转换为单字节或多字节字符串。...
当我们的字符串缓冲区被多个线程使用时,JVM不能保证StringBuilder的操作是安全的,虽然他的速度最快,但是可以保证StringBuffer的正确操作。当然大多数情况下是我们是在单线程下进行的操作,所以大多建议用...
在一些编程语言中,经常会使用 StringBuilder 类或者类似的字符串缓冲区来拼接大量字符串。对于 C++ 来说,虽然没有专门的 StringBuilder 类,但我们可以通过定义一个字符串变量并利用其自身的 append() 函数来实现...
Java 字符串与文本相关实例源码,比如不可变字符串与限定字符串、字符串的比较、提取子串、修改缓冲区中的字符串、判断回文串、正则表达式、字符串匹配、正则表达式语法等,还一一些比如用于比较两个变量是否引用同...
C++常用库函数 缓冲区操作函数、字符分类函数、数据转换函数、数学函数、输入和输出函数、进程控制函数、字符串操作函数 C++常用库函数 缓冲区操作函数、字符分类函数、数据转换函数、数学函数、输入和输出函数、...
编完整程序,利用DOS系统功能调用,从键盘输入一个字符串,并将该字符串从屏幕上换行后输出。
java源码字符串缓冲区 这是 Java String 、 StringBuilder和StringBuffer方法/API 的完整指南。 本指南的源代码示例在我们的本地开发环境中进行了良好的测试,您可以将这些示例用作无错误。 您可以在 上了解更多信息...
此功能地实现主要是通过DOS的0AH号功能调用,把字符串输入到STRING缓冲区中,以’$’符为结束标志;2.在字符串中的指定位置插入字符的操作。此功能的实现主要考虑正确处理字符串的向后移位,为扩大功能,其中还使用...