ORACLE 函数处理的数据返回值过长 character string buffer too small
来源:互联网 发布:四选一数据选择器实验 编辑:程序博客网 时间:2024/05/16 09:00
--问题
-- ORA-06502: PL/SQL: 数字或值错误 : character string buffer too small
--ORA-06512: 在 "WMSYS.WM_CONCAT_IMPL", line 30
--错误 WMSYS.WM_CONCAT()
SELECT HEAD.BOOKING_HEAD_ID,
(SELECT REPLACE
(
WMSYS.WM_CONCAT( CONTAINER.CONTAINER_NO --这里改用 F_LINK_LOB(.) 处理
|| '/'
|| SEAL_NO_1
|| '/'
|| CONTAINER_TYPE
|| '/'
|| DECODE (CONTAINER_STATUS_I, 4, 'E', 7, 'L', 'F')),
',',
CHR (10)
)
FROM DOC_BK_CONTAINER CONTAINER
WHERE HEAD.BOOKING_HEAD_ID = CONTAINER.BOOKING_HEAD_ID)
AS CONTAINERS_BOX_INFO
FROM DOC_BOOKING_HEAD HEAD
WHERE HEAD.BOOKING_HEAD_ID = '2c2881d62e50c1c1012e5573b63e54c2'
ORDER BY HEAD.BL_NO;
--方法 F_LINK_LOB
CREATE OR REPLACE FUNCTION F_LINK_LOB (P_STR VARCHAR2)
RETURN CLOB
AGGREGATE USING T_LINK_LOB;
--类型T_LINK_LOB
CREATE OR REPLACE TYPE "T_LINK_LOB"
AS
OBJECT
(
V_LOB CLOB,
STATIC FUNCTION ODCIAGGREGATEINITIALIZE (SCTX IN OUT NOCOPY T_LINK_LOB)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT NOCOPY T_LINK_LOB, VALUE IN VARCHAR2)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE
(
SELF IN T_LINK_LOB,
RETURNVALUE OUT NOCOPY CLOB,
FLAGS IN NUMBER
)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT NOCOPY T_LINK_LOB, CTX2 IN T_LINK_LOB)
RETURN NUMBER
)
CREATE OR REPLACE TYPE BODY T_LINK_LOB
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE (SCTX IN OUT NOCOPY T_LINK_LOB)
RETURN NUMBER
IS
BEGIN
SCTX := T_LINK_LOB (NULL);
DBMS_LOB.CREATETEMPORARY (SCTX.V_LOB, TRUE, DBMS_LOB.SESSION);
DBMS_LOB.OPEN (SCTX.V_LOB, DBMS_LOB.LOB_READWRITE);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT NOCOPY T_LINK_LOB, VALUE IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
DBMS_LOB.WRITEAPPEND (SELF.V_LOB, LENGTH (VALUE) + 1, VALUE || ',');
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE
(
SELF IN T_LINK_LOB,
RETURNVALUE OUT NOCOPY CLOB,
FLAGS IN NUMBER
)
RETURN NUMBER
IS
BEGIN
DBMS_LOB.CREATETEMPORARY (RETURNVALUE, TRUE, DBMS_LOB.CALL);
DBMS_LOB.COPY (RETURNVALUE, SELF.V_LOB, DBMS_LOB.GETLENGTH (SELF.V_LOB) - 1);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT NOCOPY T_LINK_LOB, CTX2 IN T_LINK_LOB)
RETURN NUMBER
IS
BEGIN
NULL;
RETURN ODCICONST.SUCCESS;
END;
END;
- ORACLE 函数处理的数据返回值过长 character string buffer too small
- ORACLE 函数处理的数据返回值过长 character string buffer too small , WMSYS.WM_CONCAT
- ORACLE 函数处理的数据返回值过长 character string buffer too small
- PHP调用Oracle存储过程时的错误:ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- ORA-06502:PL/SQL :numberic or value error: character string buffer too small
- buffer too small
- buffer too small
- Buffer is too small
- c++ buffer is too small
- CString::Format出现的Buffer too small错误
- str.Format 错误 Buffer too small
- CString.Format报Buffer too small问题
- strcpy_s Buffer is too small 出错根本原因
- xsql---Buffer too small for record data
- CString.Format报Buffer too small问题
- 在亚联的那些日子里
- (二)ubuntu安装详细步骤
- 用jstl截取字符串
- 分区表及分区索引(18)--结束语
- Javascript 与正则表达式
- ORACLE 函数处理的数据返回值过长 character string buffer too small
- eclipse快速开发方法
- (四)samba配置详细步骤
- SqLite 第三方工具
- getResourceAsStream(String name) name的路径
- Apache配置中ProxyPassReverse指令的含义
- Hibernate + Oracle id 生成策略
- NSStirng、NSArray、以及枚举(Method小集合)
- 菜鸟编译pcl